RE: STRIPING AT Oracle Level
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle.tutorials@xxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 29 Oct 2007 16:42:17 -0400
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.
Regards,
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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?
--
Regards,
Deepak
Oracle DBA
Other related posts:
