Segment Storage Summary with Buffer Pool Analysis (dsegsump.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 dsegsump.sql script.

The Script

rem  dsegsump.sql
rem
ttitle 'Segment Storage Summary'
rem
col tablespace_name format a15 heading 'TABLESPACE'
col owner format a12 heading 'OWNER'
col segment_type format a17 heading 'SEGMENT|TYPE'
col segment_count format 99999 heading 'COUNT'
col buffer_pool format a6 heading 'BUFFER|POOL'
col kbytes format 999,999,999 heading 'SIZE (K)'
col blocks format 9999999 heading 'BLOCKS'
col extents format 999999 heading 'EXTENTS'
rem
break on report on owner skip 1
compute sum of segment_count kbytes blocks extents on report owner
rem
select owner, tablespace_name,
       decode(buffer_pool, 'DEFAULT', null, buffer_pool) buffer_pool,
       segment_type,
       count(*) segment_count,
       sum(blocks) blocks,
       sum(bytes)/1024 kbytes, sum(extents) extents
  from sys.dba_segments
 where owner like nvl(upper('&owner'),'%')
   and segment_type like nvl(upper('&type'),'%')
   and tablespace_name like nvl(upper('&tablespace'),'%')
   and buffer_pool like nvl(upper('&pool'),'%')
 group by owner, tablespace_name, segment_type,
       decode(buffer_pool, 'DEFAULT', null, buffer_pool);

What This Script Does

This script provides a comprehensive analysis of database segment storage, grouping by owner, tablespace, segment type, and buffer pool assignment. It’s essential for understanding storage utilization patterns, planning capacity requirements, and optimizing buffer pool configurations. The script shows counts, sizes, and extent information for all segments matching the specified criteria.

Key Features

• Multi-dimensional Analysis: Groups by owner, tablespace, type, and buffer pool
• Flexible Filtering: Parameterized filters for owner, type, tablespace, and buffer pool
• Storage Metrics: Shows size in KB, blocks, and extent counts
• Buffer Pool Visibility: Displays non-default buffer pool assignments
• Summary Totals: Provides totals by owner and grand totals
• Space Planning Support: Essential data for capacity planning

Usage

@dsegsump.sql

Input Parameters:

• Owner: Schema owner name or pattern (press Enter for all)
• Type: Segment type pattern (press Enter for all)
• Tablespace: Tablespace name or pattern (press Enter for all)
• Pool: Buffer pool name or pattern (press Enter for all)

Required Privileges

SELECT on SYS.DBA_SEGMENTS

Sample Output

                                       Segment Storage Summary

OWNER        TABLESPACE      BUFFER SEGMENT           COUNT   BLOCKS      SIZE (K)  EXTENTS
             POOL            TYPE
------------ --------------- ------ ----------------- ----- -------- ----------- --------
HR           USERS                  TABLE                 7    2,048       16,384       45
             USERS                  INDEX                11    1,536       12,288       33
             USERS                  LOBSEGMENT            3      512        4,096        9
             USERS                  LOBINDEX              3      128        1,024        3
                                                        ===== ======== =========== ========
sum                                                        24    4,224       33,792       90

SALES        SALES_DATA             TABLE                15   12,800      102,400      125
             SALES_DATA             INDEX                23    8,960       71,680       184
             SALES_IDX              INDEX                12    4,096       32,768       48
             SALES_DATA      KEEP   TABLE                 2      256        2,048        8
             SALES_DATA      RECYCLE TABLE               3      768        6,144       12
                                                        ===== ======== =========== ========
sum                                                        55   26,880      215,040      377

                                                        ===== ======== =========== ========
sum                                                        79   31,104      248,832      467

Key Output Columns

• OWNER: Schema that owns the segments
• TABLESPACE: Tablespace containing the segments
• BUFFER POOL: Buffer pool assignment (KEEP, RECYCLE, blank for DEFAULT)
• SEGMENT TYPE: Type of segment (TABLE, INDEX, LOBSEGMENT, etc.)
• COUNT: Number of segments of this type
• BLOCKS: Total blocks allocated to these segments
• SIZE (K): Total size in kilobytes
• EXTENTS: Total number of extents

Understanding Segment Storage

Segment Types

Core Object Types

-- Common segment types:
-- TABLE: Regular table data
-- INDEX: B-tree and other index types
-- CLUSTER: Table clusters
-- LOBSEGMENT: Large object data
-- LOBINDEX: Large object indexes

System and Temporary Types

-- System segment types:
-- ROLLBACK: Rollback segments (older versions)
-- TYPE2 UNDO: Undo segments
-- TEMPORARY: Temporary segments
-- CACHE: Result cache segments

Buffer Pool Assignments

Buffer Pool Types

-- DEFAULT: Standard buffer pool (not shown)
-- KEEP: Objects kept in memory longer
-- RECYCLE: Objects aged out quickly
-- Optimal for different access patterns

Usage Patterns

-- KEEP pool: Small, frequently accessed tables
-- RECYCLE pool: Large scan tables
-- DEFAULT pool: Most general-purpose objects
-- Proper assignment improves cache efficiency

Common Use Cases

1. Capacity Planning

   • Analyze current storage utilization
   • Plan for tablespace growth
   • Estimate future space requirements
   • Optimize storage allocation

2. Performance Tuning

   • Review buffer pool assignments
   • Identify storage hotspots
   • Plan memory allocation
   • Optimize I/O patterns

3. Storage Management

   • Monitor space consumption by owner
   • Track segment growth patterns
   • Plan storage consolidation
   • Optimize extent allocation

4. Environment Assessment

   • Compare storage patterns between environments
   • Validate configuration consistency
   • Support migration planning
   • Document storage architecture

Advanced Analysis

Tablespace Utilization by Owner

-- Detailed tablespace analysis:
SELECT tablespace_name, owner,
       SUM(bytes)/1024/1024 size_mb,
       COUNT(*) segment_count,
       AVG(bytes/blocks) avg_block_size
FROM dba_segments
WHERE tablespace_name NOT IN ('SYSTEM','SYSAUX')
GROUP BY tablespace_name, owner
ORDER BY tablespace_name, size_mb DESC;

Buffer Pool Optimization Analysis

-- Review buffer pool assignments:
SELECT buffer_pool, segment_type,
       COUNT(*) segments,
       SUM(bytes)/1024/1024 total_mb,
       AVG(bytes)/1024 avg_kb_per_segment
FROM dba_segments
WHERE owner NOT IN ('SYS','SYSTEM')
GROUP BY buffer_pool, segment_type
ORDER BY buffer_pool, total_mb DESC;

Large Segment Identification

-- Find largest segments:
SELECT owner, segment_name, segment_type, tablespace_name,
       bytes/1024/1024 size_mb,
       blocks, extents,
       buffer_pool
FROM dba_segments
WHERE bytes > 100*1024*1024  -- > 100MB
ORDER BY bytes DESC;

Storage Optimization Strategies

Extent Management

Extent Analysis

-- High extent counts may indicate:
-- Poor initial extent sizing
-- Frequent growth patterns
-- Need for reorganization
-- Storage parameter tuning opportunities

Optimization Actions

-- For high extent counts:
-- Review storage parameters
-- Consider table reorganization
-- Adjust initial/next extent sizes
-- Implement uniform extent sizes

Buffer Pool Optimization

Assignment Review

-- Evaluate current assignments:
-- Small frequently-used tables → KEEP pool
-- Large scan-only tables → RECYCLE pool
-- General-purpose objects → DEFAULT pool
-- Monitor cache hit ratios by pool

Performance Impact

-- Buffer pool benefits:
-- KEEP: Improved cache hit ratios for hot data
-- RECYCLE: Prevents cache pollution
-- DEFAULT: Balanced caching strategy
-- Proper sizing prevents memory contention

Capacity Planning Applications

Growth Trend Analysis

Historical Comparison

-- Track growth over time:
-- Run script monthly
-- Compare results over quarters
-- Identify growth patterns
-- Plan capacity accordingly

Projection Methods

-- Calculate growth rates:
-- Monthly segment size increases
-- New segment creation patterns
-- Tablespace consumption trends
-- Peak growth periods

Tablespace Planning

Space Allocation Review

-- Analyze current allocation:
-- Owner distribution across tablespaces
-- Segment type concentration
-- Growth patterns by tablespace
-- Free space availability

Optimization Opportunities

-- Consider reorganization when:
-- Uneven distribution across tablespaces
-- Mixed segment types causing fragmentation
-- Poor space utilization ratios
-- Performance impact from layout

Performance Implications

I/O Distribution

Hot Spot Identification

-- Look for storage hot spots:
-- Large segments in same tablespace
-- High-access objects on same storage
-- Unbalanced I/O distribution
-- Need for storage spreading

Load Balancing

-- Distribute storage load:
-- Spread large segments across devices
-- Balance tablespace I/O
-- Consider storage performance tiers
-- Optimize for access patterns

Memory Management

Buffer Pool Sizing

-- Size pools appropriately:
-- KEEP pool: Size for critical hot data
-- RECYCLE pool: Size for scan operations
-- DEFAULT pool: Remainder of buffer cache
-- Monitor pool hit ratios

Integration with Monitoring

Regular Assessment

Monthly Reviews

-- Track storage metrics:
-- Segment count changes
-- Size growth patterns
-- New tablespace requirements
-- Buffer pool effectiveness

Alerting Thresholds

-- Set alerts for:
-- Rapid segment growth (>50% monthly)
-- High extent counts (>1000 per segment)
-- Tablespace utilization (>85%)
-- Buffer pool imbalances

Trend Analysis

Growth Tracking

-- Monitor trends:
-- Owner-based growth rates
-- Segment type proliferation
-- Tablespace consumption patterns
-- Storage efficiency metrics

Performance Correlation

-- Correlate with performance:
-- I/O patterns vs storage layout
-- Buffer hit ratios vs pool assignments
-- Response times vs extent counts
-- Throughput vs storage distribution

Troubleshooting Applications

Storage Issues

Space Problems

-- When space issues arise:
-- Identify largest consumers
-- Review growth patterns
-- Plan immediate relief
-- Implement long-term solutions

Performance Problems

-- For storage-related performance issues:
-- Check buffer pool assignments
-- Review extent fragmentation
-- Analyze I/O distribution
-- Consider reorganization

Related Scripts

• dsegbig.sql - Large segment analysis
• dtable.sql - Table storage analysis
• ddbspace.sql - Tablespace usage analysis
• vbpcache.sql - Buffer pool analysis

Best Practices

1. Regular Monitoring

   • Run monthly for trend analysis
   • Compare results over time
   • Track growth patterns
   • Plan capacity proactively

2. Storage Optimization

   • Review buffer pool assignments
   • Monitor extent fragmentation
   • Balance I/O distribution
   • Optimize for access patterns

3. Capacity Planning

   • Project growth requirements
   • Plan tablespace expansion
   • Consider storage tiers
   • Monitor space utilization