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的主要作用:


  • 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


  • 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 - 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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing option

SQL> select * from global_name;

-------------------------------------------------------------------------------- &

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;


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:
Consistent read finished for block 0 : 40e508



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函数的返回码




#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 */




SQL> select * from v$version;

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

SQL> select * from global_name;


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

Session altered.

SQL> select * from tv;


SQL> oradebug setmypid;
Statement processed.

SQL> oradebug tracefile_name;

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



Procdump目前的版本号为3.04,可以从其Homepage上下载到该工具,该监控软件由Mark Russinovich开发,是一款Free Software。其官方介绍如下:

ProcDump is a command-line utility whose primary purpose is monitoring an application for
CPU spikes and generating crash dumps during a spike that an administrator or developer can use to
determine the cause of the spike. ProcDump also includes hung window monitoring
(using the same definition of a window hang that Windows and Task Manager use),
unhandled exception monitoring and can generate dumps based on the values of system performance counters.
It also can serve as a general process dump utility that you can embed in other scripts.

Procdump可以用于监控应用进程的CPU峰值,并在峰值阶段生成crash dump转储信息,其功能还包括:

  • 监控hang住的窗口
  • 可以为未处理的意外(unhandled exceptions)生成dump
  • 基于系统性能计数器(system performance counters)生成dump
  • 因为Procdump是基于命令行的,所以它可以被嵌入在脚本中


   -64     By default Procdump will capture a 32-bit dump of a 32-bit process
           when running on 64-bit Windows. This option overrides to create a
           64-bit dump.
   -b      Treat debug breakpoints as exceptions (otherwise ignore them).
   -c      CPU threshold at which to create a dump of the process.
   -e      Write a dump when the process encounters an unhandled exception.
           Include the 1 to create dump on first chance exceptions.
   -h      Write dump if process has a hung window (does not respond to
           window messages for at least 5 seconds).
   -m      Memory commit threshold in MB at which to create a dump of the
   -ma     Write a dump file with all process memory. The default
           dump format includes thread and handle information.
   -mp     Write a dump file with thread and handle information, and all
           read/write process memory. To minimize dump size, memory areas
           larger than 512MB are searched for, and if found, the largest
           area is excluded. A memory area is the collection of same
           sized memory allocation areas. The removal of this (cache)
           memory reduces Exchange and SQL Server dumps by over 90%.
   -n      Number of dumps to write before exiting.
   -o      Overwrite an existing dump file.
   -p      Trigger on the specified performance counter when the threshold
           is exceeded. Note: to specify a process counter when there are
           multiple instances of the process running, use the process ID
           with the following syntax: "\Process(_)\counter"
   -r      Reflect (clone) the process for the dump to minimize the time
           the process is suspended (Windows 7 and higher only).
   -s      Consecutive seconds before dump is written (default is 10).
   -t      Write a dump when the process terminates.
   -u      Treat CPU usage relative to a single core.
   -x      Launch the specified image with optional arguments.


To just create a dump of a running process, omit the CPU threshold. If
you omit the dump file name, it defaults to _.dmp.

Example: Write up to 3 dumps of a process named 'consume' when it exceeds
         20% CPU usage for five seconds to the directory
         c:\dump\consume with the name consume.dmp:
            C:\>procdump -c 20 -s 5 -n 3 -o consume c:\dump\consume
Example: Write a dump for a process named 'hang.exe' when one of it's
         windows is unresponsive for more than 5 seconds:
            C:\>procdump -h hang.exe hungwindow.dmp
Example: Write 3 dumps 5 seconds apart:
            C:\>procdump -s 5 -n 3 notepad.exe notepad.dmp
Example: Launch a process and then monitor it for excessive CPU usage:
            C:\>procdump -c 30 -s 10 -x consume.exe consume.dmp
Example: Write a dump of a process named 'iexplore' to a dump file
         that has the default name iexplore.dmp:
            C:\>procdump iexplore
Example: Write a dump of a process named 'outlook' when total system
         CPU usage exceeds 20% for 10 seconds:
            C:\>procdump outlook -p "\Processor(_Total)\% Processor Time" 20
