Index Statistics Analysis (dindstats.sql)

What This Script Does

This script provides comprehensive analysis of index statistics and performance metrics, focusing on key indicators like clustering factor, selectivity, and structural characteristics. It helps DBAs evaluate index effectiveness, identify performance issues, and make informed decisions about index optimization and maintenance.

Script

rem  dindex.sql
rem
rem
rem
ttitle 'Index Storage Parameters'
rem
col table_owner format a8 heading 'OWNER'
col table_type format a1 heading 'T'
col table_name format a20 heading 'TABLE NAME'
col status format a1 heading 'S'
col u format a1 heading 'U'
col index_name format a18 heading 'INDEX NAME'
col tablespace_name format a10 heading 'TABLESPACE'
col pct_free format 999 heading 'PCT|FREE'
col ini_trans format 99 heading 'INI|TRX'
col initial_extent format 999999 heading 'INITIAL|EXTENT|(K)'
col next_extent format 999999 heading 'NEXT|EXTENT|(K)'
col pct_increase format 999 heading 'PCT|INC'
col min_extents format 99 heading 'MIN|EXT'
col max_extents format 999 heading 'MAX|EXT'
col freelists format 999 heading 'FREE|LIST'
col BLEVEL  format 999 head 'BLEVEL'
col LEAF_BLOCKS format 99,999,999 heading 'LEAF|BOCKS'
col DISTINCT_KEYS format 999,999,999,999,999 heading 'DISTINCT|KEYS'
col AVG_LEAF_BLOCKS_PER_KEY format 9,999 heading 'AVG|LEAF|BLOCKS|PER|KEY'
col AVG_DATA_BLOCKS_PER_KEY format 9,999,999 heading 'AVG|DATA|BLOCKS|PER|KEY'
col CLUSTERING_FACTOR format 999,999,999 heading 'CLUSTERING|FACTOR'
col NUM_ROWS format 999,999,999,999 heading 'NUM ROWS'
col SAMPLE_SIZE format 999,999 heading 'SAMPLE'
col LAST_ANALYZED  heading  'LAST|ANALYZED'
-- col DEGREE                                   VARCHAR2(40)
-- col INSTANCES                                VARCHAR2(40)
-- col PARTITIONED                              VARCHAR2(3)
-- col TEMPORARY                                VARCHAR2(1)
-- col GENERATED                                VARCHAR2(1)
-- col BUFFER_POOL                              VARCHAR2(7)
rem
break on table_owner skip 1 on table_name on table_type
rem
select table_owner,
       decode( table_type, 'TABLE', null, 'CLUSTER', 'C', '?' ) table_type,
       table_name,
--       decode( status, 'VALID', null, 'DIRECT LOAD', 'L', '?' ) status,
       decode( uniqueness, 'UNIQUE', 'U', null ) u,
       index_name,
       tablespace_name,
       LAST_ANALYZED,
--       SAMPLE_SIZE,
       NUM_ROWS,
       BLEVEL,
       LEAF_BLOCKS,
       DISTINCT_KEYS,
       AVG_LEAF_BLOCKS_PER_KEY,
       AVG_DATA_BLOCKS_PER_KEY,
       CLUSTERING_FACTOR
       --, GENERATED
  from sys.dba_indexes
 where table_owner like upper('&owner')
   and table_name like upper('&table')
   and index_name like upper('&index')
 order by table_owner, table_name, index_name;
rem

Usage

-- Run the script in SQL*Plus or SQLcl
@dindstats.sql

-- When prompted, enter parameters:
-- owner: Schema owner pattern (% for all)
-- table: Table name pattern (% for all)
-- index: Index name pattern (% for all)

-- Examples
Enter value for owner: %
Enter value for table: EMPLOYEES
Enter value for index: %

-- Or analyze specific index
Enter value for owner: HR
Enter value for table: %
Enter value for index: EMP_NAME_IDX

Parameters

