Re: Multiple Datafiles and performance?

Tim,

>Indexed access is a purely sequential activity from an I/O standpoint,
>putting aside the reality that a buffer cache exists. First, we access
the
>root block of the index and read its contents in order to know where to
>perform the next I/O (i.e. a branch block). Then we read that branch
block
>and read its contents in order to know where to perform the next I/O (i.e.
a
>leaf block). Then we read the leaf block and read its contents in order
to
>know where to perform the next I/O (i.e. a block in a table). And so
on...

>Since we are performing sequential single-block I/O (hence the name of the
>wait event "db file sequential read"), how can separating datafiles
>containing tables from datafiles containing indexes matter to performance?

For arguments sake, the I/O steps that you mention is for a single user. Assume thousands of users, in which case, everyone would be hitting the same disk volume. Whereas, if they were spread, the I/O would be spread across 2 different volumes.

Having said that, I am not for spreading them on different disk volumes. The goal should be spreading I/O evenly across all the available disk volumes. The S.A.M.E principle. Just for the heck of spreading the datafiles across disk volumes, I would not want the index datafile to be moved from a disk with 20% utilization to one with 90%.

Raj

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Rajesh.Rao_at_jpmchase.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).