Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Segregate tables and indexes?

Oracle Database Tips by Donald Burleson

Question:  What are the I/O benefits of segregating Oracle tables and indexes onto separate disk spindles? 

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 perils of 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 management, see my book "Oracle Tuning: The Definitive Reference" and Mike Ault's books on Oracle 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 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. 

Also, remember that some RAID levels (i.e. mirroring-only) allow for I/O segregation.
 
If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational