Oracle Internal Event:10201 consistent read undo application诊断事件

之前我介绍了<Oracle Internal Event:10200 Consistent Read诊断事件>一致性逻辑读诊断事件的用法和trace含义，10201 "consistent read undo application"是另一个十分有用的内部诊断事件，该事件可以用于诊断一致性读取时的UNDO应用情况。   10201 event可以用于探测为了创建CR(consistent read) block块以满足要求的SCN需要应用多少undo，该10201 event还可以配和10200 event使用。利用该10201 event，我们可以验证一些内部问题，例如何时会发生块上的cleanup。 注意启用10201 event可能导致在短期内产生大量的trace文件，所以不要随意在生产系统中使用。 10201 Internal Event主要会被ktrgcm( CR-rollback (ktrgcm() ) 、 ktrrbkblk  、 ktrcrf 这三个Oracle内核函数触发，这三个Internal Function的主要作用：  

• ktrgcm - common CR read code CR Requestor-Side Algorithm The following statistics are incremented by ktrgcm: “cleanouts and rollbacks - consistent read” is incremented if UNDO is applied to BUFFER and CLEANOUT is performed. “rollbacks only - consistent read gets” is incremented if UNDO is applied to BUFFER and no CLEANOUT is performed. “cleanouts only - consistent read gets” is incremented if no UNDO is applied and CLEANOUT is performed. “no work - consistent read gets” is incremented if no UNDO is applied and no CLEANOUT is performed. When UNDO is applied to produce a CR BUFFER, other UNDO blocks should be read. When CLEANOUT is performed, the TX transaction table must be read.

• ktrrbkblk retrieves previous row version with ktundo,When all rows checked, calls ktrrbkblk to rollback block (calls ktundo)      常见的stack call : ktrviupk kdiulk kcoubk ktundo kturbk ktrrbkblk ktrvfxs qerixFetch qertbFetchByRowID

•   ktrcrf (rdbms/kernel/knl/ktr.c kcbchg1 ==> ktrcrf)

   

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com & www.askmaclean.com

SQL> create table maclean (t1 int);

Table created.

SQL> insert into maclean values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1213588

SQL> delete maclean;

1 row deleted.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush buffer_cache;

System altered.

SQL>  alter session set events '10201 trace name context forever,level 10';

Session altered.

SQL> select * from maclean as of scn 1213588;

        T1
----------
         1

trace content=====================================

Applying CR undo to block 0 : 408a81 itl entry 02:
          xid:  0x0009.00b.0000017d uba: 0x00c00212.0092.2d
          flg: --U-    lkc:  1     fsc: 0x0007.0012849c
CRS upd rd env [0x2ac2ec7b5660]: (scn: 0x0000.00128494  xid: 0x0000.000.00000000
uba: 0x00000000.0000.00  statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x
0000.00000000  hi-scn: 0x0000.00000000  ma-scn: 0x0000.00000000  flg: 0x00000800)
undo env [0x7fffe8b74d10]: (
scn: 0x0000.0012849b  xid: 0x0009.00b.0000017d  uba: 0x00c00212.0092.2d  statement num=151548811
parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x9
f58.00000000  ma-scn: 0x2ac2.ec7b9c88  flg: 0x00002ac2)
CRS upd (before): 0x69bdfe68  cr-scn: 0x0000.00128494  xid: 0x0000.000.00000000
uba: 0x00000000.0000.00  cl-scn: 0x0000.001287c1  sfl: 0
CRS upd (after) : 0x69bdfe68  cr-scn: 0x0000.00128494  xid: 0x0009.00b.0000017d
uba: 0x00c00212.0092.2d  cl-scn: 0x0000.001287c1  sfl: 0

以上trace中各代码的含义如下： Applying CR undo to block 0 : 408a81 itl entry 02: 这里的0是 tablespace number, 408a81 是 DBA 而 itl entry 02 是被回滚的事务槽记录 CRS upd rd env [0x2ac2ec7b5660]: (scn: 0x0000.00128494 .....undo env [0x7fffe8b74d10]: ( 以上为当前读取的环境信息，包括env_scn等 CRS upd (before): 0x69bdfe68 cr-scn: 0x0000.00128494 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 cl-scn: 0x0000.001287c1 sfl: 0 CRS upd (before)为回滚完成前的Buffer descriptor CRS upd (after) : 0x69bdfe68 cr-scn: 0x0000.00128494 CR-SCN为1213588 如查询语句所要求的 xid: 0x0009.00b.0000017d uba: 0x00c00212.0092.2d cl-scn: 0x0000.001287c1 sfl: 0 CRS upd (after) 为回滚完成后的Buffer descriptor