Table Statistics Report (dstattr.sql)

What This Script Does

This script provides a comprehensive report of table statistics for all tables containing data, showing detailed storage metrics including row counts, block usage, row chaining information, and space utilization patterns. It automatically excludes empty tables to focus on active data and provides summary statistics by schema owner, making it ideal for database capacity analysis and performance optimization planning.

The Script

rem  dstattab.sql
rem
ttitle 'Table Statistics'
rem
col owner format a8 heading 'OWNER'
col table_name format a27 heading 'TABLE NAME'
col num_rows format 99999990 heading 'ROWS'
col chain_cnt format 9990 heading 'CHAIN|COUNT'
col avg_row_len format 9990 heading 'AVG|ROW|SIZE'
col blocks format 999990 heading 'USED|BLOCKS'
col empty_blocks format 999990 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
compute count of table_name 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 num_rows > 0
 order by owner, table_name;

Key Features

Non-Empty Table Focus: Analyzes only tables with data (num_rows > 0)
Comprehensive Storage Metrics: Shows complete storage utilization picture
Row Chaining Analysis: Identifies potential performance issues
Schema Summaries: Provides totals and counts by owner
Flexible Filtering: Supports owner and table name pattern matching
Space Efficiency Assessment: Evaluates storage optimization opportunities

Usage

@dstattr.sql

Input Parameters:

owner: Schema owner pattern (use % for wildcards)
table: Table name pattern (use % for wildcards)

Required Privileges

SELECT on SYS.DBA_TABLES

Sample Output

                                              Table Statistics

OWNER    TABLE NAME                    ROWS CHAIN AVG    USED   EMPTY AVG
                                            COUNT ROW     BLOCKS BLOCKS FREE
                                                  SIZE                  SPACE
-------- --------------------------- -------- ----- ---- ------ ------ ----
HR       COUNTRIES                         25     0   48      5      0  234
         DEPARTMENTS                       27     0   32      5      0  567
         EMPLOYEES                        107     0   69      8      0  445
         JOB_HISTORY                       10     0   56      5      0  890
         JOBS                              19     0   32      5      0  456
         LOCATIONS                         23     0   71      5      0  678
         REGIONS                            4     0   29      5      0 1234
                                     -------- ------ ------
sum                                       215     38      0
count                                       7

SALES    CUSTOMERS                      23567    45  125    234     12  456
         ORDERS                         45678    23   89    456     23  678
         ORDER_ITEMS                    89123    67  156    789     34  234
         PRODUCTS                       12345     5   78    123      8  345
                                     -------- ------ ------
sum                                    170713   1602     77
count                                       4

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
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

Row and Storage Metrics

Row Count Analysis

-- Row count significance:
-- High row counts: Large tables requiring special attention
-- Medium row counts: Standard operational tables
-- Low row counts: Reference or lookup tables
-- Growth patterns: Historical trend analysis

Block Utilization Assessment

-- Block usage patterns:
-- Used blocks: Actual data storage requirements
-- Empty blocks: Potential space waste or recent deletions
-- Block efficiency: Used blocks vs. total allocated
-- Storage optimization opportunities

Performance Indicators

Row Chaining Analysis

-- Chain count implications:
-- Zero chaining: Optimal storage configuration
-- Low chaining: Acceptable performance impact
-- High chaining: Performance degradation likely
-- Chaining percentage: Relative impact assessment

Space Efficiency Metrics

-- Space utilization factors:
-- Average row size vs. block size efficiency
-- Free space availability for updates
-- Block packing density
-- Storage parameter effectiveness

Common Use Cases

Database Capacity Analysis
- Assess overall database storage utilization
- Identify largest consumers of space
- Plan storage expansion requirements
- Support capacity planning initiatives
Performance Optimization
- Identify tables with performance issues (chaining)
- Find space utilization inefficiencies
- Plan table reorganization activities
- Support SQL performance tuning
Schema Management
- Compare storage patterns across schemas
- Identify schema-specific optimization opportunities
- Plan schema consolidation or separation
- Support application performance analysis
Maintenance Planning
- Identify tables requiring maintenance
- Plan reorganization and optimization activities
- Schedule statistics gathering
- Support proactive maintenance programs

Advanced Analysis

Storage Efficiency Assessment

Space Utilization Calculations

-- Efficiency metrics:
-- Total space used: blocks * block_size
-- Wasted space: empty_blocks * block_size
-- Space efficiency: (blocks - empty_blocks) / blocks * 100
-- Average row density: num_rows / blocks

Optimization Opportunity Identification

-- Optimization candidates:
-- Tables with high empty_blocks ratios
-- Tables with significant row chaining
-- Tables with poor space utilization
-- Tables with growth pattern anomalies

Row Chaining Impact Analysis

Chaining Performance Impact

-- Performance implications:
-- Additional I/O operations for chained rows
-- Increased CPU usage for row assembly
-- Higher response times for DML operations
-- Buffer cache efficiency reduction