Example: Write a dump of a process named 'outlook' when Outlook's handle count
         exceeds 10000:
            C:\>procdump outlook -p "\Process(Outlook)\Handle Count" 10000



Terminal A:
sqlplus / as sysdba
SQL> select count(1) from obj$,obj$,obj$;

Terminal B:
C:\Users\Maclean Liu>procdump  -c 20  -ma oracle.exe

ProcDump v3.04 - Writes process dump files
Copyright (C) 2009-2011 Mark Russinovich
Sysinternals -

Process:               oracle.exe (1776)
CPU threshold:         20% of system
Performance counter:   n/a
Commit threshold:      n/a
Threshold seconds:     10
Number of dumps:       1
Hung window check:     Disabled
Exception monitor:     Disabled
Terminate monitor:     Disabled
Dump file:             C:\Users\Maclean Liu\oracle.dmp

[19:56.51] CPU:          25%  1s
[19:56.52] CPU:          24%  2s
[19:56.53] CPU:          24%  3s
[19:56.54] CPU:          25%  4s
[19:56.55] CPU:          24%  5s
[19:56.56] CPU:          24%  6s
[19:56.57] CPU:          24%  7s
[19:56.58] CPU:          25%  8s
[19:56.59] CPU:          24%  9s
[19:57.00] CPU:          25%  10s

Process has hit CPU spike threshold.
Writing dump file C:\Users\Maclean Liu\oracle_110805_195700.dmp ...
Dump written.

Dump count reached.


Windbg是windows平台上的图形化dump分析工具,可以通过安装Debugging Tools for Windows包获得该程序,或者直接点击这里下载。

成功安装debugging工具后,从start->Debugging Tools for Windows (x86)->windbg启动工具,在主界面上选中File->Open Crash Dump 并输入dump文件的位置,成功分析后的显示:

[转]如何阅读systemstate dump

转自老白的<oracle rac 日记>一书,

dump systemstate产生的跟踪文件包含了系统中所有进程的进程状态等信息。每个进程对应跟踪文件中的一段内容,反映该进程的状态信息,包括进程信息,会话信息,enqueues信息(主要是lock的信息),缓冲区的信息和该进程在SGA区中持有的(held)对象的状态等信息。dump systemstate产生的跟踪文件是从dump那一刻开始到dump任务完成之间一段事件内的系统内所有进程的信息。









dump systemstate的语法为:

ALTER SESSION SET EVENTS ‘immediate trace name systemstate level 10’;


sqlplus -prelim / as sysdba

oradebug setmypid

oradebug unlimit;

oradebug dump systemstate 10


例如,当系统发生死锁(出现ORA-00060错误)时dump systemstate:

event = “60 trace name systemstate level 10”



10    Dump all processes (IGN state)

5     Level 4 + Dump all processes involved in wait chains (NLEAF state)

4     Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)

3     Level 2 + Dump only processes thought to be in a hang (IN_HANG state)

1-2   Only HANGANALYZE output, no process dump at all




一般来说,一份systemstate dump中包含了以下内容:

dump header文件头

process dump dump时所有的process的dump信息,每个process一个专门的章节。

call dump在process dump中,包含call dump

session dump每个process中,都有1个或多个(MTS时)session dump

enqueue dump

buffer dump在session dump中可能包含buffer dump


在阅读systemstate dump时,一般首先使用ASS工具来进行分析。ASS是oracle工程师编写的一个AWK脚本,用于分析systemstate dump文件,找出dump中可能存在问题的地方。通过ASS的输出结果,我们就可以发现一些blocker的线索,这些线索就是我们重点要查看的地方。


我们可以通过搜索SO的地址信息来定位某个SO,找到后分析这个SO的信息,并且通过PARENT SO的地址找到其PARENT,建立这些SO的关系图。比如我们找到一个SESSION的SO,就可以看看这个session属于哪个process,这个session正在执行的sql是什么,等等。通过这种分析,就把可能存在问题的SO及关联的SO全部找出来,这样就为进一步分析提供了素材。



1、标准的state object header(SO)

state object header中包含了一些基本的信息,比如:

