Sub-Partitioned Index Statistics Analysis (dstatindsub.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 analyzes statistics for sub-partitioned indexes in composite partitioned tables, providing detailed information about statistical quality, distribution, and freshness across all subpartitions. It’s essential for monitoring optimizer statistics health in complex partitioning schemes where tables are partitioned by one key and sub-partitioned by another (e.g., RANGE-HASH or RANGE-LIST partitioning).

The Script

rem  dstatindsub.sql
rem
ttitle 'Sub-Partitioned Index Statistics'
rem
set linesize 145
rem
col table_name format a22 heading 'TABLE NAME'
col index_name format a21 heading 'INDEX NAME'
col subpartition_position format 999 head 'POS'
col partition_name format a11 heading 'PARTITION'
col subpartition_name format a13 heading 'SUBPARTITION'
col u format a1 heading 'U'
col global_stats format a3 heading 'GBL'
col blevel format 90 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 9999990 heading 'DATA|BLKS|/KEY'
col LAST_ANALYZED format a17 heading 'LAST|ANALYZED'
col SAMPLE_SIZE format 999,999 heading 'SAMPLE|SIZE'
col samp_pct format 990 heading 'SAMPLE|PCT'
rem
break on owner skip 1 on table_name skip 1 on index_name skip 1
compute sum of num_rows blocks empty_blocks on owner table_name
rem
select i.table_name, i.index_name,
       p.partition_name,
       p.subpartition_position,
       p.subpartition_name,
       decode( i.uniqueness, 'UNIQUE', 'U', null ) u,
       p.global_stats,
       p.blevel, p.leaf_blocks, p.distinct_keys,
       p.avg_leaf_blocks_per_key, p.avg_data_blocks_per_key,
       to_char(p.last_analyzed, 'MM/DD/YY HH24:MI:SS') last_analyzed,
       p.sample_size / decode(p.num_rows, 0, 1, p.num_rows) * 100 samp_pct
  from sys.dba_indexes i, sys.dba_ind_subpartitions p
 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.index_owner = i.owner
   and p.index_name = i.index_name
 order by
   i.table_owner,
   i.table_name,
   i.index_name,
   p.partition_name,
   p.subpartition_position;

Key Features

• Comprehensive Sub-Partition Analysis: Shows statistics for all index subpartitions
• Multiple Filtering Options: Filter by owner, table, index, partition, and subpartition
• Statistics Quality Assessment: Displays sample sizes, freshness, and completeness
• Index Structure Metrics: Shows B-tree levels, leaf blocks, and key distributions
• Hierarchical Display: Organized by table, index, partition, and subpartition
• Global Statistics Indicator: Shows whether global statistics are available
• Performance Metrics: Key ratios for index efficiency analysis

Usage

@dstatindsub.sql

Input Parameters:

• owner: Schema owner name or pattern (press Enter for all)
• table: Table name or pattern (press Enter for all)
• index: Index name or pattern (press Enter for all)
• partition: Partition name or pattern (press Enter for all)
• subpartition: Subpartition name or pattern (press Enter for all)

Required Privileges

SELECT on SYS.DBA_INDEXES
SELECT on SYS.DBA_IND_SUBPARTITIONS

Sample Output

                                              Sub-Partitioned Index Statistics

TABLE NAME             INDEX NAME             PARTITION   POS SUBPARTITION  U GBL BL   LEAF DISTINCT  LEAF    DATA LAST             SAMPLE SAMPLE
                                                                                  BLOCKS     KEYS BLKS   BLKS ANALYZED         SIZE    PCT
                                                                                              /KEY   /KEY
---------------------- --------------------- ----------- --- ------------- - --- -- ------ ------- ---- ------ ----------------- ------ ------
SALES_HISTORY          SALES_HIST_IDX        P_2023_Q1     1 SP_JAN_WEST    YES  2     45   12,345    1      1 01/15/24 08:30:00  1,234     10
                                                           2 SP_JAN_CENT      YES  2     52   14,567    1      1 01/15/24 08:30:00  1,456     10
                                                           3 SP_JAN_EAST      YES  2     48   13,234    1      1 01/15/24 08:30:00  1,323     10

                                             P_2023_Q2     4 SP_APR_WEST      YES  2     67   18,901    1      1 04/15/24 08:30:00  1,890     10
                                                           5 SP_APR_CENT      YES  2     72   19,567    1      1 04/15/24 08:30:00  1,956     10
                                                           6 SP_APR_EAST      YES  2     69   19,123    1      1 04/15/24 08:30:00  1,912     10

ORDER_DETAILS          ORD_DET_PROD_IDX      P_2024_Q1     1 SP_PROD_A       U   YES  3    124   45,678    2      3 11/01/24 09:15:00  4,567     10
                                                           2 SP_PROD_B       U   YES  3    118   43,234    2      3 11/01/24 09:15:00  4,323     10
                                                           3 SP_PROD_C       U   YES  3    132   47,890    2      3 11/01/24 09:15:00  4,789     10

Key Output Columns

• TABLE NAME: Name of the partitioned table
• INDEX NAME: Name of the sub-partitioned index
• PARTITION: Parent partition name
• POS: Subpartition position within the partition
• SUBPARTITION: Subpartition name
• U: Uniqueness indicator (U=UNIQUE, blank=non-unique)
• GBL: Global statistics available (YES/NO)
• BL: B-tree level (index depth)
• LEAF BLOCKS: Number of leaf blocks in the subpartition
• DISTINCT KEYS: Number of distinct key values
• LEAF BLKS/KEY: Average leaf blocks per distinct key
• DATA BLKS/KEY: Average data blocks per distinct key
• LAST ANALYZED: Date and time of last statistics gathering
• SAMPLE SIZE: Number of rows sampled for statistics
• SAMPLE PCT: Percentage of rows sampled

Understanding Sub-Partitioned Index Statistics

Composite Partitioning Types

Range-Hash Partitioning

-- Example: Sales data partitioned by date, sub-partitioned by region
-- Partition: P_2024_Q1 (date range)
-- Subpartitions: SP_REGION_1, SP_REGION_2, SP_REGION_3 (hash)

Range-List Partitioning

-- Example: Orders partitioned by date, sub-partitioned by status
-- Partition: P_2024_JAN (date range)
-- Subpartitions: SP_PENDING, SP_SHIPPED, SP_DELIVERED (list)

List-Hash Partitioning

-- Example: Customer data partitioned by region, sub-partitioned by ID
-- Partition: P_WEST (region list)
-- Subpartitions: SP_CUST_1, SP_CUST_2, SP_CUST_3 (hash)

Statistics Quality Indicators

Freshness Assessment

-- Statistics freshness evaluation:
-- Recent LAST_ANALYZED = Good
-- Old LAST_ANALYZED = May need refresh
-- NULL LAST_ANALYZED = Never analyzed
-- Compare with data modification patterns

Sample Quality

-- Sample size evaluation:
-- SAMPLE_PCT >= 10% = Generally good
-- SAMPLE_PCT < 5% = May be insufficient
-- SAMPLE_SIZE vs. actual rows = Quality indicator
-- Consider data skew and distribution

Global Statistics

-- Global statistics (GBL column):
-- YES = Partition-level statistics available
-- NO = Only subpartition-level statistics
-- Affects optimizer plan selection
-- Important for cross-partition queries

Common Use Cases

1. Statistics Health Monitoring

   • Monitor statistics freshness across subpartitions
   • Identify subpartitions needing statistics refresh
   • Assess statistics quality and completeness
   • Plan automated statistics gathering

2. Partitioning Strategy Evaluation

   • Analyze subpartition size distribution
   • Assess partitioning effectiveness
   • Identify unbalanced subpartitions
   • Plan partitioning modifications

3. Performance Optimization

   • Identify indexes with poor statistics
   • Analyze index structure efficiency
   • Plan index maintenance activities
   • Support query optimization efforts

4. Capacity Planning

   • Monitor subpartition growth patterns
   • Assess storage requirements
   • Plan index maintenance windows
   • Support infrastructure planning

Advanced Analysis

Index Structure Analysis

B-Tree Level Assessment

-- B-tree level (BL) interpretation:
-- Level 1-2: Efficient for small-medium subpartitions
-- Level 3: Normal for larger subpartitions
-- Level 4+: May indicate need for rebuild or splitting

Block Distribution Analysis

-- Leaf blocks analysis:
-- High LEAF_BLKS/KEY: Potential space waste
-- Low DISTINCT_KEYS: Poor selectivity
-- Uneven distribution: Subpartition imbalance

Performance Indicators

Index Efficiency Metrics

-- Efficiency calculations:
-- Clustering factor assessment
-- Key distribution analysis
-- Block utilization evaluation
-- Access pattern optimization

Selectivity Analysis

-- Selectivity evaluation:
-- DISTINCT_KEYS vs. total rows
-- Key distribution patterns
-- Predicate effectiveness
-- Plan optimization potential

Troubleshooting Applications

Statistics Issues

Stale Statistics

-- Identify stale statistics:
-- Compare LAST_ANALYZED with data changes
-- Check for recent partition operations
-- Assess impact on query performance
-- Plan statistics refresh schedule

Missing Statistics

-- Handle missing statistics:
-- Identify never-analyzed subpartitions
-- Check for new subpartitions
-- Plan comprehensive statistics gathering
-- Consider automated maintenance

Poor Statistics Quality

-- Address quality issues:
-- Increase sample sizes for large subpartitions
-- Use appropriate estimation methods
-- Consider histogram collection
-- Monitor statistics accuracy

Performance Problems

Suboptimal Plans

-- Plan quality issues:
-- Cross-partition joins inefficiency
-- Partition pruning failures
-- Index selection problems
-- Cost estimation errors

Index Maintenance

-- Index maintenance needs:
-- High B-tree levels
-- Fragmented leaf blocks
-- Poor space utilization
-- Rebuild requirements

Filtering Examples

Specific Table Analysis

-- Analyze specific table:
Enter value for owner: SALES
Enter value for table: ORDER_HISTORY
Enter value for index: %
Enter value for partition: %
Enter value for subpartition: %

Partition-Specific Review

-- Review specific partition:
Enter value for owner: %
Enter value for table: %
Enter value for index: %
Enter value for partition: P_2024_Q1
Enter value for subpartition: %

Index-Specific Analysis

-- Analyze specific index:
Enter value for owner: HR
Enter value for table: %
Enter value for index: EMP_DEPT_IDX
Enter value for partition: %
Enter value for subpartition: %

Subpartition Pattern Search

-- Search subpartition patterns:
Enter value for owner: %
Enter value for table: %
Enter value for index: %
Enter value for partition: %
Enter value for subpartition: SP_WEST%

Statistics Maintenance Strategy

Automated Maintenance

-- Implement automated statistics:
-- Schedule regular gathering jobs
-- Use incremental statistics where possible
-- Monitor statistics job performance
-- Alert on maintenance failures

Incremental Statistics

-- Leverage incremental statistics:
-- Enable for large composite partitioned tables
-- Monitor synopsis table maintenance
-- Plan global statistics refresh
-- Handle partition operations properly

Manual Maintenance

-- Manual statistics procedures:
-- Gather after major data loads
-- Refresh after partition operations
-- Update after significant data changes
-- Validate statistics quality

Integration with Monitoring

Performance Monitoring

-- Integrate with performance tools:
-- Monitor query performance by subpartition
-- Track plan stability
-- Assess optimization effectiveness
-- Report performance trends

Capacity Monitoring

-- Monitor capacity metrics:
-- Track subpartition growth
-- Monitor space utilization
-- Plan maintenance windows
-- Support capacity planning

Automation Integration

-- Automated monitoring:
-- Alert on stale statistics
-- Report statistics quality issues
-- Monitor maintenance job status
-- Generate health reports

Best Practices

Statistics Gathering

-- Follow best practices:
-- Use appropriate sample sizes
-- Gather global statistics
-- Schedule during maintenance windows
-- Monitor gathering performance

Maintenance Planning

-- Plan maintenance activities:
-- Coordinate with application teams
-- Schedule during low-usage periods
-- Monitor impact on query performance
-- Validate results after gathering

Quality Assurance

-- Ensure statistics quality:
-- Validate sample sizes
-- Check for data skew
-- Monitor plan stability
-- Review performance impact

Related Scripts

• dstatind.sql - Index statistics analysis
• dstattab.sql - Table statistics analysis
• dstattabsub.sql - Sub-partitioned table statistics
• dtabpriv.sql - Table privileges analysis

Summary

This script is essential for:

1. Statistics Health Monitoring - Tracking statistics quality across subpartitions
2. Performance Optimization - Identifying statistics-related performance issues
3. Partitioning Strategy - Evaluating composite partitioning effectiveness
4. Maintenance Planning - Supporting automated and manual statistics maintenance
5. Capacity Management - Monitoring subpartition growth and resource utilization