Medium Table Statistics (dstatmed.sql)

What This Script Does

This script analyzes table statistics for medium-sized tables within a specified block range, providing detailed storage metrics including row counts, block usage, row chaining information, and space utilization. It’s particularly useful for focusing on tables of moderate size that may benefit from optimization without overwhelming the analysis with very large or very small tables.

The Script

rem  dstatmed.sql
rem
ttitle 'Medium Table Statistics'
rem
col owner format a8 heading 'OWNER'
col table_name format a21 heading 'TABLE NAME'
col num_rows format 9999999990 heading 'ROWS'
col chain_cnt format 9990 heading 'CHAIN|COUNT'
col avg_row_len format 9990 heading 'AVG|ROW|SIZE'
col blocks format 999999990 heading 'USED|BLOCKS'
col empty_blocks format 9999990 heading 'EMPTY|BLOCKS'
col avg_space format 9990 heading 'AVG|FREE|SPACE'
rem
break on owner skip 1
compute sum of num_rows blocks empty_blocks on owner
rem
select owner, table_name, num_rows, chain_cnt, avg_row_len,
       blocks, empty_blocks, avg_space
  from sys.dba_tables
 where owner like upper('&owner')
   and table_name like upper('&table')
   and blocks >= &low_blocks
   and blocks <= &high_blocks
 order by owner, table_name;

Key Features

Block Range Filtering: Focuses on tables within specified block size ranges
Comprehensive Storage Metrics: Shows blocks used, empty blocks, and space efficiency
Row Chaining Analysis: Identifies tables with row chaining issues
Schema and Table Filtering: Supports pattern matching for targeted analysis
Summary Statistics: Provides totals by owner for capacity planning
Space Efficiency Assessment: Shows average free space and row size metrics

Usage

@dstatmed.sql

Input Parameters:

owner: Schema owner pattern (use % for wildcards)
table: Table name pattern (use % for wildcards)
low_blocks: Minimum block count threshold
high_blocks: Maximum block count threshold

Required Privileges

SELECT on SYS.DBA_TABLES

Sample Output

                                            Medium Table Statistics

OWNER    TABLE NAME               ROWS CHAIN AVG    USED    EMPTY AVG
                                       COUNT ROW     BLOCKS  BLOCKS FREE
                                             SIZE                   SPACE
-------- --------------------- --------- ----- ---- --------- ------- ----
HR       DEPARTMENTS                  27     0   20        5       0  312
         EMPLOYEES                   107     0   65        8       0  890
         JOB_HISTORY                  10     0   52        5       0 1234
         JOBS                         19     0   32        5       0  567
         LOCATIONS                    23     0   71        5       0  423
                               --------- --------- ---------
sum                                  186        28       0

SALES    CUSTOMERS                 23567    45  125      234      12  456
         ORDERS                    45678    23   89      456      23  678
         ORDER_ITEMS               89123    67  156      789      34  234
                               --------- --------- ---------
sum                               158368      1479      69

Key Output Columns

OWNER: Schema owner of the table
TABLE NAME: Name of the table
ROWS: Number of rows in the table
CHAIN COUNT: Number of chained rows (performance indicator)
AVG ROW SIZE: Average row length in bytes
USED BLOCKS: Number of blocks containing data
EMPTY BLOCKS: Number of allocated but empty blocks
AVG FREE SPACE: Average free space per block in bytes

Understanding Table Statistics

Storage Metrics Analysis

Block Usage Efficiency

-- Block efficiency indicators:
-- Low empty_blocks: Efficient space utilization
-- High empty_blocks: Potential space waste
-- Used blocks vs. total allocated: Space efficiency
-- Block range filtering: Focus on medium-sized tables

Row Chaining Assessment

-- Chain count significance:
-- 0 chain_cnt: Optimal row storage
-- Low chain_cnt: Acceptable performance
-- High chain_cnt: Performance issues likely
-- Chain percentage: chain_cnt / num_rows * 100

Space Utilization Patterns

Average Row Size Analysis

-- Row size implications:
-- Small avg_row_len: More rows per block
-- Large avg_row_len: Fewer rows per block
-- Correlation with block size efficiency
-- Impact on buffer cache utilization

Free Space Management

-- Free space indicators:
-- High avg_space: Under-utilized blocks
-- Low avg_space: Efficient packing or potential issues
-- Correlation with PCTFREE settings
-- Update activity implications

Common Use Cases

Capacity Planning
- Analyze medium-sized table growth patterns
- Plan storage requirements for specific size ranges
- Identify tables approaching size thresholds
- Support infrastructure planning
Performance Optimization
- Identify tables with row chaining issues
- Assess space utilization efficiency
- Find candidates for reorganization
- Support SQL performance tuning
Storage Management
- Monitor space utilization by table size category
- Identify inefficient space usage
- Plan table maintenance activities
- Support storage optimization
Schema Analysis
- Compare storage patterns across schemas
- Identify schema-specific optimization opportunities
- Support application performance analysis
- Plan schema-level improvements

Advanced Analysis

Row Chaining Investigation

Chaining Root Causes

