Monitoring and Tuning Oracle Indexes

Oracle B-tree indexes are not static structures. As DML activity accumulates, two forms of index degradation occur that require monitoring and periodic maintenance: height increase from splitting and spawning, and space waste from deleted leaf nodes. Understanding both patterns is the foundation for identifying and repairing poorly performing indexes.

The first sign of index change is splitting and spawning. As rows are inserted and updated, index leaf nodes fill and must split to accommodate new entries. A split at the same level redistributes entries across sibling nodes without increasing the tree height. When an entire level fills, part of the tree spawns a new level above it, increasing the index height by one.

An index that has grown to four or more levels requires more block reads per lookup than a well-maintained three-level index. Each additional level adds at least one block read to every index-driven row access, multiplying I/O cost across high-frequency queries.

Index splitting in the sense of restructuring a large monolithic index into partitioned segments is a technique for improving performance on heavily accessed indexes. Before splitting or rebuilding, confirm that the index is actually causing the problem. Three Oracle tools identify missing or poorly performing indexes:

1. EXPLAIN PLAN: review execution plans for poorly performing SQL. Full table scans and inefficient nested loop operations on large tables are candidates for new or rebuilt indexes.
2. Automatic Database Diagnostic Monitor (ADDM): provides comprehensive performance analysis based on AWR workload snapshots, including missing index recommendations.
3. SQL Tuning Advisor: analyzes the SQL workload and generates recommendations for creating, modifying, or dropping indexes. Run via DBMS_SQLTUNE or Oracle Enterprise Manager.

Detecting Missing Materialized Views

Two approaches identify opportunities for materialized views alongside missing index analysis:

1. Examine repetitive query patterns where pre-computed aggregation or join results would eliminate repeated computation across frequent executions.
2. Use the DBMS_ADVISOR package (Materialized View Advisor) to analyze the workload and recommend suitable materialized views.

Index Partitioning as an Alternative to Full Rebuild

For large, heavily accessed indexes, partitioning distributes the index across multiple smaller segments. Three partitioning strategies apply:

• Range partitioning: divides the index on a range of key values; suited to data with natural contiguous ranges such as dates or sequence numbers.
• Hash partitioning: distributes entries evenly using a hash function; eliminates hotspots on monotonically increasing keys such as sequence-generated primary keys.
• List partitioning: partitions on a predefined set of discrete values; useful for known finite value sets such as region or status codes.

Online Reorganization Tools

Oracle 10g introduced online data reorganization capabilities that remain the foundation of current index maintenance practice. These features reduce the maintenance window required on active production databases:

1. Online table redefinition enhancements (DBMS_REDEFINITION)
2. Cloning of indexes, grants, and constraints during online redefinition
3. Online conversion from LONG to LOB datatypes
4. Unique index as an alternative to a primary key constraint
5. Table structure changes without recompiling dependent stored procedures
6. Online segment shrink (ALTER TABLE ... SHRINK SPACE)

Despite these automated tools, the Oracle DBA must still perform routine index maintenance on highly active databases to keep performance at peak levels.

The second sign of index degradation is the accumulation of deleted leaf nodes. When rows are deleted, Oracle does not immediately reclaim the corresponding index leaf node space. To avoid restructuring the index tree during peak DML, Oracle flags the index entries as logically deleted and leaves them in place. These deleted leaf nodes remain in the index structure, consuming space and increasing the number of blocks Oracle must scan during range scans on the affected key range.

With frequent INSERT, UPDATE, and DELETE activity, an index may accumulate enough deleted leaf nodes to measurably degrade range scan performance. The solution is to rebuild the index, which creates a clean B-tree with no deleted entries and an optimal height.

Oracle provides index statistics for structural analysis. An Oracle index should be considered for rebuild when any of the following conditions is true:

1. The index has spawned to more than 3 levels (BLEVEL > 3 in DBA_INDEXES).
2. There are a large number of deleted leaf nodes relative to total leaf blocks (DEL_LF_ROWS in INDEX_STATS).
3. The block BLKS_GETS_PER_ACCESS value from INDEX_STATS is greater than 5.

Always rebuild rather than drop and re-create. ALTER INDEX ... REBUILD constructs the new tree in temporary segments and replaces the original only after the new structure is fully validated, making it safe for production use.

Oracle maintains index statistics in three data dictionary views: DBA_INDEXES, USER_INDEXES, and ALL_INDEXES (IND is a synonym for USER_INDEXES). Key statistics available:

1. Exact depth of the index from root block to leaf blocks (BLEVEL)
2. Number of leaf blocks (LEAF_BLOCKS)
3. Clustering factor — the order of table rows relative to indexed values (CLUSTERING_FACTOR)
4. Number of distinct index values (DISTINCT_KEYS)
5. Average number of leaf blocks per index value (AVG_LEAF_BLOCKS_PER_KEY)
6. Average number of data blocks per index value for a table index (AVG_DATA_BLOCKS_PER_KEY)

DBA_INDEXES, USER_INDEXES, and ALL_INDEXES are the primary views for querying index metadata. Their columns organize into four groups:

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: UNIQUENESS, LOGGING, DEGREE, CACHE, PARTITIONED

To populate INDEX_STATS with structural metrics for a specific index, run ANALYZE INDEX ... VALIDATE STRUCTURE. INDEX_STATS holds data for only one index at a time and is overwritten on each run — query it immediately after each execution.

ANALYZE INDEX demo VALIDATE STRUCTURE;

To identify an out-of-balance index, run ANALYZE INDEX with VALIDATE STRUCTURE, then query INDEX_STATS for the three key diagnostic columns:

ANALYZE INDEX schema_name.index_name VALIDATE STRUCTURE;

SELECT height, del_lf_rows, blks_gets_per_access
FROM   index_stats;

Interpret the results as follows:

• HEIGHT > 3: the index has spawned additional levels; consider rebuilding.
• DEL_LF_ROWS high relative to leaf block count: significant deleted leaf node accumulation; rebuild to reclaim space.
• BLKS_GETS_PER_ACCESS > 5: excessive block reads per lookup; rebuild to improve access efficiency.

Monitor index space efficiency at regular intervals by querying PCT_USED from INDEX_STATS after each VALIDATE STRUCTURE run:

ANALYZE INDEX schema_name.index_name VALIDATE STRUCTURE;

SELECT pct_used
FROM   index_stats
WHERE  name = 'INDEX_NAME';

CREATE INDEX stores the timestamp of the most recent function used in a function-based index. This timestamp is updated when the index is validated. If a tablespace point-in-time recovery results in the function timestamp being newer than the timestamp stored in the index, Oracle marks the index invalid. Use ANALYZE INDEX ... VALIDATE STRUCTURE to revalidate the index after recovery.

Oracle 23ai continues to support ALTER INDEX ... REBUILD as the primary index rebuild mechanism. The command is safe for production use because it constructs the new index tree in temporary segments and replaces the original only after the new structure is fully validated.

-- Rebuild an index in its current tablespace
ALTER INDEX schema_name.index_name REBUILD;

-- Rebuild into a specific tablespace (recommended)
ALTER INDEX schema_name.index_name REBUILD TABLESPACE tablespace_name;

-- Rebuild online — no exclusive lock required
ALTER INDEX schema_name.index_name REBUILD ONLINE;

Always specify the TABLESPACE parameter when rebuilding. If omitted, Oracle rebuilds the index in the user's default tablespace, which may not be the correct location. Use REBUILD ONLINE on production tables where a DDL lock is not acceptable.

Oracle 23ai extends several capabilities that reduce the frequency and cost of manual index rebuilds:

1. Automatic Indexing (from Oracle 19c): the auto-indexing task monitors the SQL workload, creates candidate indexes invisibly, validates their impact, and promotes beneficial indexes to visible status automatically. Review decisions via DBMS_AUTO_INDEX.REPORT_ACTIVITY.
2. In-Memory Advisor integration: identifies tables and indexes that benefit from population into the In-Memory Column Store, reducing reliance on B-tree index scans for analytical queries.
3. Blockchain and immutable table indexes: rows cannot be deleted from these table types, so DEL_LF_ROWS accumulation does not apply; rebuild thresholds based on deleted leaf nodes are not relevant for these objects.
4. JSON Relational Duality Views: indexes on underlying duality view tables are managed through standard ALTER INDEX mechanics but require awareness of view-layer metadata caching behavior.

For standard B-tree index maintenance in Oracle 23ai, the recommended workflow is: run ANALYZE INDEX ... VALIDATE STRUCTURE → check HEIGHT, DEL_LF_ROWS, and BLKS_GETS_PER_ACCESS in INDEX_STATS → rebuild with TABLESPACE and ONLINE clauses → re-gather optimizer statistics with DBMS_STATS.GATHER_INDEX_STATS.

Index monitoring is an ongoing DBA responsibility. The two primary degradation patterns — height increase from splitting and spawning, and space waste from deleted leaf nodes — are detected through ANALYZE INDEX ... VALIDATE STRUCTURE and the INDEX_STATS view. The rebuild threshold guidelines (HEIGHT > 3, DEL_LF_ROWS significant, BLKS_GETS_PER_ACCESS > 5) provide actionable criteria for scheduling maintenance. Oracle 23ai's Automatic Indexing reduces the frequency of manual intervention, but structural monitoring with INDEX_STATS remains the definitive diagnostic for B-tree health.

The next lesson examines how the high water mark for a table affects the performance of full-table scans.