DML UPDATE/DELETE与CR一致性读的秘密

2012/10/30 by Leave a Comment

这个问题源于OTN中文论坛的一个帖子<大事务中的更新丢失问题>：

环境为Oracle 10.2.0.4 on Linux x64
有一个大表，百万级，col1字段全为0
t1 事务A启动，把所有记录col2全更新为1
t2 事务B启动，根据主键，把一条记录更新为2，然后commit
t3 事务A执行完成，并COMMIT
t4 查询此表，发现col1全部为1，事务B的更新丢失了。
这是为什么呢，其中逻辑是怎样的
谢谢！

 

对于这个问题我想说明的是对于事务transaction 而言Oracle同样提供读一致性，称为transaction-level read consistency：

The database can also provide read consistency to all queries in a transaction, known as transaction-level read consistency. In this case, each statement in a transaction sees data from the same point in time, which is the time at which the transaction began.

Oracle Database Concepts
11g Release 2 (11.2)
Part Number E10713-04

 

为了证明和演示该事务级别的读一致性，我设计了以下演示：

有一张2个列的大表(T1,T2)， 其中分别有 T1=600000，T2=’U2′ 和  T1=900000和 T2=’U1’的 2行数据，T1为主键。

在A)时刻，Session A使用SELECT .. FOR UPDATE锁住T1=600000的这一行

在之后的B)时刻，Session B尝试update TAB set t2=’U3′ where t2=’U2′ 即更新所有T2=’U2’的数据行，但是因为 T1=600000，T2=’U2’这一行正好被Session A锁住了，所以Session B会一直等待’enq: TX – row lock contention’；T1=900000和 T2=’U1’的数据行位于Session B处理 T1=600000，T2=’U2’行等待之后才能处理到的数据块中。

在之后的C)时刻，Session C更新update TAB set t2=’U2′ where t1=900000;并commit， 即将T1=900000和 T2=’U1’更新为 T1=900000和 T2=’U2’，这样就符合Session B 更新Update的条件t2=’U2’了。

在D)时刻， Session A执行commit释放锁，Session B得以继续工作，当他处理到T1=900000的记录时存在以下分歧：

 

1)若update DML满足transaction-level read consistency，则它应当看到的是session B事务开始环境SCN(env SCN)时的块的前镜像，即虽然session C更新了t2=’U2’满足其条件，但是为了一致性，session B仍需要对该行所在数据块做APPLY UNDO，直到满足该session B事务开始时间点的Best CR块，而CR一致镜像中t2=’U1’，不满足Session B的更新条件， 那么session B在整个事务中仅更新一行数据 T1=600000，T2=’U2’，session B only Update One Rows。

2) 若update DML不满足transaction-level read consistency，则session B看到的是当前read commited的镜像,即是Session C已更新并提交的块镜像，此时的记录为T1=900000和 T2=’U2’符合session B的更新条件，则session B要更新2行数据。

 

我们来看一下实际实验的结果：

构建实验环境：

 

 

SQL> select * from v$version;

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

create table update_cr tablespace users as select rownum t1, 'MACLEAN     ' T2 
from dual connect by level  update update_cr set t2='U2' where t1=600000;

1 row updated.

SQL> update update_cr set t2='U1' where t1=900000;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from update_cr where t1=600000 or t1=900000;

        T1 T2
---------- ------------
    600000 U2
    900000 U1

 

 

在A)时刻，Session A使用SELECT .. FOR UPDATE锁住T1=600000的这一行:

 

session A:

SQL> select * from update_cr where t1=600000 for update;

        T1 T2
---------- ------------
    600000 U2

 

在之后的B)时刻，Session B尝试update TAB set t2=’U3′ where t2=’U2′ 即更新所有T2=’U2’的数据行，但是因为 T1=600000，T2=’U2’这一行正好被Session A锁住了，所以Session B会一直等待’enq: TX – row lock contention’；T1=900000和 T2=’U1’的数据行位于Session B处理 T1=600000，T2=’U2’行等待之后才能处理到的数据块中。

 

 

session B:

SQL> alter system flush buffer_cache;

System altered.

SQL> /

System altered.

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

Session altered.

SQL> update update_cr set t2='U3' where t2='U2';

 

 

在之后的C)时刻，Session C更新update TAB set t2=’U2′ where t1=900000;并commit， 即将T1=900000和 T2=’U1’更新为 T1=900000和 T2=’U2’，这样就符合Session B 更新Update的条件t2=’U2’了。

session C:

SQL> select * from update_cr where t1=900000;

        T1 T2
---------- ------------
    900000 U1

