RE: should you seperate indexes from tables in seperate datafiles

Hrrrmmmm - as a wine-drinking, vegetarian, non-weightlifting new yawk city boy, this explains why I never fit in with the storage crowd....

However, to address the original idea about striping across lots of disks, etc., you have to be very careful about how you configure your storage volumes depending on your storage arrays. The "intelligence" that is built-in to high-end frames can be outsmarted (for better or worse) by certain storage configurations. Case in point - you have an EMC array that exposes 9 GB RAID-1 volumes that you use Veritas to create stripe sets across. You make a 10-volume RAID-0 stripe and following the "match the filesystem block size to the oracle block size" principle you make the stripe depth 8k. This makes a certain degree of sense - linear reads and writes getting distributed among a number of physical spindles, helps mitigate hotspots, etc. However, on a Symmetrix, this will yield poor(er) performance results. This is because of two factors - one, regardless of the I/O on the host side, the Symm will always do backend I/O and cache allocation in 32k objects and two, the symmetrix readahead won't kick in until it sees two or three sequential tracks being requested within a certain minimum amount of time. So, the small stripe size ends up unnecessarily placing objects in cache and negates the readahead that can provide large performance enhancements. There's a whole host of oddities like these that are present in all of the major storage vendors, so you have to be aware of what's going to happen.

The moral of the story is, of course, the more expensive your storage array, the more you benefit by knowing the hows and whys of what your storage array does. Also try not to be too smart about how you set up your storage unless you have a very deep understanding of the intelligence behind the storage - it'll help keep you from shooting yourself in the foot. I've seen too many oracle DBAs spend hours creating a "highly tuned" storage configuration based on faulty or lacking information on how the storage array actually works and then they complain about how slow the array is....

Thanks,

Matt

--
Matthew Zito
GridApp Systems
Email: mzito_at_gridapp.com
Cell: 646-220-3551
Phone: 212-358-8211 x 359
http://www.gridapp.com



> -----Original Message-----

> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On 

> Behalf Of Stephen Lee

> Sent: Tuesday, July 15, 2003 10:25 AM

> To: Multiple recipients of list ORACLE-L

> Subject: RE: should you seperate indexes from tables in 

> seperate datafiles

> 

> 

> 

> Steroids, weight lifting, and a flattop hair cut (orange or 

> green).  After two years of this, try talking to the storage 

> guys while holding a beer in one hand and a Polish sausage in 

> the other.  If you can manage a good belch during the 

> conversation, even better.

> 

> (Are you a visual person?)

> 

> > -----Original Message-----

> > get to control how my disks are set up (part of that "now 

> now little 

> > girl, don't you worry your pretty little head about how the 

> disks are 

> > set up, you just leave that sort of stuff to us big <male> 

> data center 

> > operations people" crap I get)

> -- 

> Please see the official ORACLE-L FAQ: http://www.orafaq.net

> -- 

> Author: Stephen Lee

>   INET: Stephen.Lee_at_DTAG.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