Index Statistics Analysis (dstatind.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 statistics analysis by:

• Displaying structural information about indexes (B-level, leaf blocks)
• Showing cardinality and selectivity metrics
• Indicating when statistics were last collected and sampling percentages
• Identifying unique vs non-unique indexes
• Supporting flexible filtering by owner, table, and index patterns

Script

rem  dstatind.sql
rem
ttitle 'Index Statistics'
rem
set lines 132
rem
col table_name format a24 heading 'TABLE NAME'
col index_name format a23 heading 'INDEX NAME'
col u format a1 heading 'U'
col blevel format 0 heading 'BL'
col leaf_blocks format 999990 heading 'LEAF|BLOCKS'
col distinct_keys format 9999990 heading 'DISTINCT|KEYS'
col avg_leaf_blocks_per_key format 990 heading 'LEAF|BLKS|/KEY'
col avg_data_blocks_per_key format 990 heading 'DATA|BLKS|/KEY'
col last_analyzed format a14 heading 'LAST|ANALYZED'
col num_rows format 999,999,999 heading 'ROWS'
col samp_pct format 999 heading 'SAMP|PCT'
rem
break on table_name
rem
select table_name, index_name,
       decode( uniqueness, 'UNIQUE', 'U', null ) u,
       global_stats,
       blevel, leaf_blocks, distinct_keys,
       avg_leaf_blocks_per_key, avg_data_blocks_per_key,
       to_char(last_analyzed, 'mm/dd hh24:mi:ss') last_analyzed,
       num_rows,
       (sample_size / decode(num_rows, 0, 1, num_rows)) * 100 samp_pct
  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;

Usage

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

Parameters

• owner: Schema owner pattern (% 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 Statistics

TABLE NAME               INDEX NAME              U GLOBAL_STATS BL LEAF   DISTINCT LEAF DATA LAST           ROWS      SAMP
                                                                    BLOCKS KEYS     BLKS BLKS ANALYZED                 PCT
                                                                                     /KEY /KEY
------------------------ ----------------------- - ------------ -- ------ -------- ---- ---- -------------- --------- ----
DEPARTMENTS              DEPT_ID_PK              U YES           1     12       27    1    1 03/15 14:30:25     27,000  100
                         DEPT_NAME_IDX             YES           2     25       27    1    1 03/15 14:30:25     27,000  100

EMPLOYEES                EMP_EMAIL_UK            U YES           2     89      107    1    1 03/15 14:30:25    107,000  100
                         EMP_EMP_ID_PK           U YES           2     95      107    1    1 03/15 14:30:25    107,000  100
                         EMP_DEPT_IDX              YES           2     25       27    1   40 03/15 14:30:25    107,000  100
                         EMP_NAME_IDX              YES           3    156       89    2   48 03/15 14:30:25    107,000   15

JOBS                     JOB_ID_PK               U YES           1      8       19    1    1 03/15 14:30:25     19,000  100

Key Output Columns

Index Identification

• TABLE NAME: Table that the index belongs to
• INDEX NAME: Name of the index
• U: Uniqueness indicator (U = Unique, blank = Non-unique)
• GLOBAL_STATS: Whether global statistics are available

Structure Metrics

• BL (B-Level): Height of the index B-tree (excluding leaf level)
• LEAF BLOCKS: Number of leaf blocks in the index
• DISTINCT KEYS: Number of distinct key values

Efficiency Metrics

• LEAF BLKS/KEY: Average leaf blocks per distinct key
• DATA BLKS/KEY: Average data blocks per distinct key

Statistics Information

• LAST ANALYZED: When statistics were last collected
• ROWS: Number of rows in the underlying table
• SAMP PCT: Percentage of rows sampled for statistics

Understanding Index Metrics

B-Level Analysis

• Level 0-1: Very small indexes (< 1000 rows)
• Level 2: Small to medium indexes (1K-100K rows)
• Level 3: Large indexes (100K-10M rows)
• Level 4+: Very large indexes (> 10M rows)

Efficiency Indicators

• LEAF BLKS/KEY = 1: Highly selective, efficient index
• LEAF BLKS/KEY > 10: Poor selectivity, may need review
• DATA BLKS/KEY: Lower values indicate better clustering

Statistics Currency

• Recent LAST_ANALYZED: Current optimizer information
• Old statistics: May cause poor execution plans
• SAMP PCT < 10%: May need higher sampling for accuracy

Performance Analysis

Index Efficiency Assessment

-- Identify potentially inefficient indexes
SELECT table_name, index_name, distinctness,
       CASE
         WHEN distinctness < 0.01 THEN 'Very Poor Selectivity'
         WHEN distinctness < 0.05 THEN 'Poor Selectivity'
         WHEN distinctness < 0.15 THEN 'Moderate Selectivity'
         ELSE 'Good Selectivity'
       END selectivity_rating
FROM (
    SELECT table_name, index_name,
           distinct_keys / GREATEST(num_rows, 1) distinctness
    FROM dba_indexes
    WHERE num_rows > 0
    AND table_owner = 'HR'
)
ORDER BY distinctness;

Statistics Staleness Analysis

-- Find indexes with stale statistics
SELECT table_name, index_name,
       ROUND(SYSDATE - last_analyzed) days_old,
       CASE
         WHEN last_analyzed IS NULL THEN 'Never Analyzed'
         WHEN SYSDATE - last_analyzed > 30 THEN 'Very Stale'
         WHEN SYSDATE - last_analyzed > 7 THEN 'Stale'
         ELSE 'Current'
       END stats_status
FROM dba_indexes
WHERE table_owner = 'HR'
ORDER BY days_old DESC NULLS FIRST;

Index Size Analysis

-- Calculate index storage requirements
SELECT table_name, index_name,
       leaf_blocks * 8192 / 1024 / 1024 index_size_mb,
       ROUND(leaf_blocks * 8192 / GREATEST(num_rows, 1)) bytes_per_row,
       blevel,
       CASE
         WHEN blevel > 4 THEN 'Consider Rebuild'
         WHEN blevel > 3 THEN 'Monitor'
         ELSE 'Normal'
       END height_status
FROM dba_indexes
WHERE table_owner = 'HR'
AND num_rows > 0
ORDER BY index_size_mb DESC;

Common Use Cases

1. Index Maintenance Planning

   • Identify indexes needing statistics refresh
   • Find candidates for rebuild due to fragmentation
   • Assess index efficiency and selectivity

2. Performance Optimization

   • Locate indexes with poor selectivity
   • Identify over-sized or inefficient indexes
   • Plan index consolidation or removal

3. Capacity Planning

   • Monitor index growth patterns
   • Plan storage requirements
   • Assess maintenance windows needed

Index Optimization Insights

High B-Level Indexes

Indexes with BLEVEL > 4 may need attention:

-- Generate rebuild statements for high B-level indexes
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD;' rebuild_sql
FROM dba_indexes
WHERE blevel > 4
AND table_owner = 'HR'
ORDER BY blevel DESC;

Poor Selectivity Indexes

Indexes with low distinct_keys/num_rows ratio:

-- Find low selectivity indexes
SELECT table_name, index_name,
       distinct_keys, num_rows,
       ROUND(distinct_keys * 100 / num_rows, 2) selectivity_pct
FROM dba_indexes
WHERE num_rows > 1000
AND distinct_keys < num_rows * 0.1  -- Less than 10% selectivity
ORDER BY selectivity_pct;

Statistics Collection Planning

-- Generate statistics gathering statements
SELECT 'EXEC DBMS_STATS.GATHER_INDEX_STATS(''' ||
       table_owner || ''',''' || index_name || ''');' stats_sql
FROM dba_indexes
WHERE (last_analyzed IS NULL OR last_analyzed < SYSDATE - 7)
AND table_owner = 'HR'
ORDER BY table_name, index_name;

Monitoring and Maintenance

Statistics Monitoring

-- Track statistics collection history
CREATE TABLE index_stats_history (
    collection_date DATE,
    owner VARCHAR2(128),
    table_name VARCHAR2(128),
    index_name VARCHAR2(128),
    num_rows NUMBER,
    distinct_keys NUMBER,
    leaf_blocks NUMBER,
    sample_size NUMBER
);

-- Insert current snapshot
INSERT INTO index_stats_history
SELECT SYSDATE, table_owner, table_name, index_name,
       num_rows, distinct_keys, leaf_blocks, sample_size
FROM dba_indexes
WHERE table_owner = 'HR';

Growth Tracking

-- Monitor index growth over time
SELECT h1.index_name,
       h1.leaf_blocks current_blocks,
       h2.leaf_blocks previous_blocks,
       ROUND((h1.leaf_blocks - h2.leaf_blocks) * 100 / h2.leaf_blocks, 2) growth_pct
FROM index_stats_history h1, index_stats_history h2
WHERE h1.index_name = h2.index_name
AND h1.collection_date = (SELECT MAX(collection_date) FROM index_stats_history)
AND h2.collection_date = (SELECT MAX(collection_date) FROM index_stats_history
                         WHERE collection_date < h1.collection_date)
ORDER BY growth_pct DESC;

Advanced Analysis

Index Clustering Analysis

-- Analyze index clustering factor
SELECT i.table_name, i.index_name,
       i.clustering_factor,
       t.blocks table_blocks,
       ROUND(i.clustering_factor / t.blocks, 2) clustering_ratio,
       CASE
         WHEN i.clustering_factor <= t.blocks THEN 'Well Clustered'
         WHEN i.clustering_factor <= t.blocks * 2 THEN 'Moderately Clustered'
         ELSE 'Poorly Clustered'
       END clustering_status
FROM dba_indexes i, dba_tables t
WHERE i.table_owner = t.owner
AND i.table_name = t.table_name
AND i.table_owner = 'HR'
AND i.clustering_factor > 0
ORDER BY clustering_ratio DESC;

Index Usage Assessment

-- Combine with index usage statistics (11g+)
SELECT i.table_name, i.index_name,
       NVL(u.total_access_count, 0) usage_count,
       i.last_analyzed,
       CASE
         WHEN u.total_access_count IS NULL THEN 'Never Used'
         WHEN u.total_access_count = 0 THEN 'Not Used'
         WHEN u.total_access_count < 100 THEN 'Rarely Used'
         ELSE 'Frequently Used'
       END usage_category
FROM dba_indexes i
LEFT JOIN dba_index_usage u ON i.owner = u.owner AND i.index_name = u.index_name
WHERE i.table_owner = 'HR'
ORDER BY NVL(u.total_access_count, 0) DESC;

Best Practices

1. Regular Statistics Collection

   • Gather statistics weekly for volatile tables
   • Use appropriate sampling percentages
   • Monitor for automatic statistics collection

2. Index Maintenance

   • Rebuild indexes with BLEVEL > 4
   • Monitor clustering factors for range scans
   • Remove unused or redundant indexes

3. Performance Monitoring

   • Track index size growth trends
   • Monitor selectivity changes
   • Correlate with application performance

Troubleshooting

Poor Query Performance

• Check if relevant indexes have current statistics
• Verify index selectivity is appropriate
• Look for high B-level or poor clustering

Excessive Storage Usage

• Identify oversized indexes
• Find indexes with poor space utilization
• Consider index compression for large indexes

Maintenance Issues

• Schedule statistics collection during off-peak hours
• Plan index rebuilds during maintenance windows
• Monitor space requirements for rebuild operations

Related Scripts

• Index Analysis
• Low Cardinality Indexes
• Index Column Analysis