Dec 06 2007

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

Published by kamus at 2:06 pm under Oracle RDBMS

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

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

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

  1. 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。

  1. SQL> SELECT ktuxesiz FROM x$ktuxe WHERE ktuxesta != 'INACTIVE';
  2.  
  3.   KTUXESIZ
  4. ----------
  5.          2

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

  1. SQL> select addr,xidusn,xidslot,xidsqn,status from v$transaction ;
  2.  
  3. ADDR         XIDUSN    XIDSLOT     XIDSQN STATUS
  4. -------- ---------- ---------- ---------- ----------------
  5. 45270E48          4         14        371 ACTIVE

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

  1. SQL> select sid,username,sql_id,sql_hash_value from v$session where taddr='45270E48';
  2.  
  3.        SID USERNAME                       SQL_ID        SQL_HASH_VALUE
  4. ---------- ------------------------------ ------------- --------------
  5.        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，来查看里面到底存了什么。

  1. SQL> SELECT * FROM V$ROLLNAME WHERE USN = 4;
  2.  
  3.        USN NAME
  4. ---------- ------------------------------
  5.          4 _SYSSMU4_1195301203$
  6.  
  7. --仅仅是为了查看undo block的dump，不需要dump出undo heander，这里只是给出语法
  8. SQL> ALTER SYSTEM DUMP UNDO HEADER "_SYSSMU4_1195301203$";
  9.  
  10. System altered
  11.  
  12. SQL> ALTER SYSTEM DUMP UNDO BLOCK "_SYSSMU4_1195301203$" XID 4 14 371;
  13.  
  14. 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视图中就可以查到到底是什么了。

  1. SQL> select owner,object_name,object_type from dba_objects where object_id=18002;
  2.  
  3. OWNER                          OBJECT_NAME                    OBJECT_TYPE
  4. ------------------------------ ------------------------------ -------------------
  5. 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 Responses to “如何知道active transaction正在操作的对象”

  1. yxyup Says:
    December 6th, 2007 at 6:07 pm [ Quote ]

    那么下面就继续来看看是不是2个undo block以及为什么需要2个undo block。
    ？？

  2. albly Says:
    December 7th, 2007 at 3:42 pm [ Quote ]

    Metalink怎么找不到这篇 Note:43653.1？internal的吗

  3. kamus Says:
    December 8th, 2007 at 1:13 am [ Quote ]

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

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

Trackback URI | Comments RSS


Leave a Reply

-> :( :! :D :| :? :X ;;) :) ;)