V$TRANSACTION

Friday, August 15, 2008

V$TRANSACTION

V$TRANSACTION lists the active transactions in the system.

Column	Datatype	Description
`ADDR`	`RAW(4 \| 8)`	Address of the transaction state object
`XIDUSN`	`NUMBER`	Undo segment number
`XIDSLOT`	`NUMBER`	Slot number
`XIDSQN`	`NUMBER`	Sequence number
`UBAFIL`	`NUMBER`	Undo block address (UBA) filenum
`UBABLK`	`NUMBER`	UBA block number
`UBASQN`	`NUMBER`	UBA sequence number
`UBAREC`	`NUMBER`	UBA record number
`STATUS`	`VARCHAR2(16)`	Status
`START_TIME`	`VARCHAR2(20)`	Start time (wall clock)
`START_SCNB`	`NUMBER`	Start system change number (SCN) base
`START_SCNW`	`NUMBER`	Start SCN wrap
`START_UEXT`	`NUMBER`	Start extent number
`START_UBAFIL`	`NUMBER`	Start UBA file number
`START_UBABLK`	`NUMBER`	Start UBA block number
`START_UBASQN`	`NUMBER`	Start UBA sequence number
`START_UBAREC`	`NUMBER`	Start UBA record number
`SES_ADDR`	`RAW(4 \| 8)`	User session object address
`FLAG`	`NUMBER`	Flag
`SPACE`	`VARCHAR2(3)`	`YES` if a space transaction
`RECURSIVE`	`VARCHAR2(3)`	`YES` if a recursive transaction
`NOUNDO`	`VARCHAR2(3)`	`YES` if a no undo transaction
`PTX`	`VARCHAR 2(3)`	`YES` if parallel transaction
`NAME`	`VARCHAR2(256)`	Name of a named transaction
`PRV_XIDUSN`	`NUMBER`	Previous transaction undo segment number
`PRV_XIDSLT`	`NUMBER`	Previous transaction slot number
`PRV_XIDSQN`	`NUMBER`	Previous transaction sequence number
`PTX_XIDUSN`	`NUMBER`	Rollback segment number of the parent XID
`PTX_XIDSLT`	`NUMBER`	Slot number of the parent XID
`PTX_XIDSQN`	`NUMBER`	Sequence number of the parent XID
`DSCN-B`	`NUMBER`	This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in `DSCN_BASE`.
`DSCN-W`	`NUMBER`	This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in `DSCN_WRAP`.
`USED_UBLK`	`NUMBER`	Number of undo blocks used
`USED_UREC`	`NUMBER`	Number of undo records used
`LOG_IO`	`NUMBER`	Logical I/O
`PHY_IO`	`NUMBER`	Physical I/O
`CR_GET`	`NUMBER`	Consistent gets
`CR_CHANGE`	`NUMBER`	Consistent changes
`START_DATE`	`DATE`	Start time (wall clock)
`DSCN_BASE`	`NUMBER`	Dependent SCN base
`DSCN_WRAP`	`NUMBER`	Dependent SCN wrap
`START_SCN`	`NUMBER`	Start SCN
`DEPENDENT_SCN`	`NUMBER`	Dependent SCN
`XID`	`RAW(8)`	Transaction XID
`PRV_XID`	`RAW(8)`	Previous transaction XID
`PTX_XID`	`RAW(8)`	Parent transaction XID

Note:

The following columns together give unique transaction id:

XIDUSN - Undo segment number
XIDSLOT - NUMBER Slot number
XIDSQN - NUMBER Sequence number

The following columns explain the number of UNDO blocks / UNDO records used per transaction:

USED_UBLK - Number of undo blocks used
USED_UREC - Number of undo records used

These two columns will give estimation about the number of undo blocks that need to be rolled back in the case of transaction rollback. When the values decrease to 0, the transaction disappears from V$TRANSACTION. To monitor the rollback, use script

SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK

FROM V$SESSION A, V$TRANSACTION B

WHERE A.SADDR=B.SES_ADDR;

STATUS column explains the status of the transaction. Before a normal/transactional shutdown, check the view to see if there is any ACTIVE transactions:

ACTIVE – the transaction is active.

SELECT XIDUSN, XIDSLOT, XIDSQN, SES_ADDR, STATUS

FROM V$TRANSACTION;

Before issuing shutdown immediate, it would be recommended to check the following views, especially when the database needs to be brought down for a very short period of time:

For large queries:

        select count(*) from v$session_longops where time_remaining>0;

For large transactions:

        select sum(used_ublk) from v$transaction;

A result greater than 0 for the first query and a large value returned for the second one would mean a relatively long time to wait until the shutdown immediate completes.

Script to show current running transactions

select a.username, a.program, c.name as rbsname,

b.status, b.phy_io

from V$SESSION a, v$transaction b, v$rollname c

where a.saddr = b.ses_addr and b.xidusn = c.usn and b.phy_io > 0

order by 5 desc;

Use V$TRANSACTION to find sessions generating lots of redo:

V$TRANSACTION contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

SELECT s.sid, s.serial#, s.username,

s.program, t.used_ublk, t.used_urec

FROM V$SESSION s, v$transaction t

WHERE s.taddr = t.addr

ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.