RE: Data and indexes on different volumes
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <patty.vonick@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 22 Mar 2011 00:55:31 -0400
See www.baarf.com
Next see Richard Foote's fine stuff about a ton of misconceptions about
gaining performance by separation of indexes and data.
Unless you have isolated media where it helps to have indexes elsewhere to
improve the chances of not interrupting full table scans all the way down to
the physical level of avoiding seeks for some batch window job, you probably
can't improve performance by separating indexes from data. (And it would be
indexes on other tables, not the one you're scanning that would likely be
accessed disturbing the seek position. Especially with S.A.M.E. and huge
spindle sizes, that is more rare now and it was always rare. Few people ever
understood the limited cases where separation of indexes and data could
improve performance and why. GL open period was a process in E-business
suite where you could gain a great deal, but it was not just separating
indexes from tables; you had to put several things on different disks.
Likewise checkruns in AP.
That said, if you're heavily indexed a lot of the i/o of an update, insert,
or delete is on the indexes. So if disk i/o is your limiting factor and you
moved some indexes off any RAID-F onto SSD, then the process would speed up
by the amount of i/o avoided on the RAID-F down to the point that i/o on the
RAID-F is no longer the limiting factor. If you tend to have wide tables
(that is, the sum of vsize on the columns of your rows is large) and narrow
indexes, then it would be acreage conservative of the SSD to move the
indexes there. If a decent percentage of your queries involve index only
access, that might play in your favor as well. Putting the most actively
used segments on faster storage, whether the segment is index or table, is
going to make those accesses take less time up to the point the new faster
media reaches its maximum i/o per unit time. And you can toss the special
case of avoiding seeks right out the window for SSD.
Against that, since getting faster disk seems to be on the table, consider a
comparable investment in media farm moving from RAID-F to a storage format
that takes up more room to store the same amount (by being fully duplex or
even triplex) but which works better with Oracle.
If you're moving to ASM you have to decide between external redundancy only,
ASM redundancy, or both. Giving ASM whole disks or whole stripesets is
probably a good start, but whatever else you do, don't mix speeds of media
in a single disk group.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Patty Vonick
Sent: Monday, March 21, 2011 7:29 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Data and indexes on different volumes
Hi all,
We are currently running 11.2.0.1 on Oracle Solaris v10. RAID-5 config.
We're hoping to move data around in the near future for many reasons - and
will likely migrate to ASM soon as well.
In the past, it was recommended that we separate data and indexes on
different storage - for optimal performance. But, we've been hearing that
is a thing of the past, and there are no performance gains by doing this -
especially when running on RAID-5.
Our SA has been hearing the opposite - that if we separate indexes from the
data, and put the indexes on faster disk (possibly SSD), we could see up to
a 2x gain in performance - since the updating of indexes is an "expensive"
operation.
Any thoughts would be very much appreciated.
Thanks!
Other related posts:
