Index Subpartition Statistics (dindsub.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 subpartition analysis by:

• Displaying detailed statistics for each index subpartition
• Showing storage parameters and space utilization
• Including performance metrics like clustering factor and B-level
• Supporting flexible filtering by owner, table, index, partition, and tablespace
• Organizing output by table, index, and partition hierarchy
• Providing status information for partition maintenance

Script

rem  dindsub.sql
rem
rem
rem set linesize 215
set linesize 132
rem
ttitle 'Index Sub-Partition Statistics'
rem
col table_owner format a6 heading 'OWNER'
col table_name format a24 heading 'TABLE NAME'
col index_name format a30 heading 'INDEX NAME'
col index_type format a7 heading 'TYPE'
col partition_name format a10 heading 'PARTITION'
col subpartition_name format a13 heading 'SUB PARTITION'
col subpartition_position form 999 heading 'POS'
col high_value format a15 heading 'HIGH VALUE'
col tablespace_name format a14 heading 'TABLESPACE'
col initial_extent format 9,999,999 heading 'INITIAL|EXTENT|(K)'
col next_extent format 9,999,999 heading 'NEXT|EXTENT|(K)'
col pct_increase format 999 heading 'PCT|INC'
col pct_free format 999 heading 'PCT|FREE'
col min_extents format 99 heading 'MIN|EXT'
col max_extents format 999 heading 'MAX|EXT'
col LAST_ANALYZED format a9 heading 'LAST|ANALYZED'
col SAMPLE_SIZE format 999,999 heading 'SAMPLE|SIZE'
col NUM_ROWS format 999,999,999
col BLEVEL format 99
col DISTINCT_KEYS format 9,999,999 heading 'DISTINCT|KEYS'
col LEAF_BLOCKS format 99,999 heading 'LEAF|BLOCKS'
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 a1 heading 'S'
col ini_trans format 999 heading 'INI|TRAN'
col max_trans format 999 heading 'MAX|TRAN'
col freelists format 999 heading 'FREE|LISTS'
rem
break on table_owner on table_name on index_name on index_type skip 1
rem
select
      i.table_owner,
      i.table_name,
      i.index_name,
      i.index_type,
      p.partition_name,
      p.subpartition_position,
      p.subpartition_name,
      decode( p.status, 'VALID', null, 'DIRECT LOAD', 'L', 'USABLE', null, 'UNUSABLE', 'U', '?' ) status,
      p.ini_trans,
      p.max_trans,
      p.pct_free,
      p.freelists,
      p.high_value,
      p.tablespace_name,
      p.last_analyzed,
      p.sample_size,
      p.num_rows,
      p.LOGGING,
      p.BLEVEL,
      p.LEAF_BLOCKS,
      p.DISTINCT_KEYS,
      p.AVG_LEAF_BLOCKS_PER_KEY,
      p.AVG_DATA_BLOCKS_PER_KEY,
      p.CLUSTERING_FACTOR
 from sys.dba_ind_subpartitions p, sys.dba_indexes i
where i.table_owner like nvl(upper('&owner'), '%')
  and i.table_name like nvl(upper('&table'), '%')
  and i.index_name like nvl(upper('&index'), '%')
  and p.partition_name like nvl(upper('&partition'), '%')
  and p.subpartition_name like nvl(upper('&subpartition'), '%')
  and p.tablespace_name like nvl(upper('&tablespace'), '%')
  and p.index_owner = i.owner
  and p.index_name = i.index_name
order by 1, 2, 3, 5, 6
/
rem
set linesize 80

Usage

SQL> @dindsub.sql
Enter value for owner: SALES
Enter value for table: ORDERS
Enter value for index: %
Enter value for partition: %
Enter value for subpartition: %
Enter value for tablespace: %

Parameters

• owner: Table owner pattern (% for all, specific name for single owner)
• table: Table name pattern (% for all, specific name for single table)
• index: Index name pattern (% for all, specific pattern for filtering)
• partition: Partition name pattern (% for all partitions)
• subpartition: Subpartition name pattern (% for all subpartitions)
• tablespace: Tablespace name pattern (% for all tablespaces)

Required Privileges

• SELECT on SYS.DBA_IND_SUBPARTITIONS
• SELECT on SYS.DBA_INDEXES

Sample Output

Index Sub-Partition Statistics

OWNER  TABLE NAME               INDEX NAME                     TYPE    PARTITION  POS SUB PARTITION S INI MAX PCT FREE LOG
                                                                                       TRAN TRAN FREE LISTS
------ ------------------------ ------------------------------ ------- ---------- --- ------------- - --- --- --- ---- ---
SALES  ORDERS                   ORDERS_DATE_IDX                NORMAL  P2024_Q1     1 SP_JAN_2024     2   255  10    1 YES
                                                                        P2024_Q1     2 SP_FEB_2024     2   255  10    1 YES
                                                                        P2024_Q1     3 SP_MAR_2024     2   255  10    1 YES
                                                                        P2024_Q2     4 SP_APR_2024     2   255  10    1 YES
                                                                        P2024_Q2     5 SP_MAY_2024   U 2   255  10    1 YES

       ORDER_ITEMS               ITEMS_PROD_IDX                 NORMAL  P2024_Q1     1 SP_JAN_2024     2   255  10    1 YES
                                                                        P2024_Q1     2 SP_FEB_2024     2   255  10    1 YES

Key Output Columns

Identification

• OWNER: Table owner
• TABLE NAME: Table associated with the index
• INDEX NAME: Index name
• TYPE: Index type (NORMAL, BITMAP, etc.)
• PARTITION: Partition name
• POS: Subpartition position
• SUB PARTITION: Subpartition name

Status and Configuration

• S: Status (U = Unusable, L = Direct Load, blank = Usable)
• INI TRAN: Initial transaction slots
• MAX TRAN: Maximum transaction slots
• PCT FREE: Percentage of free space reserved
• FREE LISTS: Number of free lists
• LOG: Logging enabled (YES/NO)

Statistics and Performance

• NUM_ROWS: Number of rows in the subpartition
• BLEVEL: B-tree level (height excluding leaf level)
• LEAF_BLOCKS: Number of leaf blocks
• DISTINCT_KEYS: Number of distinct key values
• AVG LEAF BLOCKS PER KEY: Average leaf blocks per key
• AVG DATA BLOCKS PER KEY: Average data blocks per key
• CLUSTERING_FACTOR: Measure of row ordering relative to index

Administrative

• HIGH VALUE: Partition boundary value
• TABLESPACE: Tablespace containing the subpartition
• LAST_ANALYZED: When statistics were last collected
• SAMPLE_SIZE: Number of rows sampled for statistics

Understanding Subpartition Metrics

B-Tree Structure Analysis

• BLEVEL 0-1: Very small subpartitions
• BLEVEL 2-3: Normal size subpartitions
• BLEVEL 4+: Large subpartitions, may need attention

Clustering Factor Analysis

• Low clustering factor: Rows are well-ordered relative to index
• High clustering factor: Rows are randomly distributed
• CF ≈ Number of blocks: Well-clustered data
• CF ≈ Number of rows: Poorly clustered data

Selectivity Analysis

-- Calculate selectivity for each subpartition
SELECT index_name, subpartition_name,
       num_rows, distinct_keys,
       ROUND(distinct_keys * 100 / GREATEST(num_rows, 1), 2) selectivity_pct,
       CASE
         WHEN distinct_keys * 100 / GREATEST(num_rows, 1) > 90 THEN 'Excellent'
         WHEN distinct_keys * 100 / GREATEST(num_rows, 1) > 50 THEN 'Good'
         WHEN distinct_keys * 100 / GREATEST(num_rows, 1) > 10 THEN 'Fair'
         ELSE 'Poor'
       END selectivity_rating
FROM dba_ind_subpartitions
WHERE index_owner = 'SALES'
ORDER BY selectivity_pct DESC;

Common Use Cases

1. Partition Maintenance

   • Monitor subpartition statistics currency
   • Identify unusable subpartitions
   • Plan statistics collection

2. Performance Analysis

   • Analyze clustering factors across subpartitions
   • Identify skewed data distribution
   • Optimize partition pruning

3. Storage Management

   • Monitor space utilization by subpartition
   • Plan tablespace allocation
   • Optimize storage parameters

Subpartition Maintenance

Statistics Collection

-- Gather statistics for specific subpartition
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(
    ownname => 'SALES',
    indname => 'ORDERS_DATE_IDX',
    partname => 'SP_JAN_2024',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
  );
END;
/

Rebuilding Unusable Subpartitions

-- Generate rebuild commands for unusable subpartitions
SELECT 'ALTER INDEX ' || index_owner || '.' || index_name ||
       ' REBUILD SUBPARTITION ' || subpartition_name || ';'
FROM dba_ind_subpartitions
WHERE status = 'UNUSABLE'
AND index_owner = 'SALES';

Monitoring Partition Health

-- Check for maintenance issues
SELECT index_owner, index_name,
       COUNT(*) total_subpartitions,
       SUM(CASE WHEN status IS NULL THEN 1 ELSE 0 END) usable_count,
       SUM(CASE WHEN status = 'U' THEN 1 ELSE 0 END) unusable_count,
       SUM(CASE WHEN last_analyzed IS NULL THEN 1 ELSE 0 END) no_stats_count
FROM dba_ind_subpartitions
WHERE index_owner = 'SALES'
GROUP BY index_owner, index_name
ORDER BY unusable_count DESC, no_stats_count DESC;

Performance Optimization

Clustering Factor Analysis

-- Identify poorly clustered subpartitions
SELECT index_name, subpartition_name,
       clustering_factor, num_rows,
       ROUND(clustering_factor * 100 / GREATEST(num_rows, 1), 2) clustering_ratio
FROM dba_ind_subpartitions
WHERE index_owner = 'SALES'
AND clustering_factor > num_rows * 0.1  -- Poor clustering threshold
ORDER BY clustering_ratio DESC;

Storage Efficiency

-- Calculate storage efficiency
SELECT index_name, subpartition_name,
       leaf_blocks, num_rows,
       ROUND(num_rows / GREATEST(leaf_blocks, 1), 2) rows_per_block,
       CASE
         WHEN num_rows / GREATEST(leaf_blocks, 1) < 50 THEN 'Low Density'
         WHEN num_rows / GREATEST(leaf_blocks, 1) < 200 THEN 'Medium Density'
         ELSE 'High Density'
       END storage_efficiency
FROM dba_ind_subpartitions
WHERE index_owner = 'SALES'
AND leaf_blocks > 0
ORDER BY rows_per_block;

Subpartition Pruning Analysis

Partition Elimination Testing

-- Test partition pruning effectiveness
EXPLAIN PLAN FOR
SELECT * FROM sales.orders
WHERE order_date BETWEEN DATE '2024-01-01' AND DATE '2024-01-31';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- Look for "Pstart" and "Pstop" values showing pruning

Pruning Statistics

-- Monitor partition access patterns
SELECT partition_name, subpartition_name,
       object_type, statistic_name, value
FROM v$segment_statistics ss, dba_ind_subpartitions sp
WHERE ss.object_name = sp.index_name
AND ss.subobject_name = sp.subpartition_name
AND ss.owner = sp.index_owner
AND sp.index_owner = 'SALES'
AND ss.statistic_name IN ('logical reads', 'physical reads')
ORDER BY value DESC;

Advanced Analysis

Cross-Subpartition Comparison

-- Compare statistics across subpartitions
SELECT index_name,
       AVG(num_rows) avg_rows,
       MIN(num_rows) min_rows,
       MAX(num_rows) max_rows,
       STDDEV(num_rows) stddev_rows,
       COUNT(*) subpartition_count
FROM dba_ind_subpartitions
WHERE index_owner = 'SALES'
GROUP BY index_name
ORDER BY stddev_rows DESC;

Statistics Staleness Analysis

-- Find subpartitions with stale statistics
SELECT index_name, subpartition_name,
       last_analyzed,
       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_ind_subpartitions
WHERE index_owner = 'SALES'
ORDER BY days_old DESC NULLS FIRST;

Troubleshooting Common Issues

Unusable Subpartitions

Causes:

• Failed partition maintenance operations
• Direct path loads without enabling parallel DML
• Partition exchange operations

Solutions:

• Rebuild unusable subpartitions
• Re-gather statistics
• Check for underlying table issues

Poor Performance

Symptoms:

• High clustering factors
• Skewed data distribution
• Missing statistics

Actions:

• Rebuild indexes with better clustering
• Redistribute data if possible
• Ensure regular statistics collection

Storage Issues

Problems:

• Excessive space usage
• Poor block utilization
• Fragmented storage

Remedies:

• Rebuild with appropriate storage parameters
• Consider index compression
• Optimize PCTFREE settings

Best Practices

1. Regular Monitoring

   • Check subpartition status weekly
   • Monitor statistics currency
   • Track storage growth patterns

2. Maintenance Planning

   • Schedule regular statistics collection
   • Plan rebuilds during maintenance windows
   • Document partition maintenance procedures

3. Performance Optimization

   • Monitor clustering factors
   • Optimize partition key selection
   • Plan for data skew

Related Scripts

• Index Analysis
• Index Statistics Analysis
• Table Statistics
• Partition Analysis