&owner: Schema owner pattern (use % for all schemas)
&table: Table name pattern (use % for all tables)
&index: Index name pattern (use % for all indexes)

Required Privileges

SELECT on SYS.DBA_INDEXES
Typically requires DBA role or explicit grants

Sample Output

Index Storage Parameters

OWNER    T TABLE NAME          U INDEX NAME       TABLESPACE LAST      NUM ROWS BLEVEL  LEAF DISTINCT      AVG AVG DATA CLUSTERING
                                                               ANALYZED           BOCKS       KEYS LEAF BLOCKS PER FACTOR
                                                                                                   BLOCKS PER   KEY
                                                                                                   KEY
-------- - -------------------- - ------------------ ---------- --------- --------- ------ --------- ---------- ---- ------- ----------
HR         EMPLOYEES            U EMP_EMP_ID_PK     USERS      01-JUN-24   107,000      2    285      107,000     1       3        285
                                  EMP_DEPARTMENT_IDX USERS      01-JUN-24   107,000      2    890       27         33     890      2,345
                                  EMP_SALARY_IDX     USERS      15-MAY-24   107,000      2    567    45,000        1      12      45,678
                                  EMP_NAME_IDX       USERS      01-JUN-24   107,000      2    234   106,890        1       1        567

SALES      ORDERS               U ORD_ORDER_ID_PK   ORDERS     28-MAY-24 1,500,000      3  4,567   1,500,000     1       8      4,567
                                  ORD_CUSTOMER_IDX   ORDERS     28-MAY-24 1,500,000      3  2,345      15,000    1       100    156,789
                                  ORD_DATE_IDX       ORDERS     20-MAY-24 1,500,000      3  1,234       1,095    1     1,370    234,567

Key Output Columns

OWNER: Schema owner of the index
T: Table type (C=Cluster, blank=Table)
TABLE NAME: Name of the indexed table
U: Uniqueness indicator (U=Unique)
INDEX NAME: Name of the index
TABLESPACE: Tablespace containing the index
LAST ANALYZED: When statistics were last gathered
NUM ROWS: Number of rows in the table
BLEVEL: B-tree levels (height - 1)
LEAF BLOCKS: Number of leaf blocks in index
DISTINCT KEYS: Number of unique key values
AVG LEAF BLOCKS PER KEY: Average leaf blocks per distinct key
AVG DATA BLOCKS PER KEY: Average table blocks per distinct key
CLUSTERING FACTOR: Index-to-table ordering correlation

Understanding Index Statistics

Index Structure Metrics

BLEVEL: Index depth (2-3 typical, >4 may need rebuilding)
LEAF BLOCKS: Storage footprint of index
NUM ROWS vs DISTINCT KEYS: Selectivity indicator

Selectivity Analysis

High selectivity: DISTINCT_KEYS ≈ NUM_ROWS (unique values)
Low selectivity: DISTINCT_KEYS << NUM_ROWS (many duplicates)
Poor selectivity: May not benefit range scans

Clustering Factor Significance

Good clustering: Factor ≈ LEAF_BLOCKS (ordered data)
Poor clustering: Factor ≈ NUM_ROWS (random data)
Critical for: Range scan performance

Performance Analysis

Index Effectiveness Indicators

BLEVEL > 4: May need rebuilding or partitioning
High CLUSTERING_FACTOR: Poor for range scans
AVG_LEAF_BLOCKS_PER_KEY > 50: May indicate over-indexing
Stale LAST_ANALYZED: Statistics need refreshing

Selectivity Assessment

-- Calculate selectivity percentage
SELECT owner, table_name, index_name,
       ROUND((distinct_keys/num_rows)*100, 2) selectivity_pct,
       CASE
         WHEN (distinct_keys/num_rows) > 0.9 THEN 'High Selectivity'
         WHEN (distinct_keys/num_rows) > 0.1 THEN 'Medium Selectivity'
         ELSE 'Low Selectivity'
       END selectivity_rating
