Segments with Many Extents Analysis (dsegmax.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 dsegmax.sql script.

The Script

rem  dsegmax.sql
rem
rem  linesize = 101
rem
set linesize 132
rem
ttitle 'Segment Definitions with Many Extents'
rem
col owner format a12 heading 'OWNER'
col segment_name format a30 heading 'SEGMENT NAME'
col segment_type format a8 heading 'SEGMENT|TYPE'
col tablespace_name format a15 heading 'TABLESPACE'
col kbytes format 9,999,999 heading 'BYTES (K)'
col blocks format 999999 heading 'BLOCKS'
col extents format 99999 heading 'EXTS'
col max_extents format 99999 heading 'MAX|EXTS'
rem
break on report on owner skip 1
compute sum of kbytes blocks extents on report owner
rem
select owner, segment_name, segment_type, tablespace_name,
       bytes/1024 kbytes, blocks, extents, max_extents
  from sys.dba_segments
 where owner like upper('&owner')
   and segment_type like upper('&type')
   and segment_name like upper('&object')
   and extents >= &extents
 order by owner, segment_name;
rem
set linesize 80

What This Script Does

This script identifies database segments that have accumulated many extents, which can indicate fragmentation issues and potential performance problems. It helps DBAs find objects that may benefit from reorganization, rebuild, or tablespace maintenance to improve I/O efficiency and space management.

Key Features

• Extent Threshold Filtering: Shows only segments exceeding a specified extent count
• Flexible Filtering: Filter by owner, segment type, and object name patterns
• Size Information: Displays segment size in kilobytes and blocks
• Space Analysis: Shows current and maximum extent limits
• Organized Output: Groups results by owner with summary totals

Usage

Run the script and provide filter criteria when prompted:

@dsegmax.sql

Input Parameters:

• Owner: Schema owner name or pattern (use % for all owners)
• Type: Segment type or pattern (TABLE, INDEX, LOBSEGMENT, etc.)
• Object: Segment name or pattern (use % for all objects)
• Extents: Minimum number of extents threshold (e.g., 100, 500, 1000)

Required Privileges

SELECT on SYS.DBA_SEGMENTS

Sample Output

                                    Segment Definitions with Many Extents

OWNER        SEGMENT NAME                   SEGMENT  TABLESPACE     BYTES (K)  BLOCKS  EXTS MAX
                                            TYPE                                               EXTS
------------ ------------------------------ -------- --------------- --------- ------- ----- -----
HR           EMPLOYEES                      TABLE    USERS             524,288   65,536   512  2,048
             EMP_HISTORY                    TABLE    USERS           1,048,576  131,072 1,024  2,048
             JOBS_HIST_IDX                  INDEX    USERS             262,144   32,768   256  1,024
                                                                     --------- -------   -----
sum                                                                  1,835,008  229,376  1,792

SALES        CUSTOMER_ORDERS                TABLE    DATA            4,194,304  524,288 2,048  4,096
             ORDERS_IDX1                    INDEX    INDEXES           524,288   65,536   512  2,048
             ORDERS_IDX2                    INDEX    INDEXES           262,144   32,768   256  1,024
                                                                     --------- -------   -----
sum                                                                  4,980,736  622,592  2,816

FINANCE      TRANSACTION_LOG                TABLE    DATA            8,388,608 1,048,576 4,096  8,192
             TXN_LOG_PK                     INDEX    INDEXES           131,072   16,384   128    512
                                                                     --------- -------   -----
sum                                                                  8,519,680 1,064,960  4,224

                                                                     ========= =======   =====
sum                                                                 15,335,424 1,916,928  8,832

Key Output Columns

• OWNER: Schema that owns the segment
• SEGMENT NAME: Name of the segment (table, index, etc.)
• SEGMENT TYPE: Type of segment (TABLE, INDEX, LOBSEGMENT, etc.)
• TABLESPACE: Tablespace containing the segment
• BYTES (K): Total size of the segment in kilobytes
• BLOCKS: Number of database blocks allocated
• EXTS: Current number of extents
• MAX EXTS: Maximum number of extents allowed

Understanding Extent Fragmentation

Why Many Extents Matter

Performance Impact

• I/O Overhead: More extents mean more I/O operations
• Full Table Scans: Multiple extents can impact scan performance
• Space Management: Fragmented objects are harder to manage

Storage Efficiency

• Space Waste: Many small extents can lead to space gaps
• Backup Impact: Fragmented objects take longer to backup
• Maintenance Overhead: More complex space calculations

Extent Thresholds

Normal Range

• < 100 extents: Generally acceptable for most objects
• 100-500 extents: Monitor but usually acceptable
• 500-1000 extents: Consider reorganization

High Fragmentation

• > 1000 extents: Strong candidate for reorganization
• > 2000 extents: Should be prioritized for rebuild
• > 4000 extents: Critical - immediate attention needed

Common Use Cases

1. Space Management

   • Identify highly fragmented objects
   • Plan reorganization activities
   • Optimize storage efficiency

2. Performance Optimization

   • Find objects causing I/O inefficiency
   • Prioritize rebuild candidates
   • Improve full table scan performance

3. Maintenance Planning

   • Schedule object reorganization
   • Plan tablespace maintenance
   • Optimize backup performance

4. Capacity Planning

   • Understand space usage patterns
   • Plan for storage growth
   • Optimize extent sizing

Analysis Examples

Find Highly Fragmented Tables

Owner: %
Type: TABLE
Object: %
Extents: 1000

Check Specific Schema

Owner: SALES
Type: %
Object: %
Extents: 100

Focus on Indexes

Owner: %
Type: INDEX
Object: %
Extents: 500

Remediation Strategies

Table Reorganization

-- Online table redefinition (11g+)
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname => 'HR',
    orig_table => 'EMPLOYEES'
  );
