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

 
 Home
 E-mail Us
 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 


 

 

 


 

 

 

 
 

Identifying which Oracle Indexes to Rebuild

Oracle Tips by Burleson Consulting

 

Important update:  The rules for identification of candidates for index coalescing/rebuilding depend on your specific index state.  See MOSC notes 989186.1, 122008.1, 989093.1 for Oracle's suggestions on when to coalesce/rebuild indexes. Also see my updated notes on index coalesce or rebuilding and note this demonstration of an index that benefits from scheduled oracle index rebuilding.

 

Also, please see my updates and other notes on index rebuilding strategies, and my complete notes are found in my book "Oracle Tuning: The Definitive Reference".

 


Some people suggest that indexes require rebuilding when deleted leaf rows appear or when the index has a suboptimal number of block gets per access. While it is tempting to write a script that rebuilds every index in the schema, bear in mind that your schema may contain many thousands of indexes, and a complete rebuild can be very time consuming.

 

Hence, we need to develop a method to identify those indexes that will get improved performance with a rebuild. Lets look at one method for accomplishing this task.

 

How rare are "bad" indexes? 

 

You cannot generalize to say that index rebuilding for performance is rare, or even medium rare, it depends on many factors, most importantly the characteristics of the application.

  • In scientific applications (clinical, laboratory) where large datasets are added and removed, the need to rebuild indexes is "common".

  • Conversely, in system that never update or delete rows, index rebuilding rarely improves performance.

  • In systems that do batch DML jobs, index rebuilding "often" improves SQL performance.

Oracle MOSC note 122008.1 has the officially authorized script to detect indexes that benefit from rebuilding.  This script detects indexes for rebuilding using these rules:  Rebuild the index when these conditions are true:

- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.

Oracle's index rebuilding guidelines appear in MOSC note 77574.1 (dated April 2007) recommends that indexes be periodically examined to see if they are candidates for an index rebuild:

"When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.

It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt."

Oracle index nodes are not physically deleted when table rows are deleted, nor are the entries removed from the index. Rather, Oracle "logically" deletes the index entry and leaves "dead" nodes in the index tree where that may be re-used if another adjacent entry is required.

 

However, when large numbers of adjacent rows are deleted, it is highly unlikely that Oracle will have an opportunity to re-use the deleted leaf rows, and these represent wasted space in the index. In addition to wasting space, large volumes of deleted leaf nodes will make index fast-full scans run for longer periods.


These deleted leaf nodes can be easily identified by running the IDL.SQL script.


The number of deleted leaf rows


The term "deleted leaf node" refers to the number of index inodes that have been logically deleted as a result of row deletes. Remember that Oracle leaves "dead" index nodes in the index when rows are deleted. This is done to speed up SQL deletes, since Oracle does not have to allocate resources to rebalance the index tree when rows are deleted.


Index height


The height of the index refers to the number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows.

 

Oracle indexes can support many millions of entries in three levels.  Any Oracle index that has spawned to a 4th level followed by a large delete job might benefit from rebuilding to restore the index to it's pristine state.


Gets per index access


The number of "gets" per access refers to the amount of logical I/O that is required to fetch a row with the index. As you may know, a logical "get" is not necessarily a physical I/O since much of the index may reside in the Oracle buffer cache.


Unfortunately, Oracle does not make it easy to capture this information. In Oracle we must issue these commands to populate the statistics in dba_indexes and related dictionary tables:

ANALYZE INDEX index_name COMPUTE STATISTICS
ANALYZE INDEX index_name VALIDATE STRUCTURE


We might want to rebuild an index if the 'block gets' per access is excessive.  This happens when an index becomes "sparse" after high delete activity, making full-index scans requires unnecessary I/O. Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes.

As you may know, you can easily rebuild an Oracle index with the command:


ALTER INDEX index_name REBUILD tablespace FLOP;
 

Done properly during scheduled downtime, rebuilding an index is 100% safe.  Note the use of the tablespace option.  When rebuilding multi-gigabyte indexes, many DBA's will rebuild partitioned indexes into a fresh, empty tablespace for greater manageability. ( I use the convention ts_ndexname_flip, and ts_indexname_flop)

 

The ALTER INDEX index_name REBUILD command is very safe way to rebuild indexes. Here is the syntax of the command:


alter index index_name
rebuild
tablespace tablespace_name
storage (initial new_initial next new_next freelists new_freelist_number )


Unlike the traditional method where we drop the index and recreate it, the REBUILD command does not require a full table scan of the table, and the subsequent sorting of the keys and rowids. Rather, the REBUILD command will perform the following steps:

  1. Walk the existing index to get the index keys.

  2. Populate temporary segments with the new tree structure.

  3. Once the operation has completed successfully, drop the old tree, and rename the temporary segments to the new index.

As you can see from the steps, you can rebuild indexes without worrying that you will accidentally lose the index. If the index cannot be rebuilt for any reason, Oracle will abort the operation and leave the existing index intact. Only after the entire index has been rebuilt does Oracle transfer the index to the new b-tree.


Most Oracle administrators run this script, and then select the index that they would like to rebuild. Note that the TABLESPACE clause should always be used with the ALTER INDEX REBUILD command to ensure that the index is not rebuilt within the default tablespace (usually SYS).

 

Be aware that it's always a good idea to move an index into another tablespace and you must have enough room in that tablespace to hold all of the temporary segments required for the index rebuild, so most Oracle administrators will double-size index tablespaces with enough space for two full index trees.
 

Update:

When can we "prove" a benefit from an index rebuild?  Here, Robin Schumacher proves that an index that is rebuilt in a larger tablespace will contain more index entries be block, and have a flatter structure:

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

In an OracleWorld 2003 presentation titled 'Oracle Database 10g: The Self-Managing Database' by Sushil Kumar of Oracle Corporation, Kumar states that the new Automatic Maintenance Tasks (AMT) Oracle10g feature will "automatically detect and re-build sub-optimal indexes."

This Kim Floss article shows the Oracle 10g segment advisor recommending a rebuild of an index: 

"The page lists all the segments (table, index, and so on) that constitute the object under review. The default view ("View Segments Recommended to Shrink") lists any segments that have free space you can reclaim."


Oracle index rebuild advisor (Source: Oracle Corporation)

See my related notes on index rebuilding:

 


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

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

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.