SQL> update update_cr set t2='U2' where t1=900000;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

 

 

之后session A 执行 commit;session B得以继续update，得到实验的结果：

 

session A 执行 commit;

session B;

SQL> update update_cr set t2='U3' where t2='U2';

1 row updated.

 

 

以看到以上实验的结果 update仅更新了一行数据，证明了观点1″若update DML满足transaction-level read consistency，则它应当看到的是session B事务开始环境SCN(env SCN)时的块的前镜像，即虽然session C更新了t2=’U2’满足其条件，但是为了一致性，session B仍需要对该行所在数据块做APPLY UNDO，直到满足该session B事务开始时间点的Best CR块，而CR一致镜像中t2=’U1’，不满足Session B的更新条件， 那么session B在整个事务中仅更新一行数据 T1=600000，T2=’U2’，session B only Update One Rows。”的正确性。

即update/delete之类的DML在Oracle中满足transaction-level read consistency，保证其所”看到的”是满足事务开始时间点读一致性的Consistent Read，这也是为什么DML会产生Consistent Read的原因之一。

我们回过头来看一下 上面Session B产生的10201 undo apply和10046 trace的内容，可以发现更多内容：

 

 

SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from update_cr where t1=600000 or t1=900000;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                                2589                                    4
                                3558                                    4

WAIT #139924171154784: nam='db file sequential read' ela= 19504 file#=3 block#=128 blocks=1 obj#=0 tim=1258427129863987
Applying CR undo to block 4 : 1000a1d itl entry 03:
xid: 0x0001.010.00000277 uba: 0x00c0300e.007c.1e
flg: ---- lkc: 1 fsc: 0x0000.00000000
WAIT #139924171154784: nam='db file sequential read' ela= 12957 file#=3 block#=12302 blocks=1 obj#=0 tim=1258427129877291
CRS upd rd env [0x7f42a284a704]: (scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00223e78 flg: 0x00000060) undo env [0x7fffe3fe7f60]: (
scn: 0x0000.00223eb9 xid: 0x0001.010.00000277 uba: 0x00c0300e.007c.1e statement num=2 parent xid: 0x0000.000.00000000 st-scn: 0xe404.00000000 hi-scn: 0x2100.00000000 ma-scn: 0x0000.00000000 flg: 0x62515e33)
CRS upd (before): 0xa5fe9198 cr-scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 cl-scn: 0x0000.00223eba sfl: 0
CRS upd (after) : 0xa5fe9198 cr-scn: 0x0000.00223eb9 xid: 0x0001.010.00000277 uba: 0x00c0300e.007c.1e cl-scn: 0x0000.00223eba sfl: 0

