Advanced Tuning  «Prev  Next»
Lesson 7 Monitoring and tuning Oracle indexes
ObjectiveIdentify and repair poorly performing indexes
Oracle indexes
As Oracle indexes are used, they adjust. For this reason, it is important to monitor index activity. Let us examine the two ways in which indexes change, and how this can cause index imbalance.
Index splitting and spawning
The first sign of index change is splitting and spawning. As table rows are updated, new index levels may be created. Let us look at an example.
Splitting an index
Creation of deleted leaf nodes
The second sign of index change is when, as table rows are deleted, index nodes become logically deleted. To save processing time when a series of rows are deleted, the Oracle indexes will leave the dead index tree nodes in the index and flag them as deleted. These are called deleted leaf nodes.

Maintaining an index
With frequent inserts, updates, and deletions, indexes that have new levels or a number of deleted leaf nodes may or may not use their acquired space efficiently over time. Because these indexes require additional I/O to navigate, it is sometimes necessary to rebuild them. The goal of this lesson is to provide methods to identify and resolve these out-of-balance indexes. Although in theory, you could also drop and re-create, the solution is to rebuild.

Guidelines for rebuilding an index
Oracle provides several index statistics for analysis. An Oracle index may need to be rebuilt when:
  1. The index has spawned to more than 3 levels.
  2. There are a large number of deleted leaf nodes.
  3. The block GETS_PER_ACCESS value is greater than 5.

Using the analyze index command
The way to identify these values is to first analyze the index structure and then query the INDEX_STATS view.
To analyze indexes, Oracle provides the following command:
analyze index xxx validate structure;
In the example above, this command populates a table called INDEX_STATS with the validation information.
Let us try one. It is good SQL practice to be case-consistent.
Analyzing Orracle Index

Analyzing index performance

This shows the results of the analyze index query.
This shows the results of the analyze index query.
The results are shown above. Here we see that the index has only three levels, no deleted leaf rows, and three block gets per access. Our index is fine, and does not require re-building. Use the analyze index command and query the INDEX_STATS view regularly to monitor how efficiently your index uses space.
The index_stats table is re-populated after each command, so you must view it each time you run an index analyze.
Rebuilding Oracle indexes
Once we identify indexes for re-building, it is easy to re-build the index in-place:
Alter index index_name tablespace tablespace_name rebuild;
This command is completely safe because it rebuilds the index with temporary segments in the target tablespace, and only drops the index if the new tree has been successfully created.

Always use the tablespace parameter when rebuilding indexes. If you do not specify the tablespace name, Oracle will re-build the index in your default tablespace.
The next lesson looks at how the high water mark for a table affects the performance of a full-table scan.