The main problem as I see it is that you might be lucky in getting IO balance with a tables-here-indexes-there approach in rule based databases, where pretty much the only thing Oracle can do is table scan and single block index read.
But since 7.3, and even more so with the more recent releases, suddenly there's index fast full scan, sort direct IO operations, table sampling which means that a) table "scan" IO is not always multiblock b) index IO is not always single block So my IO mantra (which I fail to achieve most of the time) is to aim to obtain a balanced IO load independent of the time quanta. So if I average the IO load for 3 hours, it will be balanced, but if I average it over 3 seconds, 3 minutes, 30 minutes etc then it will still be balanced. Sort of like the Sierpinski curves of IO. hth connor --- Stephane Faroult <[EMAIL PROTECTED]> wrote: > > Yechiel, > > > > You had mentioned only one possible scenario (i.e. > "user A accesses table while user B simultaneously > > accesses index") where there are several other > possible, equally-likely scenarios (i.e. "user A > accesses > > table while user B simultaneously accesses table", > "user A accesses index while user B simultaneously > > accesses index", etc). Separating tables and > indexes to separate devices does nothing for those > other, > > equally-likely scenarios, does it? That's the > reason for the question "why?" in the beginning of > my last > > reply... > > > > At issue here is not the concept of parallelism in > I/O. At issue (at least for me) is the > "conventional > > wisdom" that states/implies that there is some > performance benefit of separating tables and indexes > to > > separate devices. My assertion is that this is > irrelevant for two reasons: a) within a single > process the > > accessing of table blocks and index blocks are > purely sequential and b) tables and indexes have > different > > I/O characteristics which make it less likely that > they will conflict with each other. In fact, in > most > > situations datafiles/tablespaces containing > indexes generate far fewer physical I/Os than > > datafiles/tablespaces containing tables. From an > I/O perspective, the key is not to focus on whether > the > > datafile/tablespace contains tables or indexes but > rather to focus on the volume and type of physical > I/O > > they generate. > > > > By focusing on the I/O statistics rather than > whether they are tables or indexes, one can make > better > > determinations on how to distribute I/O across > non-RAID devices. > > > > Hope this helps... > > > > -Tim > > Tim, > > I fully subscribe to your conclusion but I > wouldn't be that harsh > about conventional wisdom, which once had some ring > of truth to it and > still has it on rustic configurations. Granted, for > a given user > parallelizing his or her table and index accesses > doesn't make much > sense. But when you have a lot of happy users > merrily issuing their > queries, you can hope that at some point in time > some will be hitting > indexes while others will be hitting tables - and > when dbwr and its gang > will join the party, both indexes and tables will be > hit too. This is > probably what Yechiel meant. I see conventional > wisdom as a > rough-and-ready rule-of-thumb to make people spread > their I/Os. And at > least the benefit of having separate tablespaces is > that you have > separate files which are easier to move around when > you have a finer > appreciation of what is going on. > > -- > Regards, > > Stephane Faroult > Oriole Software > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Stephane Faroult > 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). ===== Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Remember amateurs built the ark - Professionals built the Titanic" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