*** 2009-11-16 22:06:01.253
WAIT #139924171154784: nam='enq: TX - row lock contention' ela= 31358812 name|mode=1415053318 usn<<16 | slot=65552 sequence=631 obj#=76969 tim=1258427161253791
WAIT #139924171154784: nam='db file sequential read' ela= 36051 file#=4 block#=2589 blocks=1 obj#=76969 tim=1258427161290180
WAIT #139924171154784: nam='db file scattered read' ela= 14718 file#=4 block#=2590 blocks=98 obj#=76969 tim=1258427161305684
WAIT #139924171154784: nam='db file scattered read' ela= 26432 file#=4 block#=2690 blocks=126 obj#=76969 tim=1258427161338364
WAIT #139924171154784: nam='db file scattered read' ela= 38289 file#=4 block#=2818 blocks=126 obj#=76969 tim=1258427161384445
WAIT #139924171154784: nam='db file scattered read' ela= 24265 file#=4 block#=2946 blocks=126 obj#=76969 tim=1258427161416302
WAIT #139924171154784: nam='db file scattered read' ela= 21288 file#=4 block#=3074 blocks=126 obj#=76969 tim=1258427161444684
WAIT #139924171154784: nam='db file scattered read' ela= 23840 file#=4 block#=3202 blocks=126 obj#=76969 tim=1258427161476063
WAIT #139924171154784: nam='db file scattered read' ela= 27439 file#=4 block#=3330 blocks=126 obj#=76969 tim=1258427161511429
WAIT #139924171154784: nam='db file scattered read' ela= 24424 file#=4 block#=3458 blocks=126 obj#=76969 tim=1258427161543429
Applying CR undo to block 4 : 1000de6 itl entry 03:
xid: 0x0003.01f.00000345 uba: 0x00c03092.008f.34
flg: --U- lkc: 1 fsc: 0x0000.00223ef9
WAIT #139924171154784: nam='db file sequential read' ela= 8033 file#=3 block#=12434 blocks=1 obj#=0 tim=1258427161557534
CRS upd rd env [0x7f42a284a704]: (scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00223e78 flg: 0x00000060) undo env [0x7fffe3fe7f60]: (
WAIT #139924171154784: nam='db file sequential read' ela= 8033 file#=3 block#=12434 blocks=1 obj#=0 tim=1258427161557534
CRS upd rd env [0x7f42a284a704]: (scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00223e78 flg: 0x00000060) undo env [0x7fffe3fe7f60]: (
scn: 0x0000.00223ef8 xid: 0x0003.01f.00000345 uba: 0x00c03092.008f.34 statement num=8192 parent xid: 0x0000.000.0baf3fa0 st-scn: 0x3fa0.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00000000 flg: 0x00000000)
CRS upd (before): 0xa4fdaa08 cr-scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 cl-scn: 0x0000.00223eff sfl: 0
CRS upd (after) : 0xa4fdaa08 cr-scn: 0x0000.00223eb9 xid: 0x0003.01f.00000345 uba: 0x00c03092.008f.34 cl-scn: 0x0000.00223eff sfl: 0
WAIT #139924171154784: nam='db file scattered read' ela= 13029 file#=4 block#=3586 blocks=126 obj#=76969 tim=1258427161572511
WAIT #139924171154784: nam='db file scattered read' ela= 22282 file#=4 block#=3714 blocks=126 obj#=76969 tim=1258427161602324
WAIT #139924171154784: nam='db file sequential read' ela= 6127 file#=4 block#=522 blocks=1 obj#=76969 tim=1258427161615461
WAIT #139924171154784: nam='db file scattered read' ela= 13503 file#=4 block#=3842 blocks=42 obj#=76969 tim=1258427161629323

 

 

 

以上可以看到对T1=600000所在的数据块1000a1d=》datafile 4 259 apply了UNDO，其环境SCN 为scn: 0x0000.00223eb9:

Applying CR undo to block 4 : 1000a1d itl entry 03:
xid: 0x0001.010.00000277 uba: 0x00c0300e.007c.1e
flg: —- lkc: 1 fsc: 0x0000.00000000
WAIT #139924171154784: nam=’db file sequential read’ ela= 12957 file#=3 block#=12302 blocks=1 obj#=0 tim=1258427129877291
CRS upd rd env [0x7f42a284a704]: (scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00223e78 flg: 0x00000060) undo env [0x7fffe3fe7f60]: (
scn: 0x0000.00223eb9 xid: 0x0001.010.00000277 uba: 0x00c0300e.007c.1e statement num=2 parent xid: 0x0000.000.00000000 st-scn: 0xe404.00000000 hi-scn: 0x2100.00000000 ma-scn: 0x0000.00000000 flg: 0x62515e33)
CRS upd (before): 0xa5fe9198 cr-scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 cl-scn: 0x0000.00223eba sfl: 0
CRS upd (after) : 0xa5fe9198 cr-scn: 0x0000.00223eb9 xid: 0x0001.010.00000277 uba: 0x00c0300e.007c.1e cl-scn: 0x0000.00223eba sfl: 0

对于T1=900000的数据行所在块1000de6=》 datafile 4 3558 block同样apply了UNDO，其环境SCN均为00223eb9

Applying CR undo to block 4 : 1000de6 itl entry 03:
xid: 0x0003.01f.00000345 uba: 0x00c03092.008f.34
flg: –U- lkc: 1 fsc: 0x0000.00223ef9
WAIT #139924171154784: nam=’db file sequential read’ ela= 8033 file#=3 block#=12434 blocks=1 obj#=0 tim=1258427161557534
CRS upd rd env [0x7f42a284a704]: (scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: 0x0000.000.00000000 st-scn: 0x0000.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00223e78 flg: 0x00000060) undo env [0x7fffe3fe7f60]: (
scn: 0x0000.00223ef8 xid: 0x0003.01f.00000345 uba: 0x00c03092.008f.34 statement num=8192 parent xid: 0x0000.000.0baf3fa0 st-scn: 0x3fa0.00000000 hi-scn: 0x0000.00000000 ma-scn: 0x0000.00000000 flg: 0x00000000)
CRS upd (before): 0xa4fdaa08 cr-scn: 0x0000.00223eb9 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 cl-scn: 0x0000.00223eff sfl: 0
CRS upd (after) : 0xa4fdaa08 cr-scn: 0x0000.00223eb9 xid: 0x0003.01f.00000345 uba: 0x00c03092.008f.34 cl-scn: 0x0000.00223eff sfl: 0

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: ,

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

2011/10/11 by Leave a Comment

之前我介绍了<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.askmac.cn & www.askmac.cn

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

Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: , , ,

Oracle Internal Event:10200 Consistent Read诊断事件

2011/09/30 by Leave a Comment

10200(consistent read buffer status)内部诊断事件可以用于探测一致性读CR(consistent read)块的访问情况，虽然cr读的统计信息可以从v$sysstat或AWR/statspack中获取，但是10200 event还是我们研究Consistent Read一致性读的有力工具。该事件可以通过在会话session级别设置ALTER SESSION SET EVENTS 或 DBMS_SYSTEM.SET_EV. Set 来开启，一般调用级别为Level 10。

该事件返回的trace跟踪文件，记录了为了实现一致性读的目的，哪些数据块以及这些块的各历史版本在执行过程中被创建(CR block creation)并检验(CR block inspection)，以找出Best CR block满足Consistent一致性。

注意10200 Internal Event主要是被ktrgtc和ktrget(call ktrget to get one block ->calling KTR layer to apply RBS to have consistent read Block;)这2个Oracle内核功能函数触发，这2个内部函数Internal Function的主要作用：

ktrget:

  • Initializes a buffer cache CR scan request
  • Calls kcbgtcr for the best resident buffer to start from to build the CR buffer
  • Calls ktrgcm to build the CR buffer by applying undo
  • Returns CR buffer to the requestor

kcbgtcr:

  • If successful, returns the “best” candidate (performed by ktrexf or examination function)
  • Scans the hash bucket for the DBA for buffers that may be used to build a CR buffer
  • If not successful, calls kcbget

 

10200 event trace example:

 

[oracle@rh2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 30 21:23:47 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing option

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn & www.askmac.cn

SQL> create table tv(t1 int);

Table created.

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

Session altered.

SQL> select * from tv;

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

10200 trace

Consistent read started for block 0 : 0040081a
  env: (scn: 0x0000.000cf852 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00  statement num=0

parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 8sch: scn: 0x0000.00000000)
 CR exa ret 2 on: 0x600139d0  scn: 0xffff.ffffffff  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00

scn: 0xffff.ffffffff  sfl: 0

Consistent read finished for block 0 : 40081a

Consistent read started for block 0 : 0040e508
  env: (scn: 0x0000.000cf852  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00  statement num=0
parent xid: xid: 0x0000.000.00000000  scn: 0x0000.00000000 8sch: scn:
 0x0000.00000000)
Consistent read finished for block 0 : 40e508

 

以上trace中各代码的含义如下：

Consistent read started for block 0 : 0040081a
0 -> tablespace number 0040081a -> DBA

 

env: (scn: 0x0000.000cf852 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00
以上为环境/会话信息，这个scn是env_scn ，一般就是数据库的current_scn

 

SFL :0  -> SFL 为 Snapshot Flag

 

CR exa ret 2 -> 此处的ret为reture code返回代码，是ktrgtc/ktrget函数的返回码

 

以下为ktrgtc/ktrget函数部分可能返回代码的含义：

 

#define KCBRSTOP (8|0) /* return this one now */
#define KCBRSAVE (8|1) /* save this one and continue */
#define KCBRSKIP (0|1) /* skip over this one and continue */
#define KCBRQUIT (0|2) /* chuck all, return nothing and stop */
#define KCBRLAST (0|3) /* quit if read from disk else skip */

 

10200诊断事件在11g中得到了加强，通过该event我们可以获得更多有用的trace信息了：

 

SQL> select * from v$version;

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

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
MACLEAN

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

Session altered.

SQL> select * from tv;

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

SQL> oradebug setmypid;
Statement processed.

SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_28365.trc

trace content

ktrget2(): started for block   objd: 0x000040e1
env [0x2b54cde2a704]: (scn: 0x0000.0026b064  xid: 0x0000.000.00000000  uba: 0x00000000.0000.00
statement num=0  parent xid: 0x0000.000.00000000  st-scn: 0x0000.00000000  hi-scn: 0x0000.00000000
ma-scn: 0x0000.0026b053  flg: 0x00000660)
ktrexf(): returning 9 on:  0xbb132d0  cr-scn: 0xffff.ffffffff  xid: 0x0000.000.00000000
uba: 0x00000000.0000.00  cl-scn: 0xffff.ffffffff  sfl: 0
ktrgcm(): completed for block   objd: 0x000040e1
ktrget3(): completed for  block  objd: 0x000040e1
Filed Under: Oracle, Oracle Internal Research内部原理研究 Tagged With: , , , , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号