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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
