10200(consistent read buffer status)內(nèi)部診斷事件可以用于探測(cè)一致性讀CR(consistent read)塊的訪(fǎng)問(wèn)情況，雖然cr讀的統(tǒng)計(jì)信息可以從v$sysstat或AWR/statspack中獲取，但是10200 event還是我們研究Consistent Read一致性讀的有力工具。該事件可以通過(guò)在會(huì)話(huà)session級(jí)別設(shè)置ALTER SESSION SET EVENTS 或 DBMS_SYSTEM.SET_EV. Set 來(lái)開(kāi)啟，一般調(diào)用級(jí)別為L(zhǎng)evel 10。 該事件返回的trace跟蹤文件，記錄了為了實(shí)現(xiàn)一致性讀的目的，哪些數(shù)據(jù)塊以及這些塊的各歷史版本在執(zhí)行過(guò)程中被創(chuàng)建(CR block creation)并檢驗(yàn)(CR block inspection)，以找出Best CR block滿(mǎn)足Consistent一致性。 注意10200 Internal Event主要是被ktrgtc和ktrget(call ktrget to get one block ->calling KTR layer to apply RBS to have consistent read Block;)這2個(gè)Oracle內(nèi)核功能函數(shù)觸發(fā)，這2個(gè)內(nèi)部函數(shù)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 sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 30 21:23:47 2011Copyright (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 optionSQL> select * from global_name;GLOBAL_NAME -------------------------------------------------------------------------------- www.oracledatabase12g.com & www.askmaclean.comSQL> 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 ----------110200 traceConsistent read started for block 0 : 0040081aenv: (scn: 0x0000.000cf852 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0parent 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.00scn: 0xffff.ffffffff sfl: 0Consistent read finished for block 0 : 40081aConsistent read started for block 0 : 0040e508env: (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 以上為環(huán)境/會(huì)話(huà)信息，這個(gè)scn是env_scn ，一般就是數(shù)據(jù)庫(kù)的current_scn ? SFL :0? -> SFL 為 Snapshot Flag ? CR exa ret 2 -> 此處的ret為reture code返回代碼，是ktrgtc/ktrget函數(shù)的返回碼 ? 以下為ktrgtc/ktrget函數(shù)部分可能返回代碼的含義： ?

#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中得到了加強(qiáng)，通過(guò)該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 - ProductionSQL> select * from global_name;GLOBAL_NAME -------------------------------------------------------------------------------- MACLEANSQL> alter session set events '10200 trace name context forever, level 10';Session altered.SQL> select * from tv;T1 ----------1SQL> oradebug setmypid; Statement processed.SQL> oradebug tracefile_name; /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_28365.trctrace contentktrget2(): 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

轉(zhuǎn)載于:https://www.cnblogs.com/macleanoracle/archive/2013/03/19/2968024.html

總結(jié)

以上是生活随笔為你收集整理的Oracle Internal Event:10200 Consistent Read诊断事件的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。