Data Blocks  «Prev  Next»
Lesson 7 Monitoring and tuning Oracle indexes
ObjectiveIdentify and repair poorly performing indexes

Monitoring and tuning Oracle 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.

1) When an index node splits at the same level to accept new rows splitting occurs.
1) When an index node splits at the same level to accept new rows splitting occurs.

2) As a level becomes full, part of the tree may spawn, creating a new index level.
2) As a level becomes full, part of the tree may spawn, creating a new index level.
  1. When an index node splits at the same level to accept new rows splitting occurs.
  2. As a level becomes full, part of the tree may spawn, creating a new index level.

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.

Oracle Index Statistics

Oracle lists the following index statistics in the DBA_INDEXES, USER_INDEXES, and ALL_INDEXES data dictionary views:
  1. Exact depth of the index from root block to leaf blocks
  2. Number of leaf blocks
  3. Clustering factor (the order of the rows in relation to indexed values)
  4. Number of distinct index values
  5. Average number of leaf blocks per index value
  6. Average number of data blocks per index value (for an index on a table)

Querying Index Information

Several data dictionary views are available to query information about indexes. This section covers certain views and their columns that you should be familiar with before taking the exam.

DBA_INDEXES

The DBA_INDEXES, USER_INDEXES, and ALL_INDEXES views are the primary views used to query for information about indexes (IND is a synonym for USER_INDEXES). The views have the following information (the columns that can be used in the query are provided in parentheses):
  1. Identity (OWNER, INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME, TABLE_TYPE)
  2. Storage (TABLESPACE_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, MIN_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, BUFFER_POOL)
  3. Statistics (BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED)
  4. Miscellaneous create options (UNIQUENESS, LOGGING, DEGREE, CACHE, PARTITIONED)

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