Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Multiple Datafiles and performance?

Re: Multiple Datafiles and performance?

From: <Rajesh.Rao_at_jpmchase.com>
Date: Thu, 07 Aug 2003 10:59:25 -0800
Message-ID: <F001.005C97F3.20030807105925@fatcity.com>


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).
Received on Thu Aug 07 2003 - 13:59:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US