SO: c00004ti4jierj, type: 2. owner: 0000000000, flag: init/-/-/0x00

其中SO是state object的号码;

type表示state object的类别;

TYPE: state object的已知类别:

2 process(进程)

3 call

4 session(会话信息)

5 enqueue(锁信息)

6 file infomation block(文件信息块,每个FIB标识一个文件)

11 broadcast handle(广播消息句柄)

12 KSV slave class state

13 ksvslvm

16 osp req holder(会话执行os操作的holder)

18 enqueue resource detail(锁资源详细资料)

19 ges message(ges消息)

20 namespace [ksxp] key

24 buffer [db buffer]

36 dml lock

37 temp table lock(临时表锁)

39 list of blocks(用于block cleanout的块列表清单)

40 transaction(事务)

41 dummy

44 sort segment handle(排序段句柄)

50 row cache enqueue

52 user lock

53 library cache lock

54 library cache pin

55 library cache load lock

59 cursor enqueue

61 process queue

62 queue reference

75 queue monitor sob



kssoinit;state object被初始化了

kssoflst;state object在freelist上

kssofcln;state object已经被pmon释放了。


State object header的数据结构如下:

struct kssob {

unsigned char       kssobtyp; /* state object的类别*/

unsigned char       kssobflg; /* flags */

unsigned char       kssobdelstage;

struct kssob *       kssobown; /*拥有者的SO指针*/

kgglk                   kssoblnk; /*在父对象成员链中的指针*/


2、processstate dump(ksupr)

processstate dump转储了进程的状态,从这些信息中我们可以了解进程的基本属性以及进程的状态。

在阅读processstate dump时,我们主要关注的进程的标识(FLAG),从中也可以知道进程的类别。从”(latch info)”中可以看到进程等待latch的情况,这也有助于了解进程故障的原因。另外,进程的OS信息对于进一步了解进程情况也是很有帮助的。

实际上,x$ksupr包含了进程的信息,通过该内存视图可以更进一步了解processstate dump的内容。

ADDR    地址

INDX    序号


KSSPAFLG   state object的状态:

KSSOINIT 0x01 // state object initialized

KSSOFLST 0x02 // state object is on free list

KSSOFCLN 0x04 // state object freed by PMON(for debugging)

KSSPAOWN  该SO的OWNER,如果自己是顶层的SO,那么owner为0

KSUPRFLG   该process的状态:

KSUPDEAD 0x01 process is dead and should be cleaned up

KSUPDSYS 0x02 detached,system process

KSUPDFAT 0x04 detached,fatal(system) process

KSUPDCLN 0x08 process is cleanup(pmon)

KSUPDSMN 0x10 process is smon

KSUPDPSU 0x20 pseudo process

KSUPDMSS 0x40 muti-stated server

KSUPDDPC 0x80 dispatcher process



KSLLALAQ   持有的latch

KSLLAWAT   正在等待的latch

KSLLAWHY  latch请求的上下文(用于debug)

KSLLAWER  latch请求的位置(用于debug)

KSLLASPN   本进程正在spin的latch

KSLLALOW  所持有latch级别的位图(0~9级)

KSLLAPSC   进程发出的POST消息的计数

KSLLAPRC   进程收到的POST消息的计数

KSLLAPRV   收到的最后一个POST的LOC ID,参考图中的①

KSLLAPSN   最后一个发送POST的LOC ID,参考图中的②





KRMENQ    0x01 enqueues

KRMLATCH  0x02 latches

KRMLIBCALK 0x03 library cache locks

KRMBUFLK  0x04 buffer locks

KSLLARPO   最后一个发送消息给这个进程的OS进程

KSLLASPO   这个进程最后一个发送信息过去的OS进程






KSUPRTID    用户终端名









KSUPRTFI    进程的trace文件名




KSUPRPFM  pga可释放的内存

KSUPRPMM pga使用的最大内存

3、session state object 





KSUSFUSR   0x00000001   user session (as opposed to recursive session)

KSUSFREC   0x00000002   recursive session(always internal)

KSUSFAUD   0x00000004   audit logon/logoff,used by cleanup

KSUSFDCO  0x00000008   disable commit/rollback from plsql

KSUSFSYS   0x00000010   user session created by system processes

KSUSFSGA   0x00000020   whether UGA is allocate in sga

KSUSFLOG   0x00000040   whether user session logs on to ORACLE

KSUSFMSS   0x00000080   user session created by multi-stated server

KSUSFDIT    0x00000100   disable (defer) interrupt

KSUSFCLC   0x00000200   counted for current license count decrement

KSUSFDET   0x00000400   session has been detached

KSUSFFEX   0x00000800   “forced exit”during shutdown normal

KSUSFCAC   0x00001000   (cloned) session is cached

KSUSFILS    0x00002000   default tx isolation level is serializable

KSUSFOIL    0x00004000   override serializable for READ COMMITTED

KSUSFIDL    0x00008000   idle session scheduler

KSUSFSKP   0x00010000   SKIP unusable indexes maintenance

KSUSFCDF   0x00020000   defer all deferrable constraint by default

KSUSFCND  0x00040000   deferable constraints are immediate

KSUSFIDT    0x00080000   session to be implicitly detached

KSUSFTLA   0x00100000   transaction audit logged

KSUSFJQR   0x00200000   recource checking in job q process enabled

KSUSFMGS  0x00400000   session is migratable

KSUSFGOD  0x00800000   migratable session need to get ownership id

KSUSFSDS   0x01000000   suppress/enable TDSCN      computations

KSUSFMSP   0x02000000   parent of migratable session

KSUSFMVC  0x04000000   MV container update progress

KSUSFNAS   0x08000000   an NLS alter session call was done

KSUSFTRU    0x10000000  a trusted callout was performed

KSUSFHOA   0x20000000   an HO agent was called

KSUSFSTZ   0x40000000   an alter session set time_zone was done

KSUSFSRF   0x80000000   summary refresh


4、call state object

Call state object是针对一个call的,我们查看call state object的时候一定要注意depth值,以此判断该call是用户调用还是递归调用。

5、enqueue state object  

从enqueue state object中,我们主要可以查看锁的类型、锁的模式以及flag。

6、transaction dump

Transaction dump对应的oracle内存结构是KTCXB,可以通过X$KTCXB来了解更详细的情况。


1          allocated but no transaction

2          transaction active

4          state object no longer valid

8          transaction about to commit/abort

10          space management transaction

20          recursive transaction

40          no undo logging

80          no change/commit,must rollback

100        use system undo segment (0)

200        valid undo segment assigned

400        undo seg assigned,lock acquired

800        change may have been made

1000      assigned undo seg

2000      required lock in cleanup

4000      is a pseudo space extent

8000      save the tx table & tx ctl block

10000    no read-only optimize for 2pc

20000    multiple sess attached to this tx

40000    commit scn future set

80000    dependent scn future set

100000   dist call failed,force rollback

200000   remote uncoordinated ddl tx

400000   coordinated global tx

800000   pdml transaction

1000000        next must be commit or rollback

2000000        coordinator in pdml

4000000        disable block level recovery

8000000        library and/or row caches dirty

10000000      serializable transaction

20000000      waiting for unbound transaction

40000000      loosely coupled transaction branch

80000000      long-running transaction



1    tx needs refresh on commit

2    delete performed in tx

4    concurrency check enabled

8    insert performed

10   dir path insert performed

20   fast rollback on net disconnect

40   do not commit this tx

80   this tx made remote change

100 all read-only optim enabled



Struct ktcev {

kenv             ktcevenv;

kuba             UBA的高水位;

kuba             ktcevucl;

sb2        在undo高水位块中的剩余空间;

kcbds     undo block的描述;

kdbafr    undo段头的DBA地址;

kturt *    指向undo seg的KTURT结构;



7、library object lock/handle

library object lock如下:


KGLLKBRO  0x0100   this lock is broken

KGLLKCBB   0x0200   this lock can be broken

KGLLKPNC  0x0400   “kgllkpnc” is a valid pin for the call

KGLLKPNS   0x0800   “kgllkpns” is a valid pin for the session

KGLLKCGA  0x1000   this lock is in CGA memory

KGLLKINH   0x2000   the instance lock is inherited

KGLLKLRU   0x4000   lock protects an object on the session cache lru

KGLLKKPC   0x8000   lock protects an object in the session keep cache

KGLLKRES   0x0010   reserved lock preventing handle from being freed

KGLLKCBK   0x0020   need to callback the client for delete/dump


作为library object的主体,handle的信息如图:


CRSR    cursor

TABL     table/view/sequence/synonym

BODY    body(e.g.,package body)

TRGR    trigger

INDX    index

CLST     cluster

KGLT    internal KGL testing

PIPE      pipe

LOB      lob

DIR       directory

QUEU    queue

OBJG     replication object group

PROP    replication propagator

JVSC     java source

JVRE     java resource

ROBJ     reserved for server-side RepAPI

REIP      replication internal package

CPOB    context policy object

EVNT    pub_sud internal information

SUMM   summary

DIMN    dimension

CTX      app context

OUTL    stored outlines

TULS     ruleset objects

RMGR   resource manager

XDBS    xdb schema

PPLN     pending scheduler plan

PCLS     pending scheduler class

SUBS     subscription information

LOCS    location information

RMOB   remote objects info

RSMD   RepAPI snapshot metadata

JVSD     java shared data

STFG    file group

TRANS  transformation

RELC     replication – log based child

STRM    stream:capture process in log-based replication

REVC    rule evaluation context

STAP     stream:apply process in log-based replication

RELS     source inlog-based replication

RELD    destination in log-based replication

IFSD     IFS schema

XDBC    XDB configuration management

USAG    user agent mapping

VOMDTABL  multi-versioned object for table

JSQI      scheduler-event queue info object

CDCS    change set

VOMDINDX multi-versioned object for index

STBO    sql tuing base object

HTSP     hintset    object

JSGA     scheduler global attributes

JSET     scheduler start time namespace

TABL_T temporary table

CLST_T temporary cluster

INDX_Ttemporary index

SCPD    sratch pad

JSLV      scheduler job slave

MODL   mining models



EXS       existent

NEX      no-existent


CRT       being created

ALT       being altered

DRP       being dropped

PRG       being purged

UPD      being uodated

RIV       marked for rolling invalidation

NRC      don’t recover when an exclusive pin fails

UDP      dep being updated

BOW     bad owner of database link

MEM     has frame memory associated with heap 0

REA       protected with read-only access at least once

NOA      protected with no access at least once


通过对library cache object/handle的分析,可以找到相关的sql以及cursor的状态。


EVENT 10051:"trace OPI calls"

Error:  ORA 10051
Text:   trace OPI calls
This is NOT an error but is a special EVENT code.
It should *NOT* be used unless explicitly requested by RD support.

Event 10051 allows you to track OPI calls on the server side.
This can be useful to home in on what sequence of events lead
to a problem. It complements SQL*Net trace and <Event:10046>
trace. You can quickly see where FAST UPI etc.. is in use.

Levels:    The event is just either on or off.

Output: The output is simply of the form:

OPI CALL: type= 2 argc= 2 cursor=  0 name=OPEN

where:    type     = the OPI call type (program interface function call)
argc     = Argument count
cursor     = the cursor number the call is being made against
name       = description of the program interface function call.

Interpreting DUMP LOGFILE Output                      <Note:29726.1>

EVENT 10235:"check memory manager internal structures"

  7.0 - 10.1.X   Check memory manager internal structures. 
  7.0 - 10.1.X "Check memory manager internal structures" 
  NOTE: Events should NEVER be set by customers unless advised to do so by

      Oracle Support Services. Read [NOTE:75713.1] before setting any event. 
Summary Syntax: 
  EVENT="10235 trace name context forever,  level LL" 
  (Always comment exactly when and why this event is being set) 
                syntax to set this event in sessions. This can cause 
                lots of ORA-600 errors against SGA heaps as not all  
                sessions using the SGA heaps will be using the same event 
                level. This applies to ALL levels except level 65536. 
  The event being set at all causes certain heap checks to be performed. 
   ***  WARNING *********************************************************** 
    ***   This event should only EVER be set at the request of Oracle Support. 
    ***   It can impact performance on most types of system. 
    ***   Level 2 and above can impact latch contention. 
    ***   Level 3 and above can have a *SEVERE* impact on performance. 
  The bottom 3 bits of the level cause the following checks to occur: 
     ~~~~~         ~~~~~~~~~~~~ 
     Level         Description 
     ~~~~~         ~~~~~~~~~~~~ 
        1             Fast check on heap free (kghfrh) 
        2             Do 1 AND fill memory with junk on alloc / free 
        3             Do 2 AND ensure the chunk belongs to given heap on free 
        4             Do 3 AND make permanent chunks freeable so they can  
                      also be checked 
                       This level can give rise to increased memory use 
                       and can trigger false ORA-4030 and false ORA-4031 
  Oracle 9205 onwards only: 
    65536             This is introduced by the diagnostic enhancement in 
                      bug 3293155. It is a totally independent bit setting 
                      which has minimal impact on performance (unless ORed  
                      with other levels). When this is set Oracle tries to 
                      keep comments with "permanent" memory allocations 
                      which can be useful for memory leak problems if the 
                      leaked memory appears to be a leak of "perm" memory. 
                      This level can be set/unset dynamically but will only  
                      store comments in "perm" memory allocated when the  
                      event is set. 
  There are additional values which Oracle Support can use. 
  This event may be used to try to catch HEAP corruption problems closer  
  to when they occur.  Typically level 12 is required to get close to the 
  corruption but this can impact performance too much to be useful. 
Example Output / Interpreting Output: 
  The event should cause an ORA-600 and heapdump to be produced if an 
  error is detected.  

EVENT: 10060 dump predicates in optimizer (kko)

Text:   dump predicates in optimizer (kko)
        This is a special EVENT code.
        It should *NOT* be used unless explicitly requested by ST support.
        This event can be useful in conjunction with an explain plan
        to confirm which operations occur where in the execution plan.
        It dumps the predicates into a special table.
        - Available in 7.1.3 onwards when using CBO
        - NOTE: This does NOT do anything under RULE
        - After 7.1.3 you must create a special table called
          "kkoipt_table" before you set the event. This table should be
          in the schema of the person running under event 10060.
            create table kkoipt_table (
                    c1 integer, c2 varchar2(80)
        - To enable tracing:
            ALTER session set events '10060 trace name context forever';
        - Run the statement to be traced
        - Set formatting:
          set pages 999
          column c1 format 990
          column c2 format a75
        - select * from KKOIPT_TABLE;
        - Best used in conjunction with <Event:10053> trace and an
          execution plan.
KKOIPT_TABLE.C2 output information
fptconst                      - Folding constants
fptrnum                       - Remove rownum predicates
fptwhr                        - Remove all where predicates except remaining
                                rownum predicates
frofkks (rowid lookup)        - Rowid Lookup
frofkks[i] (and-equal lookup) - Start Key (And-Equal)
frofkke[i] (and-equal lookup) - End Key   (And-Equal)
froiand                       - Index only predicate
frofkksm[i] (sort-merge)      - Sort-Merg Key
frosand (sort-merge)          - Sort-Merge Predicates
frojand (sort-merge)          - Join Predicates
frofkks[i] (index start key)  - Index Start Key
frofkke[i] (index stop key)   - Index End Key
frofand (hash part)           - Table Predicate (Hash)
froiand (index only filter)   - Index Only Predicate
frofand                       - Table Predicate
froutand                      - Outer Join Predicates
select a.* from memp a, memp b
where a.sal > 5050 and rownum < 4 and 1=1
  and a.empno (+) = b.empno;
Output: (from kkoipt_table)
         1 fptrnum
         2  4>ROWNUM
         3 Table:
         4 MEMP
         5 frofand
         6  "A"."SAL">5050
         7 Table:
         8 MEMP
         9 frofand
        10  "A"."EMPNO"="B"."EMPNO"


沪公网安备 31010802001379号