Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SAME and separating disk and index tablespaces
Good stuff. Plus, watch this:
If saving dozens of hours of labor cost actually does cost a full 5% performance penalty on access time, and if reads from disk account for 10% of total response time for a given user action, then saving dozens of hours of labor cost will actually penalize total response time of that user action by only ½ of one percent.
Probably not a bad tradeoff in many situations. The key is to know what your situation is. The way to figure that out? Broken record says:
…extended SQL trace data (10046 level 8 or 12).
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Performance <http://www.hotsos.com/training/PD101.html> Diagnosis
101: 10/28 Phoenix, 11/19 Sydney
- Hotsos Symposium 2004 <http://www.hotsos.com/events/symposium/2004> :
March 7–10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
Loughmiller, Greg
Sent: Thursday, October 09, 2003 8:30 AM
To: Multiple recipients of list ORACLE-L
Maybe we have been lucky. But we use the SAME methodology. We have removed a considerable amount of "human effort" in regards to layout of datafiles and disk layout. And based on the stats that I have seen from the Storage team/SA's, we have *maybe* seen a 2-5% performance hit in access time. That info needs to be confirmed-but the disk technology has been improved so dramatically-that older "rules of thumb" aren't necessary for *specific* environments. There is still logical separation of tables/indexes as mentioned below.. But we typically present a single file system for the datafiles....
Does this work in all cases for us - nope! But it covers a majority of the environments and we address the *unique* environments accordingly. But considering the number of databases and the volume of disk space - more effective for us.
just a comment:-)
greg
-----Original Message-----
Sent: Wednesday, October 08, 2003 8:29 PM
To: Multiple recipients of list ORACLE-L
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
oraperfman_at_yahoo.com 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 <hansdegit_at_hotmail.com> wrote: >
ie%3DUTF-8%26oe%3DUTF-8%26q%3Drogers%2Bseparate%2Bdata%2Bindex
>
> > --------------------------------------------------------------------- >
> > --------------------------------------------------------------------- >
> > _________________________________________________________________ >
> > --------------------------------------------------------------------- >
> > > > ===== > Gaja Krishna Vaidyanatha > Principal Technical Product Manager, > Application Performance Management, Veritas Corporation > E-mail : gaja_at_veritas.com 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: davehau123_at_netscape.net 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: vikas kawatra INET: vkawatra_at_comcast.net 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: cary.millsap_at_hotsos.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 Oct 09 2003 - 09:34:24 CDT