Table Storage Parameter Summary (dtabstor.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 table storage parameters by summarizing PCT_FREE and PCT_USED settings across database tables, grouped by schema owner. It provides insight into storage configuration patterns, helping identify inconsistent storage parameters and opportunities for standardization. The script is essential for storage optimization, configuration management, and ensuring consistent storage policies across database schemas.

The Script

rem  dtabstor.sql
rem
ttitle 'Table Storage Summary'
rem
col owner format a12 heading 'OWNER'
col pct_free format 999 heading 'PCT|FREE'
col pct_used format 999 heading 'PCT|USED'
col table_count format 99999 heading 'TABLE|COUNT'
rem
break on owner
rem
select owner,
       pct_free,
       pct_used,
       count(*) table_count
  from sys.dba_tables
 where owner like upper('&owner')
 group by owner, pct_free, pct_used
/

Key Features

• Storage Parameter Analysis: Examines PCT_FREE and PCT_USED settings
• Schema-Level Grouping: Groups results by owner for organized analysis
• Configuration Counting: Shows number of tables with each parameter combination
• Pattern Recognition: Identifies storage configuration patterns and inconsistencies
• Flexible Filtering: Filter by specific owner or analyze all schemas
• Standardization Support: Helps identify configuration standardization opportunities

Usage

@dtabstor.sql

Input Parameters:

• owner: Schema owner name or pattern (use % for all schemas)

Required Privileges

SELECT on SYS.DBA_TABLES

Sample Output

                                            Table Storage Summary

OWNER        PCT PCT TABLE
            FREE USED COUNT
------------ ---- ---- -----
HR             10   40    15
               20   80     3

SALES          10   40    25
               15   75     5
               20   80     2

FINANCE        10   40    12
               10   60     8

WAREHOUSE      5    95    50
               10   40    15

Key Output Columns

• OWNER: Schema owner name
• PCT FREE: Percentage of free space reserved in each block for updates
• PCT USED: Threshold for block to be considered for new row inserts
• TABLE COUNT: Number of tables with this specific parameter combination

Understanding Storage Parameters

PCT_FREE Parameter

Purpose and Function

-- PCT_FREE determines:
-- Percentage of block space reserved for row updates
-- Space available for existing row expansion
-- When blocks become unavailable for new inserts
-- Update performance and row chaining prevention

Common Values and Usage

-- Typical PCT_FREE settings:
-- 10%: Standard for most tables (default)
-- 20-30%: Tables with frequent updates
-- 5%: Read-only or append-only tables
-- 0%: Data warehouse fact tables

PCT_USED Parameter

Purpose and Function

-- PCT_USED determines:
-- Threshold for blocks to be available for inserts
-- When blocks return to freelist
-- Space utilization efficiency
-- Insert performance characteristics

Relationship with PCT_FREE

-- Parameter relationship:
-- PCT_FREE + PCT_USED must be < 100
-- Gap between them creates hysteresis
-- Prevents frequent freelist additions/removals
-- Affects space utilization and performance

Common Use Cases

1. Storage Configuration Analysis

   • Review storage parameter consistency
   • Identify non-standard configurations
   • Plan storage standardization
   • Assess configuration impact

2. Performance Optimization

   • Optimize storage for workload patterns
   • Reduce row chaining and migration
   • Improve insert and update performance
   • Plan block utilization efficiency

3. Space Management

   • Analyze space utilization patterns
   • Plan storage efficiency improvements
   • Assess wasted space due to poor parameters
   • Support capacity planning

4. Configuration Standardization

   • Establish storage parameter standards
   • Identify tables needing parameter updates
   • Plan configuration migration
   • Ensure consistent policies

Advanced Analysis

Configuration Pattern Analysis

Standard vs. Custom Settings

-- Identify configuration patterns:
-- Default settings (10% FREE, 40% USED)
-- Custom optimized settings
-- Unusual or problematic combinations
-- Schema-specific patterns

Workload Alignment

-- Assess parameter alignment with workload:
-- OLTP tables: Higher PCT_FREE for updates
-- Data warehouse: Lower PCT_FREE for space efficiency
-- Archive tables: Minimal PCT_FREE
-- Staging tables: Optimized for bulk operations

Performance Impact Assessment

Space Efficiency

-- Space utilization factors:
-- Higher PCT_FREE = less space efficient
-- Lower PCT_USED = more frequent freelist management
-- Gap size affects performance stability
-- Schema-wide impact assessment

Update Performance

-- Update performance considerations:
-- Insufficient PCT_FREE causes row chaining
-- Excessive PCT_FREE wastes space
-- Workload-specific optimization needs
-- Performance vs. space trade-offs

Configuration Optimization

Workload-Based Optimization

OLTP Workloads

-- OLTP optimization guidelines:
-- PCT_FREE: 15-25% for high update tables
-- PCT_FREE: 10% for moderate update tables
-- PCT_USED: 40% for balanced performance
-- Consider row growth patterns

Data Warehouse Workloads

-- Data warehouse optimization:
-- PCT_FREE: 0-5% for fact tables
-- PCT_FREE: 10% for dimension tables
-- PCT_USED: 80-95% for space efficiency
-- Optimize for bulk loading

Mixed Workloads

-- Mixed workload considerations:
-- Analyze table-specific access patterns
-- Balance space efficiency and performance
-- Consider partitioning for different parameters
-- Monitor and adjust based on metrics

Standardization Strategies

Schema-Level Standards

-- Establish schema standards:
-- Application-specific parameter sets
-- Default configurations by table type
-- Exception handling procedures
-- Configuration documentation

Implementation Planning

-- Standardization implementation:
-- Identify tables needing changes
-- Plan maintenance windows
-- Test parameter changes
-- Monitor performance impact

Filtering Examples

Specific Schema Analysis

-- Analyze specific schema:
Enter value for owner: HR

All Schemas Review

-- Review all schemas:
Enter value for owner: %

Pattern-Based Analysis

-- Analyze schema patterns:
Enter value for owner: %APP%

Configuration Issues and Solutions

Common Problems

Row Chaining

-- Row chaining indicators:
-- PCT_FREE too low for update patterns
-- Frequent row migration
-- Poor update performance
-- Solution: Increase PCT_FREE

Space Waste

-- Space waste indicators:
-- PCT_FREE too high for workload
-- Excessive unused space in blocks
-- Poor space utilization
-- Solution: Decrease PCT_FREE

Freelist Contention

-- Freelist issues:
-- PCT_USED set too low
-- Frequent freelist management overhead
-- Insert performance problems
-- Solution: Optimize PCT_USED setting

Resolution Strategies

Parameter Adjustment

-- Adjust storage parameters:
ALTER TABLE table_name
STORAGE (PCTFREE 15 PCTUSED 75);

-- Consider table reorganization after changes
-- Monitor performance impact
-- Validate space utilization improvements

Monitoring and Validation

-- Monitor parameter effectiveness:
-- Track row chaining statistics
-- Monitor space utilization
-- Assess performance metrics
-- Adjust parameters as needed

Integration with Performance Analysis

Row Chaining Analysis

-- Correlate with chaining statistics:
SELECT
  owner, table_name,
  pct_free, pct_used,
  chain_cnt, avg_row_len
FROM dba_tables
WHERE chain_cnt > 0
ORDER BY chain_cnt DESC;

Space Utilization

-- Analyze space efficiency:
SELECT
  owner, pct_free, pct_used,
  AVG(blocks) as avg_blocks,
  AVG(empty_blocks) as avg_empty_blocks
FROM dba_tables
GROUP BY owner, pct_free, pct_used;

Best Practices

Configuration Standards

Establish Guidelines

-- Storage parameter guidelines:
-- Document workload-specific settings
-- Define default configurations
-- Create exception approval process
-- Regular configuration reviews

Application-Specific Settings

-- Tailor settings to application needs:
-- OLTP: Higher PCT_FREE for updates
-- Reporting: Lower PCT_FREE for space efficiency
-- Archive: Minimal PCT_FREE
-- Staging: Bulk operation optimization

Monitoring and Maintenance

Regular Reviews

-- Monitoring schedule:
-- Monthly configuration reviews
-- Quarterly performance assessments
-- Annual standardization audits
-- Change impact evaluations

Performance Correlation

-- Track performance metrics:
-- Row chaining statistics
-- Space utilization efficiency
-- Insert/update performance
-- User response times

Related Scripts

• dtable.sql - Comprehensive table analysis
• dstatbig.sql - Large table statistics
• dsegbig.sql - Large segment analysis
• dtabcol.sql - Table column analysis

Implementation Recommendations

Assessment Phase

-- Initial assessment steps:
-- Run script for all schemas
-- Identify configuration patterns
-- Document current state
-- Assess performance correlations

Optimization Phase

-- Optimization process:
-- Develop workload-specific standards
-- Plan parameter changes
-- Test in development environment
-- Implement in production

Monitoring Phase

-- Ongoing monitoring:
-- Regular configuration audits
-- Performance impact tracking
-- Space utilization monitoring
-- Continuous improvement

Summary

This script is essential for:

1. Storage Configuration Analysis - Understanding current storage parameter patterns
2. Performance Optimization - Optimizing storage for specific workload requirements
3. Standardization Planning - Establishing consistent storage policies
4. Space Management - Improving storage efficiency and utilization
5. Configuration Management - Maintaining optimal storage parameters across schemas