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
Leave a Reply