10200

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

Oracle Internal Event:10200 Consistent Read诊断事件