空间管理您的位置: ITPUB个人空间 » myfriend2010的个人空间 » 日志

天地不仁,以万物为刍狗!

ORACLE诊断事件的总结-转

上一篇 / 下一篇 2007-07-01 00:00:00 / 个人分类：oracle

Oracle为RDBMS提供了多种的诊断工具，诊断事件(Event)是其中一种常用、好用的方法，它使DBA可以方便的转储数据库各种结构及跟踪特定事件的发生.

　　一、Event的通常格式及分类
)YGJGu;L%@j0　　
/z?f7M4lZ ll0　　1、通常格式如下：
6d(j4ny8q`#E,te/r1|0　　EVENT="<事件名称><动作><跟踪项目><范围限定>"
V7@,Bq6~@%l;Wg0　　
d:aq)pw.S]0　　2、 Event分类
/L w)W(k#SZY2v0　　诊断事件大体上可以分为四类：ITPUB个人空间PI)jAN.r7b0GK'o
　　a．转储类事件：它们主要用于转储Oracle的一些结构，例如转储一下控制文件、数据文件头等内容。
T}&f\:jzB0　　b．捕捉类事件：它们用于捕捉一些Error事件的发生，例如捕捉一下ORA-04031发生时一些Rdbms信息，以判断是Bug还是其它原因引起的这方面的问题。
n;TGuV?8{$Q{0　　c．改变执行途径类事件：它们用于改主一些Oracle内部代码的执行途径，例如设置10269将会使Smon进程不去合并那些Free的空间。ITPUB个人空间v[%cBE'Y
　　d．跟踪类事件：这们用于获取一些跟踪信息以用于Sql调优等方面，最典型的便是10046了，将会对Sql进行跟踪。ITPUB个人空间 kp1y?m hm
　　3、说明:ITPUB个人空间 `.Md4\k7Y'Z8R
　　a．如果immediate放在第一个说明是无条件事件，即命令发出即转储到跟踪文件。
$nk)@v@W1k+q_0　　b． trace name位于第二、三项，除它们外的其它限定词是供Oracle内部开发组用的。
1?%e-uvJ3qLjb"]'Pl0　　c． level通常位于1-10之间(10046有时用到12)，10意味着转储事件所有的信息。例如当转储控制文件时，level1表示转储控制文件头，而level 10表明转储控制文件全部内容。ITPUB个人空间-G6qY-Rg
　　d．转储所生成的trace文件在user_dump_dest初始化参数指定的位置。

