Rollback Segment Statistics (vrolln.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.

Rollback Segment Statistics

This script provides comprehensive statistics for rollback segments (undo tablespaces in modern Oracle), essential for undo space management and performance analysis.

Script: vrolln.sql

rem  vrolln.sql
rem
set linesize 132
rem
ttitle 'Rollback Statistics'
rem
col usn format 90 heading 'ID'
col name format a10 heading 'NAME'
col extents format 999 heading 'EXTS'
col rssize format 999999 heading 'CURRENT|SIZE|(KB)'
col writes format 999999999 heading 'WRITES|(KB)'
col xacts format b99 heading 'TRX'
col gets format 99999999 heading 'GETS'
col waits format b99999 heading 'WAITS'
col optsize format 999999 heading 'OPTIMUM|SIZE|(KB)'
col hwmsize format 999999 heading 'HIGH|WATER|MARK|(KB)'
col shrinks format b99999 heading 'SHRINK'
col wraps format b9999999 heading 'WRAPS'
col extends format b99999 heading 'EXTEND'
col aveshrink format b999999 heading 'AVG|SHRINK|(KB)'
col aveactive format b999999 heading 'AVG|ACTIVE|(KB)'
col status format a1 heading 'S'
col curext format 999 heading 'CUR|EXT'
col curblk format 99999 heading 'CUR|BLK'
rem
break on report
compute sum -
     of extents rssize writes xacts gets waits shrinks wraps extends -
     on report
rem
select s.usn,
       decode( s.status, 'ONLINE', null,
                         'OFFLINE', 'X', '?' ) status,
       r.name, s.extents,
       s.rssize/1024 rssize, s.writes/1024 writes, s.xacts, s.gets,
       s.waits, s.optsize/1024 optsize, s.hwmsize/1024 hwmsize,
       s.shrinks, s.wraps, s.extends,
       s.aveshrink/1024 aveshrink, s.aveactive/1024 aveactive,
       s.curext, s.curblk
  from v$rollname r, v$rollstat s
 where s.usn = r.usn;
rem
set linesize 80

What This Script Does

This script provides detailed rollback/undo segment statistics including:

• Space Usage: Current size, high-water mark, and optimal size
• Activity Metrics: Transaction counts, gets, waits, and writes
• Dynamic Behavior: Shrinks, extends, and wraps
• Current Position: Current extent and block
• Summary Totals: Computed sums for key metrics

Usage

-- Run to see current rollback segment statistics
@vrolln.sql

-- For automatic undo management, also check:
SELECT tablespace_name, status, contents, extent_management
FROM dba_tablespaces WHERE contents = 'UNDO';

Required Privileges

• SELECT privilege on V$ROLLNAME
• SELECT privilege on V$ROLLSTAT
• Typically requires DBA role or SELECT_CATALOG_ROLE

Sample Output

                                     Rollback Statistics

 ID S NAME       EXTS CURRENT  WRITES    TRX      GETS WAITS OPTIMUM HIGH    SHRINK    WRAPS EXTEND    AVG     AVG  CUR CUR
                      SIZE     (KB)             WATER      SHRINK  ACTIVE EXT BLK
                      (KB)                      MARK      (KB)    (KB)
                                                (KB)
--- - ---------- ---- ------- --------- --- --------- ----- ------- ------- ------- --------- ------ ------- --- -----
  0   SYSTEM        2      64         0   0         1     0      64      64       0         0      0       0   1     2
  1   RBS01         8    2048    45231   12     89234     0    1024    4096       2        15      3     512   512   4   156
  2   RBS02        12    3072    67854   18    134567     1    1024    8192       4        22      5     768   756   8   234
  3   RBS03         6    1536    23456    8     45678     0    1024    2048       1         8      1     256   234   3    89
  4   RBS04        10    2560    89012   22    178901     2    1024    6144       3        28      4     640   612   6   345
  5   RBS05         4    1024    12345    5     23456     0    1024    1024       0         4      0     128   123   2    67
--- - ---------- ---- ------- --------- --- --------- ----- ------- ------- ------- --------- ------ ------- --- -----
sum               42   10304   237898   65    471837     3               15        82     13

Key Output Columns

Column	Description
ID	Undo segment number (USN)
S	Status: blank=ONLINE, X=OFFLINE, ?=unknown
NAME	Rollback/undo segment name
EXTS	Number of extents currently allocated
CURRENT SIZE (KB)	Current size of segment in KB
WRITES (KB)	Total KB written to this segment
TRX	Current number of active transactions
GETS	Number of header gets (access attempts)
WAITS	Number of waits for undo header blocks
OPTIMUM SIZE (KB)	Oracle’s calculated optimal size
HIGH WATER MARK (KB)	Maximum size ever reached
SHRINK	Number of times segment has shrunk
WRAPS	Number of times segment has wrapped
EXTEND	Number of times segment has extended
AVG SHRINK (KB)	Average shrink size
AVG ACTIVE (KB)	Average active undo size
CUR EXT	Current extent number
CUR BLK	Current block within extent

Understanding the Metrics

Space Management

• Current Size vs. Optimum: Shows if segments are appropriately sized
• High Water Mark: Indicates peak usage requirements
• Shrinks/Extends: Dynamic sizing activity

Performance Indicators

• Waits: Non-zero values indicate contention (should be minimal)
• Gets: Access frequency to segment headers
• Wraps: How often segments cycle through their space

Transaction Activity

• TRX: Current concurrent transactions per segment
• Writes: Total undo generation by segment

Common Use Cases

1. Undo Space Analysis

-- Current undo usage summary
SELECT
  SUM(rssize)/1024 as total_undo_mb,
  SUM(hwmsize)/1024 as peak_undo_mb,
  SUM(xacts) as active_transactions,
  ROUND(AVG(DECODE(gets, 0, 0, waits/gets*100)), 2) as avg_wait_pct
FROM v$rollstat;

2. Undo Contention Detection

-- Find segments with waits
SELECT r.name, s.gets, s.waits,
       ROUND(DECODE(s.gets, 0, 0, s.waits/s.gets*100), 2) as wait_pct
FROM v$rollname r, v$rollstat s
WHERE s.usn = r.usn
  AND s.waits > 0
ORDER BY wait_pct DESC;

3. Dynamic Sizing Analysis

-- Segments with high extend/shrink activity
SELECT r.name, s.extends, s.shrinks, s.wraps,
       s.rssize/1024 as current_mb,
       s.optsize/1024 as optimal_mb,
       s.hwmsize/1024 as peak_mb
FROM v$rollname r, v$rollstat s
WHERE s.usn = r.usn
  AND (s.extends > 10 OR s.shrinks > 5)
ORDER BY s.extends DESC;

4. Transaction Distribution

-- Check transaction distribution across segments
SELECT r.name, s.xacts,
       ROUND(s.xacts * 100 / SUM(s.xacts) OVER(), 1) as pct_of_total
FROM v$rollname r, v$rollstat s
WHERE s.usn = r.usn
  AND s.xacts > 0
ORDER BY s.xacts DESC;

Undo Tablespace Analysis (Modern Oracle)

Check Automatic Undo Management

-- Verify automatic undo management settings
SELECT name, value, description
FROM v$parameter
WHERE name IN ('undo_management', 'undo_tablespace', 'undo_retention');

-- Check undo tablespace details
SELECT tablespace_name, file_name, bytes/1024/1024 as mb,
       autoextensible, maxbytes/1024/1024 as max_mb
FROM dba_data_files
WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace');

Undo Space Monitoring

-- Current undo usage
SELECT
  tablespace_name,
  status,
  SUM(bytes)/1024/1024 as allocated_mb,
  SUM(DECODE(status, 'ACTIVE', bytes, 0))/1024/1024 as active_mb,
  SUM(DECODE(status, 'EXPIRED', bytes, 0))/1024/1024 as expired_mb,
  SUM(DECODE(status, 'UNEXPIRED', bytes, 0))/1024/1024 as unexpired_mb
FROM dba_undo_extents
GROUP BY tablespace_name, status;

Performance Tuning

Optimal Undo Configuration

-- Calculate required undo space
SELECT
  ROUND(MAX(maxquerylen)) as max_query_sec,
  ROUND(AVG(txncount)) as avg_active_txns,
  ROUND(MAX(maxquerylen) * AVG(undoblks) * 8192 / 1024 / 1024) as min_undo_mb
FROM v$undostat
WHERE begin_time >= SYSDATE - 7;

Identify Long-Running Transactions

-- Find long-running transactions
SELECT s.sid, s.serial#, s.username, s.program,
       t.start_time, t.used_ublk * 8192 / 1024 / 1024 as undo_mb,
       ROUND((SYSDATE - t.start_time) * 24 * 60) as duration_min
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr
ORDER BY t.start_time;

Troubleshooting Common Issues

ORA-01555 Snapshot Too Old

-- Check undo retention and usage
SELECT
  ROUND(retention) as retention_sec,
  ROUND(tuned_undoretention) as tuned_retention_sec,
  maxquerylen as longest_query_sec
FROM v$undostat
WHERE begin_time >= SYSDATE - 1
ORDER BY begin_time DESC;

Undo Tablespace Full

-- Check undo space usage trends
SELECT
  TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') as time_period,
  ROUND(ssolderrcnt) as snapshot_old_errors,
  ROUND(nospaceerrcnt) as no_space_errors,
  ROUND(txncount) as txn_count,
  ROUND(maxquerylen/60) as max_query_min
FROM v$undostat
WHERE begin_time >= SYSDATE - 1
ORDER BY begin_time;

Integration with Other Scripts

This script works well with:

• Session Analysis (gvsess.sql) - For transaction-level analysis
• Tablespace Usage (dtspace.sql) - For undo tablespace space analysis
• Database Health Check (db.sql) - For overall system health
• Transaction Enqueues (gvtrans.sql) - For transaction lock analysis

Best Practices

Monitoring Guidelines

1. Regular Reviews: Check rollback statistics during peak usage
2. Wait Analysis: Investigate any non-zero waits immediately
3. Sizing Trends: Monitor extend/shrink patterns for optimal sizing
4. Transaction Distribution: Ensure even distribution across segments

Optimization Tips

1. Undo Retention: Set based on longest-running queries
2. Undo Tablespace Size: Size for peak usage plus 20% buffer
3. Multiple Undo Segments: In manual mode, use multiple segments for concurrency
4. Autoextend: Enable with reasonable maxsize for undo tablespaces

This script provides essential insights for managing Oracle’s undo system and preventing undo-related performance issues.