Oracle Internal Event:10200 Consistent Read诊断事件

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.oracledatabase12g.com & www.askmaclean.com

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