v$transaction

V$TRANSACTION lists the active transactions in the system. When the transaction is complete, (either COMMIT or ROLLBACK), the entry should go away.

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

示例一：
session A:
建立測試表，並更新

SQL> create table scott.test as select * from dba_objects;

SQL> update test set OBJECT_ID=OBJECT_ID+1;

已更新50343行。

session B:
查詢v$transaction
SQL> select START_TIME,START_SCNB,USED_UBLK,USED_UREC,LOG_IO,PHY_IO
2 from v$transaction;

START_TIME        START_SCNB USED_UBLK USED_UREC LOG_IO   PHY_IO
---------------   ---------- ---------- --------- ------- ----------
05/05/08 15:13:53 708551      698        54422     185843   0

session A:
再更新一些記錄

SQL> update test set wner='AAA';
已更新50343行。

session B:
再查詢v$transaction

SQL> /
START_TIME         START_SCNB USED_UBLK USED_UREC LOG_IO PHY_IO
----------------   ---------- --------- --------- ------- ----------
05/05/08 15:13:53 708551     1269      96902     353324 1

列出這幾列的含義，自己可以對比一下：

start_time --> start_time :)
start_scnb --> 開始的scn
used_ublk --> 佔用的undo block
used_urec --> undo 記錄的行數
log_io --> 邏輯io 注意並非Consistent gets，有專門一列：CR_GET
phy_io --> 物理io

如果想要得到session那就可以再加上查詢SES_ADDR列：

SES_ADDR User session object address -->對應v$session 的saddr列，再進一步自然可以找到正在執行的sql.

如果想要對應回滾段則：
XIDUSN Undo segment number -->使用的回滾段id,可以和v$rollstat對應

想更深入瞭解一個transaction,有了sql就能更深入了。

示例二：

用如下SQL查詢到正在執行的事務，如其used_urec欄位不斷增加，說明該事物正在繼續，如果該欄位不斷下降，說明該事物正在回滾。

SQL> SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;

SID USERNAME XIDUSN USED_UREC USED_UBLKITPUB
---------- ------------------------------ ---------- ---------- ----------
118 CCP 102 12 1

SQL> 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;

SID USERNAME XIDUSN USED_UREC USED_UBLK
--------- ------------------------------ ---------- ---------- ----------
596 GCC 87 2 1

學習oracle動態效能表--v$transaction

相關文章