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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Reduce Oracle Index I/O

Oracle Tips by Burleson Consulting
May 2, 2003

By Robin Schumacher

You can use the large (16k or 32K) blocksize data caches to contain data from indexes or tables that are the object of repeated large scans.  Does such a thing really help performance?  A small but revealing test can answer that question.  

For the test, the following query will be used against a database that has a database block size of 8K, but also has the 16K cache enabled along with a 16K tablespace:

      patient_id between 1 and 40000;

The ERADMIN.ADMISSION table has 150,000 rows in it and has an index build on the PATIENT_ID column.  An EXPLAIN of the query reveals that it uses an index fast-full scan (a multi-block read) to produce the desired end result:

Execution Plan
1     (Cost=41 Card=1 Bytes=4)
              (NON-UNIQUE) (Cost=41 Card=120002 Bytes=480008)

Executing the query (twice to eliminate parse activity and to cache any data) with the index residing in a standard 8K tablespace produces these runtime statistics:

          0  recursive calls
          0  db block gets
        421  consistent gets
          0  physical reads
          0  redo size
        371  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

To test the effectiveness of the new 16K cache and 16K tablespace, the index used by the query will be rebuilt into the 16K tablespace that has the exact same characteristics as the original 8K tablespace, except for the larger blocksize:

alter index 
      rebuild nologging noreverse tablespace indx_16k;
Once the index is nestled firmly into the 16K tablespace, the 
query is re-executed (again twice)with the following runtime statistics 
being produced:
          0  recursive calls
          0  db block gets
        211  consistent gets
          0  physical reads
          0  redo size
        371  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache.  Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracleand above are worth your investigation and trials in your own database. 

Also see the latest consensus on multiple blocksizes.

Positive experiences:

"My favorite recent article was on 32KB indexes - Our client (200GB+) saw a 20% reduction in I/O from this simple change... " 

The server had 8 CPUs 32gb RAM with db_cache_size = 3g and db_32k_cache_size = 500mb. The database was over 200 gigabytes.

Steve Taylor
Technical Services Manager EMEA
Eagle Investment Systems Corp.

WARNING:  Using multiple blocksizes effectively is not simple.  It requires expert-level Oracle skills and an intimate knowledge of your I/O landscape.  While deploying multiple blocksizes can greatly reduce I/O and improve response time, it can also wreak havoc in the hands of inexperienced DBA's.  Using non-standard blocksizes is not recommended for beginners.


Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.