-- Common causes of row chaining:
-- PCTFREE setting too low for update patterns
-- Row size expansion after initial insert
-- Large column updates (LOBs, VARCHAR2)
-- Poor block size relative to row size

Chaining Impact Assessment

-- Performance impact of chaining:
-- Additional I/O operations required
-- Increased response time for DML
-- Higher CPU usage for row assembly
-- Buffer cache efficiency reduction

Space Efficiency Analysis

Block Utilization Patterns

-- Efficiency calculations:
-- Space utilization = (blocks - empty_blocks) / blocks * 100
-- Row density = num_rows / blocks
-- Average space waste = empty_blocks * block_size
-- Optimization potential assessment

Growth Pattern Analysis

-- Growth indicators:
-- Empty blocks suggest recent growth or deletions
-- High average free space indicates expansion potential
-- Row count vs. block ratio shows density trends
-- Historical comparison for growth planning

Optimization Strategies

Row Chaining Resolution

Chaining Remediation

-- Address row chaining through:
-- Table reorganization (MOVE or export/import)
-- PCTFREE adjustment for update patterns
-- Column datatype optimization
-- Large object externalization

Prevention Strategies

-- Prevent future chaining:
-- Appropriate PCTFREE settings
-- Regular table maintenance
-- Monitor update patterns
-- Design considerations for large columns

Space Optimization

Block Efficiency Improvement

-- Improve space utilization:
-- Reorganize tables with high empty blocks
-- Adjust storage parameters
-- Consider compression for appropriate tables
-- Implement regular maintenance procedures

Storage Parameter Tuning

-- Optimize storage settings:
-- PCTFREE based on update frequency
-- PCTUSED for space reclamation
-- Block size optimization
-- Extent sizing for growth patterns

Filtering Examples

Medium-Sized Tables (100-1000 blocks)

-- Focus on medium tables:
Enter value for owner: %
Enter value for table: %
Enter value for low_blocks: 100
Enter value for high_blocks: 1000

Specific Schema Analysis

-- Analyze specific schema's medium tables:
Enter value for owner: SALES
Enter value for table: %
Enter value for low_blocks: 50
Enter value for high_blocks: 500

Large Medium Tables (500-2000 blocks)

-- Focus on larger medium tables:
Enter value for owner: %
Enter value for table: %
Enter value for low_blocks: 500
Enter value for high_blocks: 2000

Table Pattern Analysis

-- Analyze specific table patterns:
Enter value for owner: %
Enter value for table: %HISTORY%
Enter value for low_blocks: 10
Enter value for high_blocks: 100

Integration with Maintenance Planning

Reorganization Planning

Reorganization Candidates

-- Identify tables needing reorganization:
-- High chain count (>5% of rows)
-- Excessive empty blocks (>20% of total)
-- Poor space utilization
-- Performance complaints correlated with tables

Maintenance Scheduling

-- Plan maintenance activities:
-- Group tables by maintenance requirements
-- Schedule during low-usage periods
-- Consider dependencies and constraints
-- Plan validation and rollback procedures

Performance Monitoring

Baseline Establishment

-- Establish performance baselines:
-- Document current statistics
-- Track changes over time
-- Correlate with application performance
-- Plan proactive maintenance

Trend Analysis

-- Monitor statistical trends:
-- Row count growth patterns
-- Block usage evolution
-- Chaining development
-- Space utilization changes

Best Practices

Regular Monitoring

Assessment Schedule

-- Regular assessment:
-- Weekly statistics gathering
-- Monthly medium table analysis
-- Quarterly optimization review
-- Annual capacity planning

Threshold Management

-- Set monitoring thresholds:
-- Chain count warnings (>1% of rows)
-- Empty block alerts (>15% of blocks)
-- Growth rate monitoring
-- Performance correlation tracking

Optimization Methodology

Systematic Approach

-- Optimization process:
-- Identify problem tables using statistics
-- Analyze root causes of inefficiency
-- Plan and test optimization strategies
-- Implement changes during maintenance windows
-- Validate improvements and monitor results

dstatbig.sql - Large table statistics
dstatzer.sql - Empty table analysis
dtable.sql - Comprehensive table analysis
dsegbig.sql - Large segment analysis

Troubleshooting Applications

Performance Issues

-- Investigate chaining impact:
-- Correlate high chain counts with slow queries
-- Monitor I/O patterns for affected tables
-- Analyze execution plans for full table scans
-- Consider reorganization for heavily chained tables

-- Address space problems:
-- High empty blocks causing inefficient scans
-- Poor space utilization affecting cache efficiency
-- Growth patterns indicating storage pressure
-- Fragmentation impact on performance

Capacity Issues

Storage Planning

-- Support capacity planning:
-- Project growth based on current trends
-- Identify tables approaching size limits
-- Plan for infrastructure scaling
-- Optimize storage allocation

Summary

This script is essential for:

Medium Table Analysis - Focused analysis of moderately-sized tables within specified block ranges
Space Optimization - Identifying and resolving space utilization inefficiencies in medium tables
Performance Tuning - Detecting row chaining and storage issues affecting performance
Capacity Planning - Understanding storage requirements and growth patterns for medium-sized tables
Maintenance Planning - Supporting systematic table maintenance and optimization strategies