　　二、说一说设置的问题了
/Gs-?!Q]Os0　　ITPUB个人空间"Ae Jh$l;xC!z
　　可以在init.ora中设置所需的事件，这将对所有会话期打开的会话进行跟踪，也可以用alter session set event 等方法设置事件跟踪，这将打开正在进行会话的事件跟踪。
Lt F9oi9dS0　　
U&M$F+US5U(l_jB0　　1、在init.ora中设置跟踪事件的方法ITPUB个人空间9_'dKcKV6WF5l
　　a．语法
7X)K/wp/i{x!Ur&m0　　EVENT=”event 语法|,level n|：event 语法|,level n|…”
'FnL [|sf.GH0　　b．举例
9RFGA#g8A2gOc'Y(F0　　event=”10231 trace name context forever,level 10’
1W_/~Z\}j0S$u0　　c．可以这样设置多个事件：ITPUB个人空间0~K"s~G`y7?gl w(V
　　EVENT="
w:H+o4Pp0　　10231 trace name context forever, level 10:ITPUB个人空间3c {0};tp'I?s
　　10232 trace name context forever, level 10"ITPUB个人空间-TYf%x/T"h$H
　　
4h${/sip#C+ny!Y0　　2、通过Alter session/system set events这种方法ITPUB个人空间,m#|Rx7dap
　　举个例子大家就明白了
　　Example:
7v([*V Io8k0　　Alter session set events ‘immediate trace name controlf level 10’;ITPUB个人空间.g"VLXA+|r
　　Alter session set events ‘immediate trace name blockdump level 112511416’; (*)
X?6r-CE{/k0　　在oracle8x及之上的版本也有这样的语句：ITPUB个人空间4l6_6^"fi._
　　Alter system dump datafile 13 block 15;实现的功能与(*)是类似的。
"\D%{~\l8Um0　　
dzT1b5yJ;y0　　3、使用DBMS_SYSTEM.SET_EV的方法
3^5\aB[*Y0　　a．过和定义如下ITPUB个人空间4EJ?,G? M#FT7V
　　DBMS_SYSTEM.SET_EV(ITPUB个人空间;zOf*B7~^
　　SI Binary_integer,ITPUB个人空间-B/xu-XS)S @$K1x
　　SE Binary_integer,ITPUB个人空间Y$hit&Dlq'M;N
　　EV Binary_integer,ITPUB个人空间 X yGWxba
　　LE Binary_integer,
Vtc#Ymf Y.h ^%t0　　NM Binary_integer);
/?f%^MY BTC F"u0　　ITPUB个人空间WO7D0h-t{
　　SI: 即v$session中的sidITPUB个人空间fwrh9[NC/p
　　SE:即v$session中的serial#
i#cc`$cN0　　EV:要设置的事件
)RI{3V nh/PDFB0　　LE:要设置事件的级别ITPUB个人空间 g"v8|U:U
　　NM:名称ITPUB个人空间KvG'SAe
　　b．举个例子，以10046为例
m-MRzJO j+h+j0　　SQL> EXECUTE SYS.DBMS_SYSTEM.SET_EV(sid,serial#,10046,12,'')；ITPUB个人空间l jl`"EK-P
　　
k Nn\,P0　　4、使用Oradebug来设置诊断事件的方法ITPUB个人空间 yR8zq-K'~KL
　　同样举个例子大家就明白了：ITPUB个人空间X?0w)v;u;N V0X6{
　　a．找到spid
fM2h1~/B0　　SQL>select username, sid, serial#, paddr from v$session where username='qiuyb';
@ V3i/nJ!zUX0　　ITPUB个人空间0q!B mFKnk$Frj
　　USERNAME SID SERIAL# PADDR
bD'|*Zfdv0　　--------------------------------------------------------
%m_T7}$^1xf6q0　　HRB3 265 910 C000000084435AD8ITPUB个人空间w,]A6esh7F
　　ITPUB个人空间%rOK8Ak2t$O1bD X
　　SQL>SELECT ADDR,PID,SPID FROM V$PROCESS WHERE ADDR= C000000084435AD8';ITPUB个人空间 W,IerfvJ w
　　ADDR PID SPID
.gd&vB_,z!f0　　------------------------------------------
8m J5t+j$X}$Z1~p0　　C000000084435AD8 91 4835ITPUB个人空间0?&n!nRn
　　
'SpojYxz0　　b．设置事件，以10046为例
R%\s+]`&NnL3O(~vE0　　sqlplus /nolog
Sws9[ad.r ?Bj0　　SQL>connect / as sysdba;ITPUB个人空间4EQ+lqz
　　SQL>oradebug setospid 4835
QB"NBOWSc0　　SQL>oradebug unlimit　　　--不限制转储文件的大小ITPUB个人空间2W8H)p3V`;NN
　　SQL> oradebug event 10046 trace name context forever,level 12 --设置事件进行sql跟踪
6RbD.E;sl2Y*[ `0　　ITPUB个人空间(`h9]y g
　　SQL> oradebug event 10046 trace name context off --关闭跟踪ITPUB个人空间)T0|%U r O-h9^!?D
　　ITPUB个人空间"TN LOM4E
　　注意不要用oradug去跟踪oracle的smon,pmon等几个进程，操作不当可能会杀掉这几个后台进和引起宕库。ITPUB个人空间0H\8oiM j6gH0L
　　ITPUB个人空间| f&KGT B
　　三、你可能的问题
@chF(r8F nt0　　
%D+g0B+x@X6tT,|\0　　1、我如何知道在系统中设置了哪些event?
#y2XDFw"h y6D0　　回答：
4YMN2q)Dt0　　a．如果你的事件是在init.ora中设置的可以用
/x-@#S+l }'JKR(p*n0　　SQL>show parameter event;ITPUB个人空间`eeg'V+F
　　来查看
A:`"k/i#})I:q0　　b． Michael R.Ault给的SQL
,a^4E,NH P9I0　　serveroutput on size 1000000
g*L Zyl0　　declare
N j]gVF/@0　　event_level number;ITPUB个人空间{ ^;p3W(X5t4g
　　beginITPUB个人空间$`Wa^'U i)xqg
　　for i in 10000..10999 loop
#nc$D1[X~0　　sys.dbms_system.read_ev(i,event_level);ITPUB个人空间SB&S"t#n8tL;X}
　　if (event_level > 0) then
HE@q5\cG@0　　dbms_output.put_line('Event '||to_char(i)||' set at level '||ITPUB个人空间 A [/y/WF/c0G.o
　　to_char(event_level));
8ra#{1b;X0　　end if;ITPUB个人空间"z\a*z-l2|*M ]J
　　end loop;
7q2y,_)]'gGhA0　　end;ITPUB个人空间%m5|)]Z,| W/L6V+H^
　　/
:|4N~4wT(@)yQMcWo'J0　　
P^!l1qa#x0　　2、在oracle9i中使用spfile的那种如何设置诊断事件呢？
+{z k@'}CpR0　　回答：
9ni"j2s'lGp^0　　简单，Alter system命令就可以完成
It-ZtW~8S^0　　alter system set event='10046 trace name context forever, level 12' scope=spfile;ITPUB个人空间}9g\vMiYM|
　　重启一下就生效了。ITPUB个人空间'J9b(_4go7D!M
　　
&F7~]{!F0　　3、坏了，我的9i设置完诊断事件，起不来了，报ORA-02194错怎么办?ITPUB个人空间;\%E#JN1}M$t'b~i9b
　　回答：ITPUB个人空间!O]$[o#x2{\%B*O*U(N
　　那你一定是在使用Alter system时把某一项写错了，比如把context写成了conetxt了，可以做如下的解决：
3lIJ+w%_-}/x+^0　　a．由spfile生成pfile
d]i:{ O|t6x$Ayy0　　SQL>create pfile from spfile; ITPUB个人空间 {z_d {8G ?
　　File created.
6L8{:VYr/p] N0　　ITPUB个人空间vN)Bu*H:z5I
　　b．编辑pfile以修正错误 ITPUB个人空间i w?!ZG
　　Change... *.event='10046 trace name conetxt forever, level 12' ITPUB个人空间#]R6tJ@2C|9U
　　-to- *.event='10046 trace name context forever, level 12'
~_&T7T_(m?LV0　　c．用pfile启动
~z^*k;i&P2q8h0　　SQL>startup pfile=/.....
9zL2B s&Fn}gJ$`/v0　　d.重新生成 SPFILE. ITPUB个人空间!N+PDq }?
　　SQL>create spfile from pfile; ITPUB个人空间teZAm&kQe&q:e
　　File created.

X*G oo$~O:w'l0常用的Event Reference
aTfu4h7i:@M#b/k[;x0Event 10013 - Monitor Transaction Recovery
sLd1~T:z0This event can be used to trace transaction recovery during startup

For example

ALTER SESSION SET EVENTSITPUB个人空间.|1Y@S6?;]+O
'10013 trace name context forever, level 1';

Event 10015 - Dump Undo Segment Headers
g sxFYl[)k0This event can be used to dump undo segment headers before and after transaction recovery

For example

ALTER SESSION SET EVENTS
\WK2t's9A$G0 '10015 trace name context forever, level 1';

Event 10032 - Dump Sort StatisticsITPUB个人空间-e8mlz2wjq2Q!_
This event can be used to dump sort statistics. Level 10 is the most detailed

For example

ALTER SESSION SET EVENTS
i3]V7Z"hn0 '10032 trace name context forever, level 10';

Event 10033 - Dump Sort Intermediate Run Statistics
,LA` m:RD7L0This event can be used to dump sort intermediate run statistics. Level 10 is the most detailed

For example

ALTER SESSION SET EVENTS
2Wf]}t-WS0 '10033 trace name context forever, level 10';

