如何知道active transaction正在操作的对象

这个问题的研究是一步一步来的。

最开始,客户想知道对于一个已经kill掉的数据库会话,如何评估SMON需要多久才能回滚成功这个会话之前做过的事务,其实也就是这个会话多久才会被完全kill掉。

由于被kill掉的会话所打开的事务已经无法在v$transation视图中查询到,因此需要从数据库基表x$ktuxe中查询。

SELECT ktuxesiz FROM x$ktuxe WHERE ktuxesta != ‘INACTIVE’;

可以获得这个会话还在使用的undo block数量,然后过10秒钟,再查询一次,之后两次得到的数值相减,就是10秒内SMON回滚成功的undo block数量,这样再跟当前的ktuxesiz 值比较一下,就可以估算出大概还需要多长时间才能完成整个工作。
Metalink Note:43653.1详细描述了x$ktuxe的各列含义,Eygle的一篇文章也描述了这个方法。

然后,我在自己的机器上实验对这个基表的查询,忽然发现自己的机器上也有一个仍然active的transaction。但是我自己机器上的数据库我并没有做什么DML操作,为什么会有一个active transaction呢?这个transaction还使用了2个undo block。

SQL> SELECT ktuxesiz FROM x$ktuxe WHERE ktuxesta != ‘INACTIVE’;

KTUXESIZ
———-
2

继续检查自己机器上的情况,确实是有一个active的事务。

SQL> select addr,xidusn,xidslot,xidsqn,status from v$transaction ;

ADDR XIDUSN XIDSLOT XIDSQN STATUS
——– ———- ———- ———- —————-
45270E48 4 14 371 ACTIVE

查一下这个事务当前在执行什么SQL,SQL_ID是null(SQL_ID是Oracle10g以后才有的字段),也就是无从判断这个事务到底在做什么。

SQL> select sid,username,sql_id,sql_hash_value from v$session where taddr=’45270E48′;

SID USERNAME SQL_ID SQL_HASH_VALUE
———- —————————— ————- ————–
132 SCOTT 0

其实后来发现虽然SQL_ID没有值,但是PREV_SQL_ID还是有值,并且通过这个值从v$sql中就可以查到刚才这个事务做了什么。
其实SQL是:DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
恩,没错,从PLAN_TABLE中删除记录,这是一条recursive sql。

当时并没有想到可以去查PREV_SQL_ID值(因为这个字段中查出来的SQL往往都是没太大意义的递归SQL),但是从username = scott上我个人可以判断基本上是因为我做了SQL的trace,因为当时我只登陆了scott用户,只运行过查看SQL执行计划的autotrace,那么因为autotrace功能其实就是使用了PLAN_TABLE表来存储中间结果,在显示完执行计划以后再删除相应记录。

所以如果Oracle在显示完执行计划,并且删除了PLAN_TABLE的相应记录以后,并没有做commit,那么就会有一个active transaction存在。但是这仅仅是一个猜测,如何验证呢?

可以想到的,是dump undo block,来查看里面到底存了什么。

SQL> SELECT * FROM V$ROLLNAME WHERE USN = 4;

USN NAME
———- ——————————
4 _SYSSMU4_1195301203$

–仅仅是为了查看undo block的dump,不需要dump出undo heander,这里只是给出语法
SQL> ALTER SYSTEM DUMP UNDO HEADER “_SYSSMU4_1195301203$”;

System altered

SQL> ALTER SYSTEM DUMP UNDO BLOCK “_SYSSMU4_1195301203$” XID 4 14 371;

System altered

然后查看trace文件。找到如下这部分

* Rec #0x3 slt: 0x0e objn: 18002(0x00004652) objd: 18002 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x02

其中objn就是这个undo record上记录的相关对象ID,从dba_objects视图中就可以查到到底是什么了。

SQL> select owner,object_name,object_type from dba_objects where object_id=18002;

OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— ——————-
SCOTT PLAN_TABLE TABLE

确实是SCOTTPLAN_TABLE,至此已经可以验证确实是因为set autotrace并且显示了执行计划之后产生了active transaction。

文章到这里其实就可以结束了,但是记得前面x$ktuxe基表中显示这个transaction需要2个undo block,那么下面就继续来看看是不是2个undo block以及为什么需要2个undo block。

上面这两个问题很简单。

1. 在trace文件中我们可以看到确实dump出了两个undo block,即使不去仔细看trace,从trace文件的最后总结也可以知道是2个。

Total undo blocks scanned = 2
Total undo records scanned = 8
Total undo blocks dumped = 2
Total undo records dumped = 8

2. 为什么是2个?因为1个undo block放不下delete PLAN_TABLE之后的记录前镜像。

3 thoughts on “如何知道active transaction正在操作的对象

  1. @yxyup
    更新了一下,因为后来发现,这两个问题一句话就可以搞定了。。。索性不写了

    @albly
    这我倒没注意,可能是的

Leave a Reply to yxyup Cancel reply

Your email address will not be published. Required fields are marked *