Sub-Partitioned Table Statistics Analysis (dstattabsub.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 tables in composite partitioning schemes, providing detailed information about row counts, storage utilization, and statistics quality across all subpartitions. It’s essential for monitoring data distribution, statistics health, and performance optimization in complex partitioning environments where tables are partitioned by one key and sub-partitioned by another.

The Script

rem  dstattabpartsub.sql
rem
ttitle 'Sub-Partitioned Table Statistics'
rem
rem set linesize 145
set linesize 500
rem
col owner format a8 heading 'OWNER'
col table_name format a22 heading 'TABLE NAME'
col subpartition_position format 999 head 'POS'
col partition_name format a13 heading 'PARTITION'
col subpartition_name format a22 heading 'SUBPARTITION'
col global_stats format a3 heading 'GBL'
col num_rows format 999,999,990 heading 'ROWS'
col chain_cnt format 9,990 heading 'CHAIN|COUNT'
col avg_row_len format 9,990 heading 'AVG|ROW|SIZE'
col blocks format 999,990 heading 'USED|BLOCKS'
col empty_blocks format 999,990 heading 'EMPTY|BLOCKS'
col avg_space format 9,990 heading 'AVG|FREE|SPACE'
col LAST_ANALYZED format a17 heading 'LAST|ANALYZED'
col SAMPLE_SIZE format 999,999 heading 'SAMPLE|SIZE'
col samp_pct format 999 heading 'SAMP|PCT'
rem
break on owner skip 1 on table_name
compute sum of num_rows blocks empty_blocks on owner table_name
rem
select table_owner owner,
       table_name,
       partition_name,
       subpartition_position,
       subpartition_name,
       global_stats,
       num_rows,
--       chain_cnt,
       avg_row_len,
       blocks,
--       empty_blocks,
--       avg_space,
       to_char(LAST_ANALYZED, 'MM/DD/YY HH24:MI:SS') last_analyzed,
       (SAMPLE_SIZE / decode(num_rows, 0, 1, num_rows))*100 samp_pct
  from sys.dba_tab_subpartitions
 where table_owner like nvl(upper('&owner'), '%')
   and table_name like nvl(upper('&table'), '%')
   and partition_name like nvl(upper('&partition'), '%')
   and subpartition_name like nvl(upper('&subpartition'), '%')
 order by table_owner, table_name, partition_name, subpartition_position;

Key Features

• Comprehensive Sub-Partition Analysis: Shows statistics for all table subpartitions
• Multiple Filtering Options: Filter by owner, table, partition, and subpartition
• Statistics Quality Assessment: Displays sample sizes, freshness, and global statistics status
• Storage Metrics: Shows row counts, block usage, and space utilization
• Hierarchical Display: Organized by owner, table, partition, and subpartition
• Data Distribution Analysis: Reveals partition balance and skew patterns
• Performance Indicators: Key metrics for query optimization and capacity planning

Usage

@dstattabsub.sql

Input Parameters:

• owner: Schema owner name or pattern (press Enter for all)
• table: Table 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_TAB_SUBPARTITIONS

Sample Output

                                                    Sub-Partitioned Table Statistics

OWNER    TABLE NAME             PARTITION     POS SUBPARTITION           GBL        ROWS AVG USED   LAST             SAMPLE SAMP
                                                                                      ROW BLOCKS ANALYZED         SIZE PCT
                                                                                     SIZE
-------- ---------------------- ------------- --- ---------------------- --- ----------- --- ------ ----------------- ------ ----
SALES    ORDER_HISTORY          P_2023_Q1       1 SP_REGION_WEST         YES   1,234,567  85  2,468 01/15/24 08:30:00 12,345   1
                                                 2 SP_REGION_CENTRAL      YES   1,156,789  87  2,314 01/15/24 08:30:00 11,567   1
                                                 3 SP_REGION_EAST         YES   1,345,234  83  2,690 01/15/24 08:30:00 13,452   1

                                P_2023_Q2       4 SP_REGION_WEST         YES   1,567,890  84  3,136 04/15/24 08:30:00 15,678   1
                                                 5 SP_REGION_CENTRAL      YES   1,434,567  86  2,869 04/15/24 08:30:00 14,345   1
                                                 6 SP_REGION_EAST         YES   1,678,901  82  3,358 04/15/24 08:30:00 16,789   1
sum                                                                             8,417,948     17,835

HR       EMPLOYEE_AUDIT         P_2024_Q1       1 SP_DEPT_SALES          YES     456,789  92    913 11/01/24 09:15:00  4,567   1
                                                 2 SP_DEPT_MARKETING      YES     234,567  95    469 11/01/24 09:15:00  2,345   1
                                                 3 SP_DEPT_ENGINEERING    YES     678,901  88  1,358 11/01/24 09:15:00  6,789   1
                                                 4 SP_DEPT_SUPPORT        YES     123,456  98    247 11/01/24 09:15:00  1,234   1
sum                                                                             1,493,713      2,987

Key Output Columns

• OWNER: Schema owner of the sub-partitioned table
• TABLE NAME: Name of the composite partitioned table
• PARTITION: Parent partition name
• POS: Subpartition position within the partition
• SUBPARTITION: Subpartition name
• GBL: Global statistics available (YES/NO)
• ROWS: Number of rows in the subpartition
• AVG ROW SIZE: Average row length in bytes
• USED BLOCKS: Number of blocks containing data
• LAST ANALYZED: Date and time of last statistics gathering
• SAMPLE SIZE: Number of rows sampled for statistics
• SAMP PCT: Percentage of rows sampled

Understanding Sub-Partitioned Table Statistics

Composite Partitioning Schemes

Range-Hash Partitioning

-- Example: Sales data by date range, sub-partitioned by hash
-- Partition: P_2024_Q1 (date range: Jan-Mar 2024)
-- Subpartitions: SP_REGION_1, SP_REGION_2, SP_REGION_3 (hash)
-- Benefits: Time-based maintenance + parallel processing

Range-List Partitioning

-- Example: Orders by date range, sub-partitioned by status
-- Partition: P_2024_JAN (date range: January 2024)
-- Subpartitions: SP_PENDING, SP_SHIPPED, SP_DELIVERED (list)
-- Benefits: Time-based maintenance + status-based access

List-Hash Partitioning

-- Example: Customer data by region, sub-partitioned by hash
-- Partition: P_WEST (region list: CA, OR, WA)
-- Subpartitions: SP_CUST_1, SP_CUST_2, SP_CUST_3 (hash)
-- Benefits: Region-based access + load distribution

Statistics Quality Assessment

Freshness Evaluation

-- Statistics freshness criteria:
-- Recent LAST_ANALYZED (< 7 days): Good
-- Moderate age (7-30 days): Monitor
-- Old statistics (> 30 days): Refresh needed
-- NULL LAST_ANALYZED: Never analyzed

Sample Quality Analysis

-- Sample size evaluation:
-- SAMP_PCT >= 10%: Excellent quality
-- SAMP_PCT 5-10%: Good quality
-- SAMP_PCT 1-5%: Adequate quality
-- SAMP_PCT < 1%: May be insufficient

Global Statistics Importance

-- Global statistics (GBL column):
-- YES: Partition-level aggregated statistics available
-- NO: Only subpartition-level statistics
-- Critical for cross-partition queries
-- Affects optimizer cost calculations

Common Use Cases

1. Data Distribution Analysis

   • Monitor subpartition size balance
   • Identify data skew patterns
   • Assess partitioning effectiveness
   • Plan partition maintenance

2. Statistics Health Monitoring

   • Track statistics freshness across subpartitions
   • Identify subpartitions needing refresh
   • Monitor sample quality
   • Plan automated statistics gathering

3. Performance Optimization

   • Analyze query performance by subpartition
   • Identify partitions causing plan instability
   • Support partition pruning optimization
   • Plan index strategies

4. Capacity Planning

   • Monitor subpartition growth trends
   • Assess storage requirements
   • Plan partition splits or merges
   • Support infrastructure planning

Advanced Analysis

Data Distribution Patterns

Balanced Distribution

-- Good distribution indicators:
-- Similar row counts across subpartitions
-- Consistent average row sizes
-- Uniform block usage
-- Effective partition pruning

Skewed Distribution

-- Skew indicators:
-- Large variance in row counts
-- Some subpartitions much larger
-- Uneven resource utilization
-- Performance bottlenecks

Growth Patterns

-- Growth analysis:
-- Compare subpartition sizes over time
-- Identify fastest-growing subpartitions
-- Plan capacity accordingly
-- Monitor partition boundaries

Storage Efficiency

Block Utilization

-- Storage efficiency metrics:
-- Rows per block ratios
-- Average row size trends
-- Block packing efficiency
-- Space utilization patterns

Compression Analysis

-- Compression effectiveness:
-- Compare compressed vs. uncompressed sizes
-- Analyze compression ratios by subpartition
-- Monitor compression performance impact
-- Plan compression strategies

Troubleshooting Applications

Performance Issues

Partition Pruning Problems

-- Pruning inefficiency signs:
-- Missing or stale statistics
-- Poor predicate selectivity
-- Cross-partition data access
-- Suboptimal execution plans

Query Performance Degradation

-- Performance problem indicators:
-- Uneven subpartition access patterns
-- Large subpartitions causing bottlenecks
-- Statistics staleness impact
-- Plan instability across partitions

Data Management Issues

Partition Maintenance

-- Maintenance challenges:
-- Large subpartitions taking too long
-- Unbalanced maintenance windows
-- Statistics gathering overhead
-- Partition operations complexity

Data Loading Problems

-- Loading issues:
-- Uneven data distribution during loads
-- Partition key selection problems
-- Constraint validation overhead
-- Index maintenance impact

Filtering and Analysis Examples

Specific Table Analysis

-- Analyze specific table:
Enter value for owner: SALES
Enter value for table: ORDER_HISTORY
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 partition: P_2024_Q1
Enter value for subpartition: %

Subpartition Pattern Search

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

Schema-Wide Analysis

-- Analyze entire schema:
Enter value for owner: HR
Enter value for table: %
Enter value for partition: %
Enter value for subpartition: %

Statistics Maintenance Strategies

Automated Statistics

Incremental Statistics

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

Scheduled Maintenance

-- Schedule statistics gathering:
-- Daily for active partitions
-- Weekly for historical partitions
-- After major data loads
-- During maintenance windows

Manual Maintenance

Targeted Gathering

-- Manual statistics procedures:
-- Gather for specific subpartitions
-- Use appropriate sample sizes
-- Include histogram collection
-- Validate gathering results

Performance Impact

-- Monitor gathering impact:
-- Resource utilization during gathering
-- Time requirements by subpartition size
-- Impact on concurrent operations
-- Plan optimal gathering schedules

Integration with Monitoring

Performance Monitoring

-- Integrate with performance tools:
-- Monitor query performance by subpartition
-- Track execution plan stability
-- Assess partition pruning effectiveness
-- Report optimization opportunities

Capacity Monitoring

-- Monitor capacity trends:
-- Track subpartition growth rates
-- Monitor storage utilization
-- Plan partition maintenance
-- Support capacity planning

Alert Management

-- Automated alerting:
-- Alert on stale statistics
-- Monitor statistics gathering failures
-- Report significant growth changes
-- Track performance degradation

Performance Optimization

Query Optimization

-- Optimize queries for subpartitions:
-- Use partition-wise joins
-- Implement partition pruning
-- Consider parallel processing
-- Optimize predicate selection

Partition Strategy

-- Optimize partitioning strategy:
-- Evaluate partition key effectiveness
-- Consider sub-partitioning alternatives
-- Plan partition boundary adjustments
-- Assess maintenance requirements

Index Strategy

-- Index optimization:
-- Local vs. global index trade-offs
-- Partition-wise index maintenance
-- Consider prefixed vs. non-prefixed
-- Plan index rebuild strategies

Best Practices

Statistics Management

-- Follow statistics best practices:
-- Use appropriate sample sizes
-- Maintain global statistics
-- Schedule regular gathering
-- Monitor statistics quality

Partition Design

-- Design considerations:
-- Choose effective partition keys
-- Balance partition sizes
-- Plan for growth patterns
-- Consider access patterns

Maintenance Planning

-- Plan maintenance activities:
-- Coordinate with application teams
-- Schedule during low-usage periods
-- Monitor maintenance impact
-- Validate results

Related Scripts

• dstattab.sql - Table statistics analysis
• dstatindsub.sql - Sub-partitioned index statistics
• dtabpart.sql - Partitioned table analysis
• dtable.sql - General table analysis

Summary

This script is essential for:

1. Data Distribution Analysis - Understanding data balance across subpartitions
2. Statistics Health Monitoring - Tracking statistics quality and freshness
3. Performance Optimization - Supporting query and partition optimization
4. Capacity Planning - Monitoring growth and storage requirements
5. Maintenance Planning - Supporting automated and manual maintenance activities