Large Table Statistics

Caution

This script queries DBA views. Ensure you have appropriate privileges before running.

Script: dstatbig.sql

rem  dstatbig.sql
rem
ttitle 'Large Table Statistics'
rem
col owner format a8 heading 'OWNER'
col table_name format a25 heading 'TABLE NAME'
col num_rows format 999999990 heading 'ROWS'
col chain_cnt format 9990 heading 'CHAIN|COUNT'
col avg_row_len format 9990 heading 'AVG|ROW|SIZE'
col blocks format 9999990 heading 'USED|BLOCKS'
col empty_blocks format 999990 heading 'EMPTY|BLOCKS'
col avg_space format 9990 heading 'AVG|FREE|SPACE'
rem
break on report on owner skip 1
compute sum of num_rows blocks empty_blocks on report 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 >= &rows
      or blocks >= &blocks
      or empty_blocks >= 10000 )
 order by owner, table_name;

Purpose

This script identifies and displays statistics for large tables in the database based on configurable thresholds for row count, used blocks, or empty blocks. It helps DBAs focus on tables that may require special attention due to their size.

Key Features

• Flexible Filtering: Filter by owner, table name, and size thresholds
• Comprehensive Stats: Shows row counts, chaining, block usage
• Summary Totals: Computes sums by owner and report total
• Large Table Focus: Automatically filters for significant tables

Parameters

The script prompts for:

1. owner: Schema owner (supports wildcards with %)
2. table: Table name pattern (supports wildcards with %)
3. rows: Minimum row count threshold
4. blocks: Minimum used blocks threshold

Output Columns

• OWNER: Schema that owns the table
• TABLE NAME: Name of the table
• ROWS: Number of rows (from statistics)
• CHAIN COUNT: Number of chained/migrated 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

Usage Examples

Find All Large Tables

SQL> @dstatbig.sql
Enter value for owner: %
Enter value for table: %
Enter value for rows: 1000000
Enter value for blocks: 10000

Specific Schema Analysis

SQL> @dstatbig.sql
Enter value for owner: SALES
Enter value for table: %
Enter value for rows: 500000
Enter value for blocks: 5000

Pattern Matching

SQL> @dstatbig.sql
Enter value for owner: %
Enter value for table: %HISTORY%
Enter value for rows: 100000
Enter value for blocks: 1000

Large Table Criteria

Tables are included if they meet ANY of these conditions:

• Row count >= specified threshold
• Used blocks >= specified threshold
• Empty blocks >= 10000 (hardcoded)

Performance Indicators

Chain Count

• High chain_cnt indicates row migration/chaining
• May require table reorganization
• Can impact query performance

Empty Blocks

• Large number suggests space wastage
• Consider shrinking or reorganizing
• May indicate deleted data

Average Row Size

• Helps estimate storage requirements
• Useful for capacity planning
• Indicates data characteristics

Best Practices

1. Regular Monitoring: Run periodically to track growth
2. Statistics Currency: Ensure statistics are up-to-date
3. Threshold Tuning: Adjust thresholds based on database size
4. Action Planning: Use results to plan maintenance

Common Actions

Based on findings:

• High Chaining: Consider increasing PCTFREE or reorganizing
• Many Empty Blocks: Use ALTER TABLE SHRINK SPACE
• Rapid Growth: Implement partitioning or archiving
• No Statistics: Gather statistics for accurate results

Related Scripts

• dtable.sql: General table information
• dtabhist.sql: Table statistics history
• objcnt.sql: Object counts by type
• maxshrink.sql: Calculate shrinkable space