ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 学习oracle动态性能表--v$transaction

学习oracle动态性能表--v$transaction

原创 Linux操作系统 作者:xuelu2000 时间:2009-04-04 15:55:34 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/95233/viewspace-584647/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-23

  • 博文量
    89
  • 访问量
    238952