 |
|
Identifying which Oracle Indexes to Rebuild
Oracle Tips by Burleson Consulting
|
Important 2009 update:
The rules for identification of candidates for index rebuilding
are changing. Please see my
updated notes on 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. Let’s 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 MetaLink 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
Metalink 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.
Hence, an Oracle
index may have four levels, but only in those areas of the index
tree where the massive inserts have occurred. Oracle indexes can
support many millions of entries in three levels, and any Oracle
index that has four or more levels would benefit from
rebuilding.
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.
However, any SAP index with a number greater than 10 would
probably benefit from an index rebuild.
Unfortunately, Oracle does not make it easy to capture this
information. In Oracle we must issue these commands:
ANALYZE INDEX index_name COMPUTE STATISTICS
ANALYZE INDEX index_name VALIDATE STRUCTURE
After you analyze the report above, you may want to consider
rebuilding any index where the height is more than three levels,
since three levels will support millions of index entries. Note
that Oracle indexes will “spawn” to a fourth level only in areas
of the index where a massive insert has occurred, such that 99%
of the index has three levels, but the index is reported as
having four levels.
We might want to rebuild an index if the “block gets” per access
is greater than five, since excessive “blocks gets” indicate a
fragmented b-tree structure. 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
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:
-
Walk the existing index to get the index keys.
-
Populate temporary segments with the new tree
structure.
-
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:
