Comprehensive Index Analysis (dindex.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides comprehensive index analysis by:

• Displaying detailed storage parameters for all indexes
• Showing index types, status, and uniqueness indicators
• Revealing extent information and space allocation
• Indicating parallelism settings and partitioning status
• Providing statistics currency and row count information
• Supporting flexible filtering by owner, table, and index name

Script

rem  dindex.sql
rem
set lines 500
rem
ttitle 'Index Storage Parameters'
rem
col table_owner format a10 heading 'OWNER' trunc
col table_name format a18 heading 'TABLE NAME' trunc
col index_name format a12 heading 'INDEX NAME' trunc
col index_type format a7 heading 'TYPE' trunc
col partition_name format a12 heading 'PARTITION' trunc
col partition_position form 999 heading 'POS' trunc
col high_value format a9 heading 'HIGH|VALUE' trunc
col tablespace_name format a14 heading 'TABLESPACE' trunc
col initial_extent format 9,999,999 heading 'INITIAL|EXTENT|(K)' trunc
col next_extent format 9,999,999 heading 'NEXT|EXTENT|(K)' trunc
col pct_increase format 999 heading 'PCT|INC' trunc
col min_extents format 99 heading 'MIN|EXT' trunc
col max_extents format 999 heading 'MAX|EXT' trunc
col LAST_ANALYZED format a9 heading 'LAST|ANALYZED' trunc
col SAMPLE_SIZE format 999,999 heading 'SAMPLE|SIZE'
col NUM_ROWS format 999,999,999 HEADING 'NUM|ROWS'
col BLEVEL format 99
COL LEVELS FORMAT 99 HEADING 'NUM|LEVELS'
col DISTINCT_KEYS format 9,999,999 heading 'DISTINCT|KEYS' trunc
col LEAF_BLOCKS format 99,999 heading 'LEAF|BLKS'
col AVG_ROW_LEN format 9,999 heading 'AVG|ROW|LEN'
col AVG_LEAF_BLOCKS_PER_KEY format 99,999 heading 'AVG|LEAF|BLOCKS|PER KEY'
col AVG_DATA_BLOCKS_PER_KEY format 9,999 heading 'AVG|DATA|BLOCKS|PER KEY'
col CLUSTERING_FACTOR format 9,999,999 heading 'CLUSTERING|FACTOR'
col NUM_FREELIST_BLOCKS format 99,999 heading 'NUM|FREELIST|BLOCKS'
col logging format a3 heading 'LOG'
col status format a12 heading 'STATUS' trunc
col PARTITIONED format a12 heading 'PARTITIONED' trunc
col ini_trans format 999 heading 'INI|TRAN'
col max_trans format 999 heading 'MAX|TRAN'
col freelists format 999 heading 'FREE|LISTS'
rem
rem
select table_owner,
       decode( table_type, 'TABLE', null, 'CLUSTER', 'C', '?' ) table_type,
       table_name,
       index_type,
       status,
       decode( uniqueness, 'UNIQUE', 'U', null ) u,
       index_name,
  LAST_ANALYZED,
  NUM_ROWS,
       initial_extent/1024 initial_extent,
--       next_extent/1024 next_extent, pct_increase,
       degree,
       PARTITIONED    ,(BLEVEL + 1) LEVELS,LEAF_BLOCKS  ,      min_extents, max_extents,          pct_free,
       ini_trans,
tablespace_name
--       freelists
  from sys.dba_indexes
 where table_owner like nvl(upper('&owner'),'%')
   and table_name like nvl(upper('&table'),'%')
   and index_name like nvl(upper('&index'),'%')
 order by table_owner, table_name, index_name;
rem

Usage

SQL> @dindex.sql
Enter value for owner: HR
Enter value for table: EMPLOYEES
Enter value for index: %

Parameters

• owner: Schema owner (% for all, specific name for single schema)
• table: Table name pattern (% for all, specific name for single table)
• index: Index name pattern (% for all, specific name for single index)

Required Privileges

• SELECT on SYS.DBA_INDEXES

Sample Output

Index Storage Parameters

OWNER      TABLE NAME         TYPE    STATUS       U INDEX NAME  LAST     NUM ROWS INITIAL DEGREE PARTITIONED NUM LEAF MIN MAX PCT INI TABLESPACE
                                                                 ANALYZED          EXTENT         NO          LEVELS BLKS EXT EXT FREE TRAN
---------- ------------------ ------- ------------ - ---------- -------- -------- ------- ------ ----------- --- ----- --- --- ---- ---- --------------
HR         DEPARTMENTS        NORMAL  VALID        U DEPT_ID_PK  15-MAR-24  27,000      64      1 NO            2    12   1 999   10    2 USERS
           DEPARTMENTS        NORMAL  VALID          DEPT_NAME_IDX 15-MAR-24  27,000      64      1 NO            2    15   1 999   10    2 USERS

HR         EMPLOYEES          NORMAL  VALID        U EMP_EMAIL_UK 15-MAR-24 107,000     256      1 NO            3    89   1 999   10    2 USERS
           EMPLOYEES          NORMAL  VALID        U EMP_EMP_ID_PK 15-MAR-24 107,000     256      1 NO            3    95   1 999   10    2 USERS
           EMPLOYEES          NORMAL  VALID          EMP_DEPT_IDX  15-MAR-24 107,000      64      1 NO            2    25   1 999   10    2 USERS
           EMPLOYEES          NORMAL  VALID          EMP_NAME_IDX  15-MAR-24 107,000     128      4 NO            3   156   1 999   10    2 USERS

SALES      ORDER_ITEMS        NORMAL  VALID        U ORDER_ITEMS_PK 15-MAR-24 665,000    1024     1 NO            4   2567   1 999   10    2 SALES_DATA
           ORDER_ITEMS        BITMAP  VALID          OI_STATUS_IDX   15-MAR-24 665,000     512     1 NO            2    234   1 999   10    2 SALES_DATA

Key Output Columns

Identification

• OWNER: Schema that owns the index
• TABLE NAME: Table being indexed
• INDEX NAME: Name of the index
• U: Uniqueness indicator (U = Unique, blank = Non-unique)

Type and Status

• TYPE: Index type (NORMAL, BITMAP, FUNCTION-BASED, etc.)
• STATUS: Index status (VALID, INVALID, UNUSABLE)
• PARTITIONED: Whether index is partitioned (YES/NO)

Statistics

• LAST ANALYZED: When statistics were last gathered
• NUM ROWS: Number of rows in the underlying table
• NUM LEVELS: Height of the index B-tree structure
• LEAF BLKS: Number of leaf blocks in the index

Storage

• INITIAL EXTENT: Initial extent size in KB
• MIN EXT: Minimum number of extents
• MAX EXT: Maximum number of extents
• PCT FREE: Percentage of free space reserved in each block
• INI TRAN: Initial transaction slots per block

Performance

• DEGREE: Parallel degree for index operations
• TABLESPACE: Tablespace where index is stored

Understanding Index Types

NORMAL (B-Tree)

• Best for: High cardinality columns, range scans, equality searches
• Structure: Balanced tree with leaf and branch blocks
• Performance: Excellent for most OLTP operations

BITMAP

• Best for: Low cardinality columns in data warehouses
• Structure: Bitmap representation of values
• Performance: Excellent for complex WHERE clauses with AND/OR

FUNCTION-BASED

• Best for: Queries on expressions or functions
• Structure: Index on computed values
• Performance: Enables index usage on transformed data

Index Health Analysis

Status Indicators

• VALID: Index is current and usable
• INVALID: Index needs rebuilding
• UNUSABLE: Index cannot be used by optimizer

Performance Metrics

• NUM LEVELS: Optimal is 2-4 levels for most indexes
• LEAF BLKS: Higher values may indicate fragmentation
• Statistics Currency: Recent LAST_ANALYZED improves optimization

Storage Analysis

Space Efficiency

-- Calculate index size
SELECT owner, index_name,
       ROUND(leaf_blocks * 8192 / 1024 / 1024, 2) as size_mb,
       num_rows, blevel
FROM dba_indexes
WHERE owner = 'HR'
ORDER BY size_mb DESC;

Extent Analysis

• Large Initial Extent: May waste space for small indexes
• Many Extents: Could indicate growth patterns
• Tablespace Distribution: Check for optimal placement

Performance Tuning Insights

High NUM_LEVELS (> 4)

• Cause: Very large table or poor key distribution
• Solution: Consider partitioning or index rebuild
• Impact: More I/O required for index traversal

INVALID/UNUSABLE Status

-- Find invalid indexes
SELECT owner, table_name, index_name, status
FROM dba_indexes
WHERE status IN ('INVALID', 'UNUSABLE')
ORDER BY owner, table_name;

Stale Statistics

-- Find indexes with old statistics
SELECT owner, table_name, index_name, last_analyzed,
       ROUND(SYSDATE - last_analyzed) days_old
FROM dba_indexes
WHERE last_analyzed < SYSDATE - 30
ORDER BY days_old DESC;

Index Maintenance

Rebuild Recommendations

-- Generate rebuild statements for problem indexes
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD;' rebuild_sql
FROM dba_indexes
WHERE (status = 'UNUSABLE' OR blevel > 4)
AND owner NOT IN ('SYS', 'SYSTEM');

Statistics Update

-- Generate statistics gathering statements
SELECT 'EXEC DBMS_STATS.GATHER_INDEX_STATS(''' || owner || ''',''' ||
       index_name || ''');' stats_sql
FROM dba_indexes
WHERE last_analyzed < SYSDATE - 7
AND owner = 'HR';

Parallel Configuration

Parallel Degree Analysis

• DEGREE = 1: Serial processing (default)
• DEGREE > 1: Parallel processing enabled
• HIGH DEGREE: May consume excessive resources

Parallel Recommendations

-- Check parallel configuration
SELECT owner, table_name, index_name, degree,
       CASE
         WHEN degree = '1' THEN 'Serial'
         WHEN TO_NUMBER(degree) BETWEEN 2 AND 4 THEN 'Moderate Parallel'
         WHEN TO_NUMBER(degree) > 4 THEN 'High Parallel'
       END parallel_category
FROM dba_indexes
WHERE owner = 'SALES'
ORDER BY TO_NUMBER(degree) DESC;

Common Issues and Solutions

Fragmented Indexes

• Symptom: High BLEVEL, poor performance
• Solution: Rebuild or coalesce index
• Prevention: Regular maintenance, appropriate PCTFREE

Unused Indexes

• Identification: Monitor index usage statistics
• Decision: Drop if never used
• Caution: Verify with application team first

Inappropriate Index Types

• Bitmap on OLTP: Can cause locking issues
• B-tree on low cardinality: Poor selectivity
• Solution: Choose appropriate index type for workload

Best Practices

1. Regular Monitoring

   • Check index status weekly
   • Monitor space usage trends
   • Review statistics currency

2. Maintenance Planning

   • Schedule regular statistics gathering
   • Plan index rebuilds during maintenance windows
   • Monitor for fragmentation

3. Performance Optimization

   • Match index types to query patterns
   • Consider composite indexes for multi-column predicates
   • Monitor and tune parallel settings

Related Scripts

• Index Column Analysis
• Index Statistics
• Low Cardinality Index Analysis