Segregate 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 myth.

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 process.

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 disk spindles). 

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 says here:

“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 databases

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.