Event 10045 - Trace Free List Management OperationsITPUB个人空间1oW*MOn7]t8PJ2_
This event can be used to trace free list management operations

For example

ALTER SESSION SET EVENTS
&|;s&f3cP lo~9l0 '10045 trace name context forever, level 1';

Event 10046 - Enable SQL Statement TraceITPUB个人空间 ]^"k8VJ,_#e~
This event can be used to dump SQL statements executed by a session with execution plans and statistics. Bind variable and wait statistics can optionally be included. Level 12 is the most detailed.

For example

ALTER SESSION SET EVENTS
5V+Q(d^v0 '10046 trace name context forever, level 12';

Levels are

Level Action
[Fl,A&Ks#tF01 Print SQL statements, execution plans and execution statistics
BH7KZ2Pa$dU q04 As level 1 plus bind variables ITPUB个人空间(nRhP CXa
8 As level 1 plus wait statistics ITPUB个人空间2I/^M[,Zmn-dN
12 As level 1 plus bind variables and wait statistics
sjELkk-v0

ITPUB个人空间!sz3sD#UAb8nG
Event 10053 - Dump Optimizer DecisionsITPUB个人空间m9rAdz$srOu.[
This event can be used to dump the decisions made by the optimizer when parsing a statement. Level 1 is the most detailed

For example

ALTER SESSION SET EVENTS
1h+CeC!Uc@'Me0 '10053 trace name context forever, level 1';

Levels are

Level Action
V3Tp5a @&v o/{X01 Print statistics and computations
!fi7DJuI:N(pL g02 Print computations only ITPUB个人空间 G E`E2?+a|9{Q

DX&HyM$h5bM0Event 10060 - Dump Predicates
z-zYiL4Ay2p/AY0This event can be used to force the optimizer to dump predicates to a table It is available in Oracle 7.1.3 and above, and was still working in Oracle 9.2.

This event requires the following table to be created in the schema of the user parsing the statement

CREATE TABLE kkoipt_table
Us3G4|2JmF0 (
0XH/uMf0 c1 INTEGER,ITPUB个人空间/k Z(Ng.CY
c2 VARCHAR2(80)ITPUB个人空间3e!S o R2U"E
);

To enable this event use

ALTER SESSION SET EVENTS
#IG*u {*hu4t$L0 '10060 trace name context forever, level 1';

This example uses the following object

CREATE TABLE t1 (c01 NUMBER, c02 NUMBER);

With event 10060 set to level 1, when the following statement is executed for the first time

SELECT c01 FROM t1 WHERE c02 = 0;

It is parsed and the results written to kkoipt_table

The results can be selected using the statement

SELECT c1,c2 FROM kkoipt_table ORDER BY c1;

C1 C2
e:fTWd/j EJa01 Table: ITPUB个人空间t o#Bs7c7~[&ga
2 T1 ITPUB个人空间U s6Y7u`2X/Au
3 frofand ITPUB个人空间h0u\l*a;Q7M%z{
4 "T1"."C02"=0 ITPUB个人空间}gDuK

f#F"l!\cA(g9B0The following table summarises the various operations that can be reported by this event

Operation Description ITPUB个人空间1IQ`:T}z,Y{Y
fptconst Folding constants
5qK}I$Rlm9Unv0fptrnum Remove ROWNUM predicates
-X:a)ikA][0fptwhr Remove all WHERE predicates except remaining ROWNUM predicates
*KWk;ri M'G(`jn0frofkks (rowid lookup) ROWID lookup ITPUB个人空间y}a.T&x+RD
frofkks[i] (and-equal lookup) start key
};N;yK7mOo;w0frofkke[i] (and-equal lookup) end key
:^z q;C:G9p2|jn0froiand index-only predicate
)z ImAF#d1E}0frofkksm[i] (sort-merge) sort-merge key ITPUB个人空间*gfql Y`X[
frosand (sort-merge) sort-merge predicates
H!O8m#IGQ-sN9{s0frojand (sort-merge) join predicates
S1B&qw]5D*\3S(j0frofkks[i] (index start key) index start key
*Mg2YG*`0frofkke[i] (index stop key) index end key
"{&`b0vK0q+V~IC0frofand (hash part) table predicate (hash) ITPUB个人空间v {1V6BwKLM-_
froiand (index only filter) index only predicate ITPUB个人空间q(jKN(R;DnW
frofand table predicate ITPUB个人空间%nf4nT6P {K `
froutand outer join predicates
p DW*H3rJ#en0

$H"lNb9V0Event 10065 - Restrict Library Cache Dump Output for State Object DumpsITPUB个人空间6Om*~6n9F E&uO
The amount of library cache dump output for state object dumps can be limited using event 10065

ALTER SESSION SET EVENTS '10065 trace name context forever, level level';

where level is one of the following

Level Description ITPUB个人空间:`z4{$?n#k{
1 Address of library object only ITPUB个人空间@*v3V/Q Ac5A
2 As level 1 plus library object lock details
YT4NAYt'Hz?1@03 As level 2 plus library object handle and library object ITPUB个人空间4oC`8kr9P

ITPUB个人空间^#u:cLc_g3~ A'\
Level 3 is the default

Event 10079 - Dump SQL*Net Statistics
xW%pBE0This event can be used to SQL*Net statistics. Level 2 is the most detailed

For example

ALTER SESSION SET EVENTSITPUB个人空间Jc-n/Hb
'10079 trace name context forever, level 2';

Event 10081 - Trace High Water Mark Changes
-ZR5^P] C B8}4}0This event can be used to trace high water mark changes

For example

ALTER SESSION SET EVENTS
-^7zz)ZV[-Oc@q0 '10081 trace name context forever, level 1';

Event 10104 - Dump Hash Join StatisticsITPUB个人空间:H u:[(h4h"me!aa
This event can be used to hash join statistics. Level 10 is the most detailed

For example

ALTER SESSION SET EVENTS
R2@Iu#U.hY/X0 '10104 trace name context forever, level 10';

Event 10128 - Dump Partition Pruning InformationITPUB个人空间Ek+G+jUhR{
This event can be used to partition pruning information

For example

ALTER SESSION SET EVENTSITPUB个人空间lQPq]~[*L
'10128 trace name context forever, level level';

Levels are

Level Action
a!P[^D(Lv00x0001 Dump pruning descriptor for each partitioned object ITPUB个人空间Ed*r4eEsS
0x0002 Dump partition iterators ITPUB个人空间-\q;C.^+IN(Zj;A
0x0004 Dump optimizer decisions about partition-wise joins
hy o1jc8s J00x0008 Dump ROWID range scan pruning information
T9H.]+|!\]w0T5e0

v}dya+W$w0There are further levels (up to 4096?)

In Oracle 9.0.1 and above, a table must be created before level 2 of this event can be set.

The table definition is as follows

CREATE TABLE kkpap_pruningITPUB个人空间;~9M.U.H|9Bw8p G
(
%C%W"HA/K0 partition_count NUMBER,
W5acA6Z0 iterator VARCHAR2(32),ITPUB个人空间 b,gn^^(\2A3T
partition_level VARCHAR2(32),ITPUB个人空间D?K c,Et
order_pt VARCHAR2(12),ITPUB个人空间#| SY%t4w X8H}
call_time VARCHAR2(12),
8{O'}.^:et+|QQ0 part# NUMBER,ITPUB个人空间|(sqLB.A|s
subp# NUMBER,
9m-o+Zdc.z0 abs# NUMBER
#^9V&ko4T(Xl0 );

Event 10200 - Dump Consistent Reads
Uc+`$g\d0This event can be used to dump consistent reads

ALTER SESSION SET EVENTS
(MA1}XGc8TP*Kv0 '10200 trace name context forever, level 1';

Event 10201 - Dump Consistent Read Undo Application
/K s`K9y.q1n7L0This event can be used to dump consistent read undo application

ALTER SESSION SET EVENTSITPUB个人空间g([#B4Z,@,a'n*r
'10201 trace name context forever, level 1';

Event 10220 - Dump Changes to Undo HeaderITPUB个人空间TD4L'hRt
This event can be used to dump changes to the undo header (transaction table)

ALTER SESSION SET EVENTS
7j d~d9b LL0 '10220 trace name context forever, level 1';

Event 10221 - Dump Undo Changes
}:z;x(P,vAKu3B0This event can be used to dump undo changes applied. Level 7 is the most detailed

ALTER SESSION SET EVENTSITPUB个人空间]_4`'[(w$t"?
'10221 trace name context forever, level 7';

Event 10224 - Dump Index Block Splits / Deletes
tOn7v?0This event can be used to dump index block splits and deletes detailed

ALTER SESSION SET EVENTS
UZMKefJPf0 '10224 trace name context forever, level 1';

Event 10225 - Dump Changes to Dictionary Managed ExtentsITPUB个人空间 `Qn`)D'ma5V
This event can be used to dump changes to dictionary-managed extents made in the row cache

ALTER SESSION SET EVENTS
N? m)c|Pt im0 '10225 trace name context forever, level 1';

Event 10241 - Dump Remote SQL ExecutionITPUB个人空间1_wr%qK%I;_7mi
This event can be used to dump remotely executed SQL statements

ALTER SESSION SET EVENTSITPUB个人空间9bxfWv"zo
'10241 trace name context forever, level 1';

Event 10246 - Trace PMON ProcessITPUB个人空间-ZjWmWMQ7w)K
This event can be used to trace the actions of the PMON background process

This event can only be enabled in the init.ora file using

event = "10246 trace name context forever, level 1"

The ALTER SYSTEM command does not appear to work for this event

There only appears to be one level for this event (levels 5 and 10 appear to generate the same output as level 1)

Event 10248 - Trace Dispatcher Processes
nNN7M~0This event can be used to trace dispatcher processes

This event can be enabled in the init.ora file using

event = "10248 trace name context forever, level 10"

In Oracle 9.2 (Windows 2000) the trace is written to a file in the udump directory with a name in the format

ServiceName_dDispatcherNumber_ThreadNumber.trc

e.g.

JD92001_d000_1234.trc

Valid levels are 1 to 10 (Metalink Note)

Event 10249 - Trace Shared Server (MTS) Processes
^)M6O7J5x8p3~9g?0This event can be used to trace shared server (MTS) processes

This event can be enabled in the init.ora file using

event = "10249 trace name context forever, level 10"

In Oracle 9.2 (Windows 2000) the trace is written to a file in the udump directory with a name in the format

ServiceName_sSharedServerNumber_ThreadNumber.trc

e.g.

JD92001_s000_5678.trc

Valid levels are 1 to 10 (Metalink Note)

Event 10270 - Debug Shared CursorsITPUB个人空间m2hB%G1Ht_A
This event can be used to enable debugging code in shared cursor management modules

event = "10270 trace name context forever, level 10"

Event 10299 - Debug PrefetchingITPUB个人空间9K9Ku(Kb5k
This event can be used to enable debugging code for table and index block prefetching. It also enables dumping of trace by the CKPT process.

event = "10299 trace name context forever, level 1"

Event 10357 - Debug Direct Path
.{9Q s%@,oK}'v!K0This event can be used to enable debugging code for direct path

ALTER SESSION SET EVENTS
XJ[(R$^0 '10357 trace name context forever, level 1';

Event 10390 - Dump Parallel Execution Slave StatisticsITPUB个人空间'GAc1@f1g
This event can be used to dump parallel slave statistics

ALTER SESSION SET EVENTS
9B)a_[R2IIKu.T0 '10390 trace name context forever, level level';

Levels are (from messages)

Level Action
9Pga2z'kg!xFt v6}00x0001 Slave-side execution messages ITPUB个人空间qfc*poLZ
0x0002 Coordinator-side execution messages
0x0004 Slave context state changes ITPUB个人空间K f9n&hd-NuO;r
0x0008 Slave ROWID range bind variables and xty ITPUB个人空间*]2b$S9x] gc:}
0x0010 Slave fetched rows as enqueued to TQ ITPUB个人空间,@XD$b-]7zm
0x0020 Coordinator wait reply handling
0x0040 Coordinator wait message buffering
H.GZ$JZ00x0080 Slave dump timing
%~6U!~~R5g00x0100 Coordinator dump timing ITPUB个人空间S0g0MoR
0x0200 Slave dump allocation file number
+y5P:k S8zDL00x0400 Terse format for debug dumps ITPUB个人空间*YLV!Q~bk
0x0800 Trace CRI random sampling
w`Z'q#[.|9T8P#hM00x1000 Trace signals
L%o9e g:{J4^c00x2000 Trace parallel execution granule operations
M(ufH"YO;fz00x4000 Force compilation by slave 0
r%I T!aeXdr0

ITPUB个人空间ARbBct h"ECm
Event 10391 - Dump Parallel Execution Granule AllocationITPUB个人空间E(Y]#]ZK
This event can be used to dump parallel granule allocation / assignment statistics

ALTER SESSION SET EVENTS
m4u$n#R8Z0_}r%c*|u-vk9g0 '10391 trace name context forever, level level';

Levels are (from messages)

Level Action ITPUB个人空间 `1Q'`:u)C1A} ]#T
0x0001 Dump summary of each object scanned in parallel
[e rK4}*\00x0002 Full dump of each object except extent map ITPUB个人空间$Z6r;Q&Lr1P9\&C&FJ
0x0004 Full dump of each object including extent map ITPUB个人空间&Bge$l9e
0x0010 Dump summary of each granule generators ITPUB个人空间+YEO7k*J0w
0x0020 Full dump of granule generators except granule instances ITPUB个人空间5UoKX+P
0x0040 Full dump of granule generators including granule instances ITPUB个人空间@N(rln qr
0x0080 Dump system information
)h G [+p1^!Rf&i00x0100 Dump reference object for the query
N5\@3Mm q00x0200 Gives timing in kxfralo
_*ANIG7viZw00x0400 Trace affinity module
K"v(Pjx,Z PB00x0800 Trace granule allocation during query execution ITPUB个人空间f5tw%V8o AP X*o&w
0x1000 Trace object flush ITPUB个人空间O'])w'_m$Fyi
0x2000 Unknown ITPUB个人空间2_nz }L5c.s

ITPUB个人空间-@ D d UJzQ$hJ&r
Event 10393 - Dump Parallel Execution StatisticsITPUB个人空间$\0JIx5yu O3s3_#K
This event can be used to dump kxfp statistics after each parallel query

ALTER SESSION SET EVENTS
??-\-v0]]^0 '10393 trace name context forever, level 1';

Note that in Oracle 9.2 for parallel execution trace is written to files with names of the format

ServiceName_pServerNumber_ThreadNumber.trc

This is an example of the output for this event. The output has been modified for readability

kxfpdst ITPUB个人空间:D&hZ"]Szh
dumping statistics
i0Xi!d,^$di8`0 --------------------------- ITPUB个人空间TE\4qJ l$rsm
Query Sessions 1 ITPUB个人空间B:HdS.OF(_
Total Messages Sent 0
:E7f3f2F!\yst-Q s0 Data Messages Sent 948
Hp/~.c2F\*U0 Stream Messages Sent 917
g Ja!I;jYm0 Dialog Messages Sent 26
/H%w0i-n4}G:T0 Null Messages Sent 0
1hM'M1Bwzy9Q0 Fast Shared Memory Streams 669
]2E}^*G1lu$w0 Fast Distributed Stream 0
I"@Wu%mX6CGx0 Stream Mode Credit Ping 0
&g K)c.t|6ccLx0 Unknown Credit Pings 0
N%J:w EJC#Nk0 Single Credit Pings 252 ITPUB个人空间h$@@\g-D ?+r
Double Credit Pings 0
q&b[^}0l\9W4XK0 Triple Credit Pings 0
)l7W,d'xs]NK0 Multiple Credit Pings 0 ITPUB个人空间5AT4?V?gw/hs
Total Messages Dequeued 0 ITPUB个人空间.oL8NS'f3p/M
Data Messages Dequeued 31
8r7Z+['S8Pj }3t b0 Null Messages Dequeued 0 ITPUB个人空间aI d9Z?n1x
Immediate Dequeues 1 ITPUB个人空间9{2^r@A*B }
Posted Dequeues 31 ITPUB个人空间m-afXx2A
Timed-out Dequeues 0
G Vn!{z0C0 Implicit Dequeues 255 ITPUB个人空间"e*U5sDD*DB
Total Dequeue Waits 85
*f&R4C?p L!z0 Total Dequeue Timeouts 44
Q$vW.bGA0 Dequeues for Credit (geb) 77
J#UT%Q-GQ0D0 Dequeues for Credit (free) 0
H%bNG }0 Dequeues for Credit (enq) 39

Event 10500 - Trace SMON ProcessITPUB个人空间7u'K%T^`t F%t5T s
This event can be used to trace the actions of the SMON background process

This event can be enabled in the init.ora file using

event = "10500 trace name context forever, level 1"

Event 10608 - Trace Bitmap Index CreationITPUB个人空间S-[*V8ra)G+O
This event traces bitmap index creation.

ALTER SESSION SET EVENTSITPUB个人空间g1o2v:_{ENH
'10608 trace name context forever, level 10';

Event 10704 - Trace Enqueues
_ Q BL5qIFI\%e J0This event dumps information about which enqueues are being obtained

When enabled it prints out arguments to calls to ksqcmi and ksqlrl and the return values

ALTER SESSION SET EVENTSITPUB个人空间*rJr#O C
'10704 trace name context forever, level 1';

Event 10706 - Trace Global Enqueue ManipulationITPUB个人空间$f:G*AfR*o)VA:k
This event allows RAC global enqueue manipulation to be trace

ALTER SESSION SET EVENTSITPUB个人空间jk$w"E-Qdow;bq
'10706 trace name context forever, level 1';

The amount of output can be limited using the unsupported parameter '_ksi_trace'.

This parameter specifies the lock types that should be included e.g. TM, TX etc. They are specified as a string e.g. 'TMTX'

The parameter '_ksi_trace' can only be set in the initialisation file.

Event 10708 - Trace RAC Buffer CacheITPUB个人空间 Qw;vHW,t2O
This event allows RAC buffer cache activity to be traced

ALTER SESSION SET EVENTSITPUB个人空间n2uf_]
'10708 trace name context forever, level 10';

This diagnostic applies only to RAC clusters (not single-instance)

Event 10710 - Trace Bitmap Index Access
:XJg+Z8e~+c0This event traces bitmap index access. It displays the start ROWID and end ROWID of each bitmap

ALTER SESSION SET EVENTS
E#E:b(B4y0uT%g3k0 '10710 trace name context forever, level 1';

Event 10711 - Trace Bitmap Index Merge Operation
8h;wGE9`0This event traces the bitmap index merge operation.

ALTER SESSION SET EVENTSITPUB个人空间Uy^4}0@;{(KQ)M7a9j
'10711 trace name context forever, level 1';

Event 10712 - Trace Bitmap Index OR Operation
.q8px6^.i:Tio2G6eIW0This event traces the bitmap index OR operation.

ALTER SESSION SET EVENTS
2U T;p^ns}le0 '10712 trace name context forever, level 1';

Event 10713 - Trace Bitmap Index AND Operation
1Zm#?M@)K(w0This event traces the bitmap index AND operation.

ALTER SESSION SET EVENTSITPUB个人空间Y2uX2x6i!j d]pM
'10713 trace name context forever, level 1';

Event 10714 - Trace Bitmap Index MINUS OperationITPUB个人空间,w;~X.]&s-pv
This event traces the bitmap index MINUS operation.

ALTER SESSION SET EVENTSITPUB个人空间+{e [D1^AL4F.?
'10714 trace name context forever, level 1';

Event 10715 - Trace Bitmap Index Conversion to ROWIDs Operation
%\ ]7W6oQ)NI$^0This event traces the bitmap index conversion to ROWIDs operation

ALTER SESSION SET EVENTS
B2s3^O|n8`G Qm_0 '10715 trace name context forever, level 1';

Event 10716 - Trace Bitmap Index Compress/Decompress
(`{V}\B#`'w5go0This event traces the bitmap index compress/decompress

ALTER SESSION SET EVENTS
;V,{8VW(~W!?0 '10716 trace name context forever, level 1';

Event 10717 - Trace Bitmap Index Compaction
7QfE~kH,g'p0This event traces the bitmap index compaction.

ALTER SESSION SET EVENTSITPUB个人空间8m&\3KQ|%f nzRZ
'10717 trace name context forever, level 1';

Event 10719 - Trace Bitmap Index DML
ES,H`7N!k0This event traces the bitmap index DML.

ALTER SESSION SET EVENTS
kNcyY;l#c&C2I0 '10719 trace name context forever, level 1';

Event 10730 - Trace Fine Grained Access PredicatesITPUB个人空间&e+xl`g2H1?!{-K0w
This event traces find grained access (RLS) predicates

ALTER SESSION SET EVENTSITPUB个人空间F6a)[2FIj.z
'10730 trace name context forever, level 1';

Event 10731 - Trace CURSOR StatementsITPUB个人空间 M1I:W1^%R:e#a8|Na)?
This event traces CURSOR statements

ALTER SESSION SET EVENTS
6?+w#q'V/D_0 '10731 trace name context forever, level level';

Levels are

Level Action ITPUB个人空间lb:dret0l/S J
1 Print parent query and subquery
,m8wRS v!{ w3n02 Print subquery only
I$A+F&~c0

ITPUB个人空间A5e4}Em}O
Event 10928 - Trace PL/SQL Execution
r2eDA?R1b.yt0This event traces PL/SQL execution

ALTER SESSION SET EVENTS
EF/x:VUT e+BC0 '10928 trace name context forever, level 1';

Event 10938 - Dump PL/SQL Execution StatisticsITPUB个人空间)S#O5s!aDC
This event dumps PL/SQL execution statistics.

ALTER SESSION SET EVENTSITPUB个人空间!~?m+e!rHZ V
'10938 trace name context forever, level 1';

This event currently generates the following output

--NOTICE --------------------------------------- ITPUB个人空间l:u!P,`l1P-R){0U
--PL/SQL TRACE INFORMATION IS NOW IN THE DATABASEITPUB个人空间Nt'v"} S5G#OwC
-- To create the trace tables, use the script --ITPUB个人空间 N] ~"Yf U^;w
-- rdbms/admin/tracetab.sql under ORACLE_HOME --