v$transaction tips

Question:  What is the v$transaction view and how can I use the v$transaction columns used_urec and used_ublk to measure undo and rollback information?

Answer:  The v$transaction view lists the active transactions in the system.

Oracle will insert an entry in v$transaction for each active transaction in the database. When the transaction is complete the v$transaction row will disappear.  Also see these related notes on v$resource_limit.

The v$transaction columns used_urec and v$transaction used_ublk show the number of undo records held by an active transaction.  This is useful for monitoring long-running DML.

The start_time column in the view v$transaction, the column shows only the starting time of the whole transaction.

From this v$transaction information we can compute the time required to roll-back a transaction with a query such as this, which joins v$session into v$transaction:

select
   t1.sid,
   t1.username,
   t2.xidusn,
   t2.used_urec,
   t2.used_ublk
from
   v$session     t1,
   v$transaction t2
where
   t1.saddr = t2.ses_addr;

Here is another script example using v$transaction which is extremely valuable for Oracle administrators who need to very quickly find out what is going on within their Oracle system.

select
   substr(s.username,1,18) username,
   substr(s.program,1,15) program,
   decode(s.command,
     0,'No Command',
     1,'Create Table',
     2,'Insert',
     3,'Select',
     6,'Update',
     7,'Delete',
     9,'Create Index',
     15,'Alter Table',
     21,'Create View',
     23,'Validate Index',
     35,'Alter Database',
     39,'Create Tablespace',
     41,'Drop Tablespace',
     40,'Alter Tablespace',
     53,'Drop User',
     62,'Analyze Table',
     63,'Analyze Index',
     s.command||': Other') command
from
   v$session     s,
   v$process     p,
   v$transaction t,
   v$rollstat    r,
   v$rollname    n
where s.paddr = p.addr
and s.taddr = t.addr (+)
and t.xidusn = r.usn (+)
and r.usn = n.usn (+)
order by 1;

Here is a sample of the output, showing the individual command for each session:

USERNAME PROGRAM COMMAND
------------------ --------------- ----------------
APPS f45runm@corp-hp Select
APPS S:\ORANT\BIN\F5 Insert
APPS S:\ORANT\BIN\R3 No Command

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.