Comprehensive Table Statistics Report (dstattab.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 Oracle database administration functionality via the dstattab.sql script.

The Script

rem  dstattab.sql
rem
ttitle 'Table Statistics'
rem
set linesize 500
rem
col owner format a8 heading 'OWNER'
col table_name format a26 heading 'TABLE NAME'
col global_stats format a3 heading 'GBL'
col num_rows format 99,999,999,990 heading 'ROWS'
col chain_cnt format 99,999,990 heading 'CHAIN|COUNT'
col avg_row_len format 9,990 heading 'AVG|ROW|SIZE'
col blocks format 9,999,999,990 heading 'USED|BLOCKS'
col empty_blocks format 999,990 heading 'EMPTY|BLOCKS'
col avg_space format 9990 heading 'AVG|FREE|SPACE'
col LAST_ANALYZED format a17 heading 'LAST|ANALYZED'
col SAMPLE_SIZE format 99,999,999 heading 'SAMPLE|SIZE'
col SAMPLE_PCT format 999 heading 'SAMP|PCT'
rem
break on owner skip 1
compute sum of num_rows blocks empty_blocks on owner
rem
select owner, table_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,
       trunc(sample_size / decode(num_rows, 0, 1, num_rows) * 100) sample_pct
  from sys.dba_tables
 where owner like nvl(upper('&owner'), '%')
   and table_name like nvl(upper('&table'), '%')
 order by owner, table_name;

What This Script Does

This script provides comprehensive table statistics information that’s critical for Oracle’s Cost-Based Optimizer (CBO) and performance analysis. It displays row counts, storage metrics, chaining information, and statistics collection details including sample sizes and last analysis dates. This information is essential for understanding query performance, planning statistics collection, and troubleshooting optimizer issues.

Key Features

• Complete Statistics Overview: Shows all key table statistics in one report
• Storage Analysis: Displays blocks, space utilization, and chaining metrics
• Statistics Quality Assessment: Shows sample sizes and collection dates
• Global Statistics Indicator: Identifies partitioned table statistics status
• Wide Format Display: Uses 500-character width for comprehensive information
• Flexible Filtering: Filter by owner and table name patterns

Usage

@dstattab.sql

Input Parameters:

• Owner: Schema owner name or pattern (press Enter for all)
• Table: Table name or pattern (press Enter for all)

Required Privileges

SELECT on SYS.DBA_TABLES

Sample Output

                                                                    Table Statistics

OWNER    TABLE NAME                 GBL        ROWS     CHAIN AVG USED      EMPTY  AVG LAST             SAMPLE   SAMP
                                               COUNT    ROW BLOCKS    BLOCKS FREE ANALYZED          SIZE   PCT
                                                        SIZE              SPACE
-------- -------------------------- --- ----------- -------- ---- ---------- ------ ---- ----------------- -------- ----
HR       COUNTRIES                  YES          25        0   21         10      1 2048 11/15/24 14:30:25       25  100
         DEPARTMENTS                YES          27        0   22         15      1 1536 11/15/24 14:30:25       27  100
         EMPLOYEES                  YES         107       15   45        180     12 3584 11/15/24 14:30:25      107  100
         JOB_HISTORY                YES          10        0   18          8      1  896 11/15/24 14:30:25       10  100
         JOBS                       YES          19        0   19         12      1 1280 11/15/24 14:30:25       19  100
         LOCATIONS                  YES          23        0   23         18      2 1792 11/15/24 14:30:25       23  100
                                        ----------- -------- ---------- ------
sum                                              211       15        243     17

SALES    CUSTOMERS                  YES      50,000    1,245  125     12,500    156 4096 11/10/24 08:15:45   10,000   20
         ORDERS                     YES     150,000    2,890  85      25,600    890 3840 11/10/24 08:16:12   15,000   10
         ORDER_ITEMS                YES     450,000    8,750  45      35,200  1,200 2048 11/10/24 08:16:45   45,000   10
         PRODUCTS                   YES       5,000       89   95       1,250     45 3200 11/15/24 10:20:30    5,000  100
                                        ----------- -------- ---------- ------
sum                                             655,000   12,974      74,550  2,291

Key Output Columns

Table Identification

• OWNER: Schema that owns the table
• TABLE NAME: Name of the table
• GBL: Global statistics status (YES/NO) - important for partitioned tables

Row and Size Metrics

• ROWS: Number of rows in the table (NUM_ROWS statistic)
• CHAIN COUNT: Number of chained rows
• AVG ROW SIZE: Average row length in bytes

Storage Metrics

• USED BLOCKS: Number of blocks allocated and used by the table
• EMPTY BLOCKS: Number of allocated but empty blocks
• AVG FREE SPACE: Average free space per block in bytes

Statistics Collection Info

• LAST ANALYZED: Date and time when statistics were last collected
• SAMPLE SIZE: Number of rows sampled during statistics collection
• SAMP PCT: Percentage of table sampled (calculated)

Understanding Table Statistics

Statistics Quality Assessment

Sample Size Evaluation

-- Sample size guidelines:
-- 100%: All rows analyzed (small tables)
-- 10-20%: Good sample for large tables
-- 5-10%: Acceptable for very large tables
-- <5%: May be insufficient for accurate statistics

Statistics Freshness

-- Freshness evaluation:
-- Last analyzed within 1 week: Fresh
-- Last analyzed 1-4 weeks: Acceptable
-- Last analyzed 1-3 months: Stale
-- Last analyzed >3 months: Very stale
-- NULL last_analyzed: No statistics

Storage Analysis

Space Utilization

-- Storage efficiency indicators:
-- High empty blocks: Potential space waste
-- High average free space: Poor space utilization
-- Low blocks vs. high rows: Efficient packing
-- High chain count: Row chaining issues

Performance Implications

-- Storage impact on performance:
-- Chained rows: Increased I/O for row access
-- Empty blocks: Wasted storage and slower scans
-- Poor space utilization: Inefficient I/O
-- Accurate block counts: Better optimizer decisions

Common Use Cases

1. Optimizer Performance Tuning

   • Verify statistics accuracy for performance issues
   • Identify tables needing statistics refresh
   • Assess statistics quality for cost calculations
   • Plan optimal statistics collection strategies

2. Statistics Management

   • Monitor statistics collection schedules
   • Identify stale or missing statistics
   • Plan statistics gathering operations
   • Validate statistics collection results

3. Storage Optimization

   • Identify space waste from empty blocks
   • Find tables with excessive row chaining
   • Plan table reorganization activities
   • Optimize space utilization

4. Capacity Planning

   • Analyze table growth patterns
   • Estimate storage requirements
   • Plan for statistics collection overhead
   • Assess I/O impact of table scans

Statistics Analysis Techniques

Stale Statistics Identification

Find Stale Statistics

-- Tables with stale statistics (>30 days):
SELECT owner, table_name, num_rows,
       last_analyzed,
       ROUND(SYSDATE - last_analyzed) days_old
FROM dba_tables
WHERE last_analyzed < SYSDATE - 30
  AND owner NOT IN ('SYS','SYSTEM')
ORDER BY last_analyzed NULLS LAST;

Statistics Quality Assessment

-- Assess statistics quality:
SELECT owner, table_name,
       CASE
         WHEN sample_size IS NULL THEN 'NO STATS'
         WHEN sample_size = num_rows THEN 'FULL SCAN'
         WHEN sample_size / num_rows >= 0.20 THEN 'GOOD SAMPLE'
         WHEN sample_size / num_rows >= 0.10 THEN 'FAIR SAMPLE'
         ELSE 'POOR SAMPLE'
       END sample_quality,
       ROUND(sample_size / num_rows * 100, 1) sample_pct
FROM dba_tables
WHERE num_rows > 0
ORDER BY sample_pct;

Growth Analysis

Table Growth Tracking

-- Monitor table growth:
-- Run dstattab regularly
-- Compare row counts over time
-- Identify rapidly growing tables
-- Plan statistics refresh accordingly

Statistics Drift Detection

-- Detect significant data changes:
-- Compare current row counts with statistics
-- Look for >20% variance
-- Identify tables needing immediate re-analysis
-- Plan incremental statistics updates

Performance Optimization Applications

Query Performance Analysis

Cardinality Estimation Issues

-- When queries perform poorly:
-- Check if table statistics are current
-- Verify sample sizes are adequate
-- Look for major data distribution changes
-- Consider histogram requirements

Plan Stability

-- For execution plan changes:
-- Compare statistics before/after plan changes
-- Identify which statistics updates caused changes
-- Assess if changes are beneficial
-- Consider statistics locking if needed

Statistics Collection Planning

Collection Strategy

-- Plan statistics collection:
-- Small tables (<1M rows): 100% sample
-- Medium tables (1M-100M): 10-20% sample
-- Large tables (>100M): 5-10% sample
-- Partitioned tables: Consider global vs partition stats

Timing and Frequency

-- Collection scheduling:
-- Daily: Fast-changing transactional tables
-- Weekly: Moderate-growth tables
-- Monthly: Slowly-changing reference tables
-- After major data loads: Immediate re-analysis

Storage Optimization

Row Chaining Analysis

Chaining Detection

-- High chain counts indicate:
-- Rows too large for blocks (increase block size)
-- Updates causing row expansion
-- Poor PCTFREE settings
-- Need for table reorganization

Chaining Resolution

-- Address chaining issues:
-- Increase PCTFREE for growing rows
-- Consider larger block sizes
-- Reorganize tables with high chaining
-- Optimize application update patterns

Space Utilization

Empty Block Analysis

-- High empty blocks suggest:
-- Data deletion without cleanup
-- Poor space management
-- Need for table reorganization
-- Potential for space reclamation

Space Optimization

-- Improve space utilization:
-- Reorganize tables with many empty blocks
-- Adjust PCTUSED settings
-- Consider table compression
-- Implement regular maintenance

Advanced Statistics Analysis

Partitioned Table Statistics

Global vs. Partition Statistics

-- For partitioned tables:
-- GBL = YES: Global statistics available
-- GBL = NO: Only partition-level statistics
-- Both types needed for optimal performance
-- Plan collection for both levels

Partition Statistics Strategy

-- Partition statistics planning:
-- Collect partition stats after partition changes
-- Refresh global stats after significant changes
-- Consider incremental statistics
-- Monitor partition skew

Automated Statistics Collection

Monitoring Automatic Collection

-- Check automatic statistics:
-- Review DBA_OPTSTAT_OPERATIONS
-- Monitor collection success/failure
-- Identify tables excluded from auto collection
-- Adjust automation parameters

Custom Collection Requirements

-- Tables needing manual collection:
-- External tables
-- Very large tables with special requirements
-- Tables with unusual data distributions
-- Objects with locked statistics

Integration with Monitoring

Regular Health Checks

Statistics Health Dashboard

-- Create statistics dashboard:
-- Tables with no statistics
-- Tables with stale statistics (>30 days)
-- Tables with poor sample quality
-- Tables with collection failures

Alerting Thresholds

-- Set alerts for:
-- Statistics older than 30 days
-- Sample sizes less than 5%
-- Tables with no statistics
-- Collection failures

Performance Correlation

Statistics and Performance

-- Correlate statistics with performance:
-- Query response time changes after stats collection
-- Plan changes following statistics updates
-- Cardinality estimation accuracy
-- Index usage pattern changes

Best Practices

Statistics Collection

Collection Guidelines

-- Follow best practices:
-- Collect during maintenance windows
-- Use appropriate sample sizes
-- Include histogram collection for skewed data
-- Monitor collection performance impact

Quality Assurance

-- Ensure statistics quality:
-- Verify collection completion
-- Check sample sizes meet requirements
-- Validate statistics make sense
-- Monitor for collection errors

Maintenance Schedule

Regular Monitoring

-- Establish monitoring routine:
-- Weekly: Check for stale statistics
-- Monthly: Review collection strategy
-- Quarterly: Assess statistics quality
-- After major changes: Immediate review

Related Scripts

• dstatbig.sql - Large table statistics analysis
• dstatind.sql - Index statistics analysis
• dtable.sql - Table storage analysis
• gather_stats.gen - Statistics collection generator

Summary

This script is essential for:

1. Performance Tuning - Identifying statistics issues affecting query performance
2. Statistics Management - Planning and monitoring statistics collection
3. Storage Optimization - Finding space waste and chaining issues
4. Capacity Planning - Understanding table growth and storage patterns
5. Maintenance Planning - Scheduling appropriate statistics collection