Transaction Analysis (gvtrans.sql)

What This Script Does

Provides comprehensive analysis of active transactions across RAC instances, showing transaction duration, undo usage, I/O statistics, and rollback segment information. This script is essential for monitoring long-running transactions and their resource consumption.

The Script

rem  gvtrans.sql
rem
ttitle 'Active Transaction Analysis'
rem
set linesize 325
rem
col inst_id format 9999 heading 'Inst|ID'
col sid_ser format a12
col username format a22
col start_time format a18
col machine format a25
col osuser format a10
col module format a30
col sql_id format a14
col held_time           format 999,999.99  heading 'HELD|TIME|(MIN)'
col UNDO_SEG_SLOT_SEQ_NBR format a20 heading 'Undo Address|Seg-Slot-Seq'
col used_urec format 999,999,999 heading 'Used|URec'
col used_ublk format 999,999,999 heading 'Used|UBlks'
col log_io format 999,999,999 heading 'Lgcl|IO'
col phy_io format 999,999,999 heading 'Phys|IO'
col cr_get format 999,999,999 heading 'CR|Get'
col cr_change format 999,999,999 heading 'CR|Chng'
col rbs_name format a25 heading 'RollBack|Seg|NM'
rem
   SELECT s.inst_id
        , s.username
        , s.sid||','||s.serial# sid_ser
        , s.machine
        , s.osuser
        , s.module
        , s.PREV_SQL_ID
        , s.sql_id
        , t.used_ublk
        , (sysdate - t.start_date)*60*24 held_time
        , t.start_time
        , t.used_urec
        , t.log_io
        , t.phy_io
        , t.cr_get
        , t.cr_change
        , t.xidusn||' - '||t.xidslot||' - '||t.xidsqn  undo_seg_slot_seq_nbr
        , t.ses_addr obj_addr
        , r.name     rbs_name
     FROM gv$session s
        , gv$transaction t
left join v$rollname r
       on t.xidusn = r.usn
    WHERE s.inst_id = t.inst_id
      AND s.saddr = t.ses_addr
      and s.taddr = t.addr
   order by held_time desc
/

Usage

-- Show all active transactions
@gvtrans.sql

Parameters

The script requires no input parameters and automatically displays all active transactions.

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT on GV$SESSION
SELECT on GV$TRANSACTION
SELECT on V$ROLLNAME

Sample Output

                                Active Transaction Analysis

Inst USERNAME              SID_SER      MACHINE                   OSUSER     MODULE                         PREV_SQL_ID   SQL_ID         Used    HELD      START_TIME         Used     Lgcl      Phys        CR        CR Undo Address        RollBack
  ID                                                                                                                                      UBlks   TIME                        URec       IO        IO       Get      Chng Seg-Slot-Seq         Seg NM
---- ---------------------- ------------ ------------------------- ---------- ------------------------------ -------------- ------------- -------- -------- ------------------ -------- --------- --------- --------- --------- -------------------- -------------------------
   1 BATCH_USER            1234,5678    batchserver01.company.com batch      BatchProcessor.exe             def456ghi789  abc123def456    12,345   125.50 06-01-25 12:30:15   98,765 1,234,567   456,789 2,345,678   123,456 2 - 15 - 987654321  RBS_BATCH_01
   2 SALES_USER            2345,6789    salesapp02.company.com    webapp     SalesApplication.jar           ghi789jkl012  def456ghi789     8,765    89.25 06-01-25 13:15:30   45,632   987,654   234,567 1,456,789    89,123 5 - 22 - 876543210  RBS_SALES_02
   1 DBA_USER              3456,7890    dbaserver03.company.com   oracle     sqlplus.exe                    jkl012mno345  ghi789jkl012     2,345    45.75 06-01-25 14:00:45   12,345   456,789   123,456   678,901    45,678 1 - 8 - 765432109   RBS_SYSTEM

Key Output Columns

Inst ID: RAC instance number
USERNAME: Database username
SID_SER: Session ID and serial number
MACHINE: Client machine name
OSUSER: Operating system username
MODULE: Application module name
PREV_SQL_ID: Previous SQL statement identifier
SQL_ID: Current SQL statement identifier
Used UBlks: Undo blocks consumed
HELD TIME (MIN): Transaction duration in minutes
START_TIME: Transaction start timestamp
Used URec: Undo records generated
Lgcl IO: Logical I/O operations
Phys IO: Physical I/O operations
CR Get: Consistent read gets
CR Chng: Consistent read changes
Undo Address Seg-Slot-Seq: Undo segment identifier
RollBack Seg NM: Rollback segment name

Transaction Metrics Explained

Undo Usage

Used UBlks: Number of undo blocks consumed
Used URec: Number of undo records generated
Higher values indicate more database modifications

I/O Statistics

Lgcl IO: Logical reads from buffer cache
Phys IO: Physical reads from disk
CR Get: Consistent read block gets
CR Chng: Consistent read modifications

Time Tracking

HELD TIME: Duration since transaction started
START_TIME: Exact timestamp when transaction began

Common Use Cases

Long-Running Transaction Detection

-- Identify transactions running for extended periods
@gvtrans.sql
-- Look for high HELD TIME values

Undo Space Monitoring

-- Monitor undo space consumption
@gvtrans.sql
-- Analyze Used UBlks and Used URec columns

Performance Impact Analysis

-- Assess transaction impact on system resources
@gvtrans.sql
-- Review I/O statistics and undo usage

Rollback Segment Usage

-- Monitor rollback segment distribution
@gvtrans.sql
-- Analyze RollBack Seg NM column for load balancing

RAC Transaction Distribution

-- Check transaction spread across RAC instances
@gvtrans.sql
-- Compare Inst ID distribution

Warning Signs to Monitor

Long-Held Transactions

HELD TIME > 60 minutes may indicate issues
Consider transaction design and commit frequency

High Undo Usage

Large Used UBlks values may exhaust undo space
Monitor undo tablespace usage

Excessive I/O

High Phys IO values indicate disk-intensive operations
May impact overall system performance

Performance Impact

Low Impact: Reads system views with minimal overhead
Real-time Data: Shows current transaction state
RAC Comprehensive: Covers all instances simultaneously

Troubleshooting Tips

No Transactions Displayed

Verify that active transactions exist
Check if transactions have been committed/rolled back