Chaining Root Cause Analysis

-- Common causes of chaining:
-- PCTFREE settings too low for update patterns
-- Row expansion after initial insert
-- Large column updates (CLOBs, VARCHAR2)
-- Block size inappropriate for row size

Schema-Level Analysis

Cross-Schema Comparison

Storage Pattern Analysis

-- Compare schemas:
-- Total storage consumption by schema
-- Average table sizes across schemas
-- Space efficiency patterns
-- Row chaining prevalence by application

Application Performance Correlation

-- Application-specific patterns:
-- OLTP schemas: Smaller tables, minimal chaining
-- Data warehouse schemas: Large tables, high density
-- Archive schemas: Large tables, low activity
-- Reference schemas: Small tables, stable size

Growth Pattern Assessment

Historical Growth Analysis

-- Growth trend indicators:
-- Row count changes over time
-- Block allocation patterns
-- Empty block accumulation
-- Space utilization evolution

Capacity Planning Support

-- Planning considerations:
-- Projected growth rates by schema
-- Storage requirement forecasts
-- Performance impact projections
-- Infrastructure scaling needs

Optimization Strategies

Table Reorganization Planning

Reorganization Candidates

-- Prioritize tables based on:
-- High row chaining (>5% of rows)
-- Poor space utilization (<80% efficiency)
-- Large size with performance issues
-- Critical application tables

Reorganization Methods

-- Reorganization approaches:
-- MOVE command for online reorganization
-- Export/import for complete rebuild
-- Partition maintenance for large tables
-- Storage parameter adjustments

Storage Parameter Optimization

PCTFREE and PCTUSED Tuning

-- Parameter optimization:
-- PCTFREE based on update frequency and row growth
-- PCTUSED for space reclamation efficiency
-- Block size optimization for row size patterns
-- Extent sizing for growth patterns

Filtering Examples

All Schemas Analysis

-- Comprehensive database analysis:
Enter value for owner: %
Enter value for table: %

Specific Schema Focus

-- Single schema analysis:
Enter value for owner: SALES
Enter value for table: %

Table Pattern Analysis

-- Specific table patterns:
Enter value for owner: %
Enter value for table: %HISTORY%

Application-Specific Analysis

-- Application schema analysis:
Enter value for owner: %APP%
Enter value for table: %

Integration with Monitoring

Performance Monitoring

Statistics Correlation

-- Correlate with performance metrics:
-- Query response times vs. row chaining
-- Full table scan frequency vs. table size
-- Buffer cache hit ratios vs. space efficiency
-- I/O patterns vs. storage utilization

Baseline Establishment

-- Performance baselines:
-- Normal storage utilization patterns
-- Expected row count ranges
-- Typical space efficiency levels
-- Standard chaining percentages

Automated Analysis

Trend Monitoring

-- Automated trend analysis:
-- Regular statistics gathering and comparison
-- Growth rate calculation and alerting
-- Space efficiency degradation detection
-- Performance correlation tracking

Alert Configuration

-- Alert thresholds:
-- Row chaining > 5% of total rows
-- Space efficiency < 80%
-- Empty blocks > 20% of total blocks
-- Unexpected row count changes

Best Practices

Regular Assessment

Monitoring Schedule

-- Assessment frequency:
-- Daily monitoring for critical schemas
-- Weekly analysis for all active schemas
-- Monthly comprehensive reviews
-- Quarterly optimization planning

Statistics Maintenance

-- Statistics gathering:
-- Regular ANALYZE TABLE or DBMS_STATS
-- Automated statistics gathering jobs
-- Manual statistics for critical tables
-- Historical statistics preservation

Performance Optimization

Systematic Approach

-- Optimization methodology:
-- Identify highest impact optimization opportunities
-- Plan changes during appropriate maintenance windows
-- Test optimizations in development environments
-- Monitor post-optimization performance

Documentation and Tracking

-- Change management:
-- Document current state before changes
-- Track optimization results
-- Maintain performance improvement history
-- Share best practices across teams

dstatbig.sql - Large table statistics
dstatmed.sql - Medium table statistics
dstatzer.sql - Empty table analysis
dtable.sql - Comprehensive table analysis

Troubleshooting Applications

Performance Issues

-- Investigate performance issues:
-- Correlate slow queries with high chaining tables
-- Analyze space efficiency impact on scans
-- Review growth patterns affecting performance
-- Identify fragmentation-related issues

Capacity Issues

Space Management

-- Address space problems:
-- Identify space waste from empty blocks
-- Plan storage reclamation activities
-- Optimize storage allocation patterns
-- Implement space monitoring procedures

Summary

This script is essential for:

Comprehensive Table Analysis - Complete overview of table statistics and storage utilization
Capacity Planning - Understanding current storage usage and planning future requirements
Performance Optimization - Identifying tables with storage-related performance issues
Schema Management - Comparing and managing storage patterns across database schemas
Maintenance Planning - Supporting systematic table maintenance and optimization programs