during a 'db file sequential read', an index is _not_ accessed sequentially.
An index is not a sequential structure, so reading from an index in order will cause multiple seeks on the index itself. And we're talking single user here....
regards, Hans
Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 08 Oct 2003 16:29:25 -0800
Great responses ! Thanks very much ..
-----Original Message----- Dave Hau Sent: Wednesday, October 08, 2003 3:19 PM To: Multiple recipients of list ORACLE-L
Hi Gaja,
I agree that throughput can always be improved by adding more drives to the striped array. However, this does not improve access time. If you have your tables and indexes on the same striped array, necessarily the two I/O's have to be done sequentially, incurring two times access time at a minimum. However, if you separate the two into different arrays, then you can access them in parallel, starting to get data from each disk array in 1* access time. This makes sense esp. in scenarios where response time is more important than throughput, and also in use cases where your access pattern is random rather than sequential.
So I feel that there's a tradeoff between access time and throughput. If you have ten drives, and you stripe all of them into a single array and put both your data and indexes onto this array, you get maximum throughput but you're sacrificing access time for throughput. However, if you build two arrays each consisting of five drives, and put your data and indexes onto each array, you get half of the previous throughput, but you get better access time because now your data and index access can be truly in parallel.
Regards, Dave
[EMAIL PROTECTED] wrote: > Hi Hans/Vikas, > > I tend to agree that the old draconian rule that "thou > shalt always separate indexes from tables" may not > apply any more. We used to apply that principle in the > past when the number of available spindles was not > adequate. Seems like with 256G drives in the market, > we are being pushed back in time, in some way!!! > > The way I look at the problem is purely from an IOPS > perspective. For example, if each physical disk is > capable of 256 IOPS (ignore the cache configured here) > and you have 10 disks in your volume, then the total > I/O capacity on this volume is 2560 IOPS. Separation > of objects across multiple volumes may becomes an > issue, only when the demand for I/O outstrips the > supply (in this case 2560 IOPS). > > Even then, you can always add more drives to the > existing volume and restripe, i.e., adding 5 more > drives to 10 drives increases the I/O capacity by 50%. > At the end of the day, the I/O sub-system does not > care, whether it is servicing a data segment, index > segment or undo segment. > > But, in certain environments, that I have dealt with, > there has been a need to separate heavily and > concurrently accessed objects (does not matter whether > these objects are all indexes or tables or both). This > may be true only for certain objects and certain > queries. So, please don't apply this in a blanket > fashion. > > Empirical data is always the best justification > mechnism for a configuration exercise such as this. > Plus, you may have partitioning and other requirements > such as parallelism that impact the placement and > availability of your data. This in turn will control > the number of logical volumes that need to be created. > > I think the idea and philosophy behind SAME is noble - > Use all available drives, so that you do not have > localized hot-spots. But the implementation of SAME > and how many volumes you need in your enviroment, is a > function of your custom needs based on your system and > application demands. When you over-simplify something, > you lose the flexibility. The art factor here (which > requires some planning) is in achieving a balance > between simplicity, flexibility, performance, > manageability and availability. > > > Hope that helps, > > > Gaja > --- Hans de Git <[EMAIL PROTECTED]> wrote: > >>Vikas, >> >>Spend an hour on reading this usenet thread: >> >> > > http://groups.google.nl/groups?hl=nl&lr=&ie=UTF-8&oe=UTF-8&threadm=brjz8 .15%24707.245%40news.oracle.com&rnum=1&prev=/groups%3Fhl%3Dnl%26lr%3D%26 ie%3DUTF-8%26oe%3DUTF-8%26q%3Drogers%2Bseparate%2Bdata%2Bindex > >>It will open your eyes about separating data/index. >> >>Still not sure about the redolog stream...Because of >>the sequential nature >>of redologfiles. I've read tests that 'prove' it >>doesn't matter much >>whether you separate your redolog from 'ordinary' >>datafiles or not. It does >>simplify things when you pure SAME. >> >>Regards, >>Hans >> >> >>Reply-To: [EMAIL PROTECTED] >>To: Multiple recipients of list ORACLE-L >><[EMAIL PROTECTED]> >>Date: Wed, 08 Oct 2003 09:54:30 -0800 >> >>Thanks Gaja ! Does it also make sense from a >>performance perspective >>(I/O issues due to concurrent access of index and >>data ) to separate >>them or is that point moot once you apply the SAME >>methodology ? >> >>-----Original Message----- >>Gaja Krishna Vaidyanatha >>Sent: Wednesday, October 08, 2003 9:24 AM >>To: Multiple recipients of list ORACLE-L >> >>Vikas, >> >>The answer is an enthusiastic yes. This is purely >>from >>an administrative and manageability standpoint. For >>example, if you have INDEX and DATA segments >>separated >>in 2 different tablespaces, the backup of these >>tablespaces can be done INDEPENDENTLY. This is >>relevant, as if you were to rebuild your indexes >>using >>the NOLOGGING option between 2 backup jobs. If that >>were the case, then all you will need to do after >>the >>rebuild is complete, is to backup only the INDX >>tablespace. >> >>This is a best practice (if not a requirement) in >>most >>production shops, unless you think you can >>re-re-build >>your indexes in the event of media failure and you >>lose your INDX tablespace. >> >> >>Hope that helps, >> >> >>Gaja >>--- vikas kawatra <[EMAIL PROTECTED]> wrote: >> > Guys, >> > >> > Does it make sense to separate data and index >> > segments into separate >> > tablespaces if you create a single logical volume >> > and all files are >> > striped using the SAME methodology ? >> > >> > Thanks >> > >> > vikas >> > >> > >> > -- >> > Please see the official ORACLE-L FAQ: >> > http://www.orafaq.net >> > -- >> > Author: vikas kawatra >> > INET: [EMAIL PROTECTED] >> > >> >> >>===== >>Gaja Krishna Vaidyanatha >>Principal Technical Product Manager, >>Application Performance Management, Veritas >>Corporation >>E-mail : [EMAIL PROTECTED] Phone: (650)-527-3180 >>Website: http://www.veritas.com >> >>__________________________________ >>Do you Yahoo!? >>The New Yahoo! Shopping - with improved product >>search >>http://shopping.yahoo.com >>-- >>Please see the official ORACLE-L FAQ: >>http://www.orafaq.net >>-- >>Author: Gaja Krishna Vaidyanatha >> 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). >> >> >>-- >>Please see the official ORACLE-L FAQ: >>http://www.orafaq.net >>-- >>Author: vikas kawatra >> 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). >> >> > > _________________________________________________________________ > >>Chatten met je online vrienden via MSN Messenger. >>http://messenger.msn.nl/ >> >>-- >>Please see the official ORACLE-L FAQ: >>http://www.orafaq.net >>-- >>Author: Hans de Git >> 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). > > > > ===== > Gaja Krishna Vaidyanatha > Principal Technical Product Manager, > Application Performance Management, Veritas Corporation > E-mail : [EMAIL PROTECTED] Phone: (650)-527-3180 > Website: http://www.veritas.com > > __________________________________ > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search > http://shopping.yahoo.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dave Hau 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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: vikas kawatra 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).
_________________________________________________________________ MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hans de Git 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).