END;
/

-- Traditional rebuild
ALTER TABLE hr.employees MOVE TABLESPACE users;

Index Rebuild

-- Rebuild fragmented indexes
ALTER INDEX hr.emp_idx REBUILD ONLINE;
ALTER INDEX sales.orders_idx1 REBUILD TABLESPACE indexes;

Tablespace Maintenance

-- Coalesce free space
ALTER TABLESPACE users COALESCE;

-- Resize datafiles if needed
ALTER DATABASE DATAFILE '/path/to/datafile.dbf' RESIZE 2G;

Advanced Analysis

Extent Size Analysis

-- Check extent sizes for segments
SELECT owner, segment_name,
       MIN(bytes/1024) min_extent_kb,
       MAX(bytes/1024) max_extent_kb,
       AVG(bytes/1024) avg_extent_kb
FROM dba_extents
WHERE (owner, segment_name) IN (
  SELECT owner, segment_name
  FROM dba_segments
  WHERE extents > 500
)
GROUP BY owner, segment_name;

Growth Pattern Analysis

-- Monitor extent growth over time
-- Run dsegmax.sql periodically and compare results
-- Track which objects are growing rapidly

Performance Considerations

Script Performance

• DBA_SEGMENTS is a data dictionary view
• Large extent threshold values run faster
• Consider time-based execution during low activity

System Impact

• Reorganization operations can be resource-intensive
• Plan maintenance during appropriate windows
• Consider online operations where possible

Troubleshooting

No Results Returned

• Lower the extent threshold
• Check if the filter criteria are too restrictive
• Verify privileges on DBA_SEGMENTS

Too Many Results

• Increase the extent threshold
• Use more specific owner or object filters
• Focus on largest objects first

High Extent Counts

• May indicate inadequate initial extent sizing
• Could suggest high data growth rate
• May need tablespace maintenance

Related Scripts

• dcontig.sql - Free space coalescing analysis
• dsegbig.sql - Large segment analysis
• dtable.sql - Table storage analysis
• dindex.sql - Index storage analysis

Best Practices

1. Regular Monitoring

   • Run monthly during maintenance windows
   • Track extent growth trends
   • Set up automated monitoring

2. Proactive Management

   • Set appropriate initial extent sizes
   • Monitor rapid growth objects
   • Plan regular reorganization schedules

3. Performance Optimization

   • Prioritize high-usage objects
   • Consider partitioning for very large objects
   • Optimize tablespace configuration