Question: What are the
I/O benefits of segregating Oracle tables and indexes onto separate disk
Answer: This is a "big"
question because it touches Oracle DBA best practices, Simplified DBA
management, and Oracle performance reporting. There are many
misunderstanding and myths, and this topic deserved an in-depth answer.
Also, see the
very-large Oracle disks and see why disk segregation has become increasing
difficult, and why disk contention is unavoidable because of the limitations of
the ancient 50 year-old technology which relies on metal platters coated with
magnetic media. The speed of super-large Oracle disks (over 500 gig) are
constrained by the speed of the read-write heads and platter RPM (today's
platters cannot spin faster because they become aerodynamic).
For complete details on Oracle I/O
my book "Oracle
Tuning: The Definitive Reference" and Mike Ault's books on
Disk I/O Tuning.
Myths about separating tables and indexes
With all of the layers that we
see within Oracle I/O sub-systems, it's no wonder that many myths have arisen. A
few years ago, someone even published that the whole concept of reducing I/O
contention by segregating tables and index on separate physical devices was a
They noted that
Oracle the access between an index and it's table entries is linear.
Oracle requests an index block, waits, then requests a table block, &c, such
that any individual SQL statement would never simultaneously request and index
and table block. Hence, they mistakenly concluded that the
separation of tables and indexes would not reduce I/O contention.
The above statement was verified by Mike
Ault (see below), but it's a bit of a jump to conclude that segregating tables
and indexes onto separate devices does not reduce I/O contention.
In an active OLTP database you can
have thousands of concurrent requests per minute, and if every database object
is on the same physical disk, there is no way to reduce the contention (except
to move it to super-fast solid-state
disk, 300x faster). You can even get
SSD on PC laptops today.
If you have active files on the inner and outer cylinders, the
read-write heads will shake back-and-forth, and I/O speed slow dramatically,
with up to 10ms required for the read-write read to reposition itself:
As Mike Ault says, people don't take into
consideration the head movement and latency times.
'the main argument, supported by
10046 traces with a single-user system, is that access to tables and indexes
in a single query is not asynchronous in nature, but is rather a linear
However, even in single-user
systems this fails to take into consideration the required head movement and
disk latencies associated with reading index, then table.
In a multi-user environment, it
fails to take into consideration all of the above plus the effects of
multi-user access against co-located tables and indexes.?
Approaches to segregating tables and indexes
There are several layers of logical and
physical segregation for tables and indexes, each with it's own features,
advantages and disadvantages:
- Move tables and indexes onto
separate disks on separate datafiles.
- Move tables and indexes into
their own datafiles, but put them on the same physical disk.
- Partition the indexes and tables,
mapping the tables onto separate tablespaces based on a key or hash value.
The tablespaces, in-turn, are mapped to datafiles that reside on independent
There are many compelling reasons
to separate Oracle tables and indexes onto separate disks:
- Easier DBA organization
- By separating tables and indexes onto separate tablespaces, it becomes
easier to spot "hot" objects and manager recovery. For example, if
you place all of your indexes on a separate disk, you don't have to
worry if the disk of backup becomes corrupt because you can rebuild the
indexes to restore the device.
- Better reporting - By
segregating related tables onto separate data files, it's easier to
track I/O at the data file and tablespace level. In a large
multi-disk database, the smart DBA will map all giant tables to their
own set of disks, so that the OS level disk I/O reports point to the
activity of a specific table or index.
- I/O balancing - Some
say that it's a myth that segregating tables and indexes onto separate
disks doe not help because Oracle never reads the index and table at the
same instance. For a single task, yes, but in a real-world
database with hundreds of concurrent tasks, you may see serious disk
enqueues, as the disk read-write heads slam around, trying to serve-up
data blocks from distant cylinders.
What about RAID, SAN and NAS?
This discussion is clouded
when we discuss RAID with Oracle. Oracle's default RAID (as
codified in 10g ASM) is called SAME (Stripe and Mirror Everywhere), more
commonly known as RAID10. The RAID 10 mirrors all disks and then
deals-out the data blocks across the disks, just like dealing-out deck
of cards. This truly randomized I/O and any hot disks would be
very rare. These RAID'ed disk sets behaves as if they were a
single disk, with I/O levels raising and filling in unison.
Some say that these days with
RAID and SANs where you don't really have control over where the
information is laid out, separation doesn't matter. Like Mladen Gogala
'spreading the disk I/O is
not an issue. Majority of cases involves some kind of SAN solution,
which completely hides any underlying disk structure.
Your volumes consist of
striped and mirrored disk devices cached by the SAN box itself.
At the time when the DBA
sees data files, disk spindles are completely indiscernible.?
But what if the database is
not using a SAN or RAID and using old technology where they just have
regular disk drive running off Windows OS device drivers? Separation of
tables and indexes is obviously going to help.
And what about systems with
more than one CPU, those with SMP and parallel disk read requests? Would
it follow that one CPU can be requesting a look at an index while
another is looking for table data? With disk caches, the disk
controllers probably could handle these requests in almost simultaneous
fashion, eliminating disk latency caused by using a single disk.
Segregating objects in very large
But even with 500 gig disks, a
large database may map to dozens of disks, and some DBA's create "RAID
Groups", raiding sets of disks together, such that they all respond to
uniformly to random I/O activity.
Also, remember that some RAID
levels (i.e. mirroring-only) allow for I/O segregation.