FROM dba_indexes
WHERE num_rows > 0;

Common Use Cases

Index Health Assessment

-- Identify indexes needing attention
@dindstats.sql
-- Look for high BLEVEL (>4)
-- Check CLUSTERING_FACTOR vs NUM_ROWS
-- Verify recent LAST_ANALYZED dates

Query Performance Investigation

-- Analyze indexes for specific table
@dindstats.sql
-- Enter specific table name
-- Evaluate index appropriateness for query patterns

Index Maintenance Planning

-- Find candidates for rebuilding
@dindstats.sql
-- Focus on high CLUSTERING_FACTOR
-- Consider space usage (LEAF_BLOCKS)

Troubleshooting Index Issues

Poor Range Scan Performance

High clustering factor:

-- Check if rebuild helps
ALTER INDEX index_name REBUILD;

-- Or consider partitioning
-- for very large tables

Table reorganization:

-- Improve physical ordering
ALTER TABLE table_name MOVE;
ALTER INDEX index_name REBUILD;

Index Bloat Analysis

-- Calculate index efficiency
SELECT index_name,
       leaf_blocks,
       num_rows,
       ROUND(leaf_blocks / (num_rows/100), 2) blocks_per_100_rows,
       CASE
         WHEN leaf_blocks / (num_rows/100) > 2 THEN 'Consider Rebuild'
         ELSE 'OK'
       END recommendation
FROM dba_indexes
WHERE num_rows > 1000;

Clustering Factor Analysis

-- Identify poorly clustered indexes
SELECT owner, table_name, index_name,
       clustering_factor,
       num_rows,
       ROUND((clustering_factor/num_rows)*100, 2) clustering_pct,
       CASE
         WHEN clustering_factor > (num_rows * 0.5) THEN 'Poor Clustering'
         WHEN clustering_factor > (num_rows * 0.1) THEN 'Fair Clustering'
         ELSE 'Good Clustering'
       END clustering_quality
FROM dba_indexes
WHERE num_rows > 0
ORDER BY clustering_pct DESC;

Advanced Analysis

Index Usage Validation

-- Cross-reference with index usage stats
SELECT i.owner, i.table_name, i.index_name,
       i.num_rows, i.distinct_keys,
       u.total_access_count,
       u.total_exec_count
FROM dba_indexes i, v$index_usage_info u
WHERE i.owner = u.owner
AND i.index_name = u.name
ORDER BY u.total_access_count DESC;

Composite Index Analysis

-- Analyze multi-column index effectiveness
SELECT i.owner, i.table_name, i.index_name,
       COUNT(c.column_position) column_count,
       i.distinct_keys,
       i.num_rows,
       ROUND((i.distinct_keys/i.num_rows)*100, 2) selectivity
FROM dba_indexes i, dba_ind_columns c
WHERE i.owner = c.index_owner
AND i.index_name = c.index_name
GROUP BY i.owner, i.table_name, i.index_name,
         i.distinct_keys, i.num_rows
HAVING COUNT(c.column_position) > 1
ORDER BY selectivity DESC;

Statistics Freshness Check

-- Find indexes with stale statistics
SELECT owner, table_name, index_name,
       last_analyzed,
       ROUND(SYSDATE - last_analyzed) days_old,
       CASE
         WHEN last_analyzed IS NULL THEN 'Never Analyzed'
         WHEN SYSDATE - last_analyzed > 30 THEN 'Stale Statistics'
         WHEN SYSDATE - last_analyzed > 7 THEN 'Consider Refresh'
         ELSE 'Current'
       END stats_status
FROM dba_indexes
WHERE owner NOT IN ('SYS','SYSTEM')
ORDER BY last_analyzed NULLS FIRST;

Table Statistics (dtable.sql) - Table-level statistics
Index Definitions (dindex.sql) - Index structure details
Index Usage (../performance-analysis/vindex.md) - Index access patterns
Column Statistics (dtabcol.sql) - Column-level statistics