Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: STRIPING AT Oracle Level

RE: STRIPING AT Oracle Level

From: Mark W. Farnham <>
Date: Mon, 29 Oct 2007 16:42:17 -0400
Message-ID: <02fa01c81a6c$35340190$>

If your goal really is attempting to distribute I/O uniformly without hardware assistance, then you are more likely to achieve that goal by using multiple files per tablespace. Then fill up each tablespace with dummy objects and delete them selectively so that the free extents available are a statistically spread out honeycomb of holes in tablespaces otherwise occupied by dummy objects. Then when you create your real objects the blocks of each object will be spread out across all the remaining available space. Now if there is high variation of activity in rows in some blocks by object, you would have to know that in advance of loading in order to spread out the "hot" rows. Usually older rows are cooler than newer rows, so you might want to spread out your rows by birthdate as well.  

Now all things considered, if your goal really is to distribute I/O uniformly across all disks, it might be easier to build daemons that each have a file assigned to them on a particular disk. Then each daemon would sample the average i/o blocks read and written on each disk in the system from system statistics and ramp up its own rate of reading and writing to bridge its gap with the average to result in a flat load profile across the system. If the i/o of the daemons is large compared to the total Oracle i/o this will be particularly effective in establishing uniform measured IO across the disks. This is very similar to running a variable number of processes repeatedly reading the same one block table in order to achieve a desired buffer cache hit ratio.  

Now if your actual goal is to achieve maximum throughput, that is a completely different matter from achieving uniform IO. If you have a tractable number of objects that comprise the bulk of the load and you know the i/o profile of rows and blocks of the busy objects, then it may be possible to achieve actual throughput of your transactions much higher than what is represented by "balanced" i/o. It seems unlikely that a valid analysis can be done and implemented cheaper than buying raid devices, but if your case is quite trivial it might be possible. Either of the techniques mentioned in your post might be better depending on your real situation, but if sequentially run batch jobs dominate your machine separation of objects by device tends to minimize seek time and maximize throughput. If a number of parallel jobs run on different objects, but the number of parallel jobs is not much greater than two times the number of independently operating disk drives, it may be possible to pipeline your i/o and your jobs so that they do not interfere with each other. As the number of parallel jobs increases and/or different jobs must access the same objects, all attempts to minimize extraneous seeks will disappear and a statmux layout will tend to be the better answer.  

>>> Buy the hardware. Based on how you have asked this question it is almost certainly the cheaper solution, and you get to keep the hardware, while if your situation changes the i/o signature analysis can go "poof!" If you really can't buy the hardware, you might try object isolation first to see if it generates any particularly hot disks. If it does, then spread out the objects on the drive(s) that were hot. Without knowing a lot about the details of your situation and job mix that's about the best you can do.  



From: [] On Behalf Of DBA Deepak
Sent: Saturday, October 27, 2007 5:26 PM To: oracle-l
Subject: STRIPING AT Oracle Level  

Hi Experts,  

Due to cost implications of RAID controllers we have thought of implementing striping at Oracle level using multiple data files per tablespace and spreading them across various disks. Our goal is to distribute IO uniformly across disks. Would like to have your expert comments on the following...  

> Is the above a better approach than balacing the IO by placing various
objects in different tablespaces based on the IO on them i.e., by monitoring the IO on various objects and placing them in appropriate tablespaces depending on the IO that happens on them?  

What are the pros-cons in our approach?  


Oracle DBA 

Received on Mon Oct 29 2007 - 15:42:17 CDT

Original text of this message