ORACLE診斷事件的總結

Oracle為RDBMS提供了多種的診斷工具，診斷事件(Event)是其中一種常用、好用的方法，它使DBA可以方便的轉儲資料庫各種結構及跟蹤特定事件的發生.[@more@]一、Event的通常格式及分類
　　
　　1、 通常格式如下：
　　EVENT=""
　　
　　2、 Event分類
　　診斷事件大體上可以分為四類：
　　a． 轉儲類事件：它們主要用於轉儲Oracle的一些結構，例如轉儲一下控制檔案、資料檔案頭等內容。
　　b． 捕捉類事件：它們用於捕捉一些Error事件的發生，例如捕捉一下ORA-04031發生時一些Rdbms資訊，以判斷是Bug還是其它原因引起的這方面的問題。
　　c． 改變執行途徑類事件：它們用於改主一些Oracle內部程式碼的執行途徑，例如設定10269將會使Smon程式不去合併那些Free的空間。
　　d． 跟蹤類事件：這們用於獲取一些跟蹤資訊以用於Sql調優等方面，最典型的便是10046了，將會對Sql進行跟蹤。
　　3、 說明:
　　a． 如果immediate放在第一個說明是無條件事件，即命令發出即轉儲到跟蹤檔案。
　　b． trace name位於第二、三項，除它們外的其它限定詞是供Oracle內部開發組用的。
　　c． level通常位於1-10之間(10046有時用到12)，10意味著轉儲事件所有的資訊。例如當轉儲控制檔案時，level1表示轉儲控制檔案頭，而level 10表明轉儲控制檔案全部內容。
　　d． 轉儲所生成的trace檔案在user_dump_dest初始化引數指定的位置。

　　二、說一說設定的問題了
　　
　　可以在init.ora中設定所需的事件，這將對所有會話期開啟的會話進行跟蹤，也可以用alter session set event 等方法設定事件跟蹤，這將開啟正在進行會話的事件跟蹤。
　　
　　1、 在init.ora中設定跟蹤事件的方法
　　a． 語法
　　EVENT=”event 語法|,level n|：event 語法|,level n|…”
　　b． 舉例
　　event=”10231 trace name context forever,level 10’
　　c． 可以這樣設定多個事件：
　　EVENT="
　　10231 trace name context forever, level 10:
　　10232 trace name context forever, level 10"
　　
　　2、 透過Alter session/system set events這種方法
　　舉個例子大家就明白了
　　Example:
　　Alter session set events ‘immediate trace name controlf level 10’;
　　Alter session set events ‘immediate trace name blockdump level 112511416’; (*)
　　在oracle8x及之上的版本也有這樣的語句：
　　Alter system dump datafile 13 block 15;實現的功能與(*)是類似的。
　　
　　3、 使用DBMS_SYSTEM.SET_EV的方法
　　a． 過和定義如下
　　DBMS_SYSTEM.SET_EV(
　　SI Binary_integer,
　　SE Binary_integer,
　　EV Binary_integer,
　　LE Binary_integer,
　　NM Binary_integer);
　　
　　SI: 即v$session中的sid
　　SE:即v$session中的serial#
　　EV:要設定的事件
　　LE:要設定事件的級別
　　NM:名稱
　　b． 舉個例子，以10046為例
　　SQL> EXECUTE SYS.DBMS_SYSTEM.SET_EV(sid,serial#,10046,12,'')；
　　
　　4、 使用Oradebug來設定診斷事件的方法
　　同樣舉個例子大家就明白了：
　　a． 找到spid
　　SQL>select username, sid, serial#, paddr from v$session where username='qiuyb';
　　
　　USERNAME SID SERIAL# PADDR
　　--------------------------------------------------------
　　HRB3 265 910 C000000084435AD8
　　
　　SQL>SELECT ADDR,PID,SPID FROM V$PROCESS WHERE ADDR= C000000084435AD8';
　　ADDR PID SPID
　　------------------------------------------
　　C000000084435AD8 91 4835
　　
　　b． 設定事件，以10046為例
　　sqlplus /nolog
　　SQL>connect / as sysdba;
　　SQL>oradebug setospid 4835
　　SQL>oradebug unlimit　　　--不限制轉儲檔案的大小
　　SQL> oradebug event 10046 trace name context forever,level 12 --設定事件進行sql跟蹤
　　
　　SQL> oradebug event 10046 trace name context off --關閉跟蹤
　　
　　注意不要用oradug去跟蹤oracle的smon,pmon等幾個程式，操作不當可能會殺掉這幾個後臺進和引起宕庫。
　　
　　三、你可能的問題
　　
　　1、 我如何知道在系統中設定了哪些event?
　　回答：
　　a． 如果你的事件是在init.ora中設定的可以用
　　SQL>show parameter event;
　　來檢視
　　b． Michael R.Ault給的SQL
　　serveroutput on size 1000000
　　declare
　　event_level number;
　　begin
　　for i in 10000..10999 loop
　　sys.dbms_system.read_ev(i,event_level);
　　if (event_level > 0) then
　　dbms_output.put_line('Event '||to_char(i)||' set at level '||
　　to_char(event_level));
　　end if;
　　end loop;
　　end;
　　/
　　
　　2、 在oracle9i中使用spfile的那種如何設定診斷事件呢？
　　回答：
　　簡單，Alter system命令就可以完成
　　alter system set event='10046 trace name context forever, level 12' scope=spfile;
　　重啟一下就生效了。
　　
　　3、 壞了，我的9i設定完診斷事件，起不來了，報ORA-02194錯怎麼辦?
　　回答：
　　那你一定是在使用Alter system時把某一項寫錯了，比如把context寫成了conetxt了，可以做如下的解決：
　　a．由spfile生成pfile
　　SQL>create pfile from spfile;
　　File created.
　　
　　b．編輯pfile以修正錯誤
　　Change... *.event='10046 trace name conetxt forever, level 12'
　　-to- *.event='10046 trace name context forever, level 12'
　　c．用pfile啟動
　　SQL>startup pfile=/.....
　　d.重新生成 SPFILE.
　　SQL>create spfile from pfile;
　　File created.

常用的Event Reference
Event 10013 - Monitor Transaction Recovery
This event can be used to trace transaction recovery during startup

For example

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

Event 10015 - Dump Undo Segment Headers
This event can be used to dump undo segment headers before and after transaction recovery

For example

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

Event 10032 - Dump Sort Statistics
This event can be used to dump sort statistics. Level 10 is the most detailed

For example

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

Event 10033 - Dump Sort Intermediate Run Statistics
This event can be used to dump sort intermediate run statistics. Level 10 is the most detailed

For example

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

Event 10045 - Trace Free List Management Operations
This event can be used to trace free list management operations

For example

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

Event 10046 - Enable SQL Statement Trace
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
'10046 trace name context forever, level 12';

Levels are

Level Action
1 Print SQL statements, execution plans and execution statistics
4 As level 1 plus bind variables
8 As level 1 plus wait statistics
12 As level 1 plus bind variables and wait statistics

Event 10053 - Dump Optimizer Decisions
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
'10053 trace name context forever, level 1';

Levels are

Level Action
1 Print statistics and computations
2 Print computations only

Event 10060 - Dump Predicates
This 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
(
c1 INTEGER,
c2 VARCHAR2(80)
);

To enable this event use

ALTER SESSION SET EVENTS
'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
1 Table:
2 T1
3 frofand
4 "T1"."C02"=0

The following table summarises the various operations that can be reported by this event

Operation Description
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
frofkke[i] (and-equal lookup) end key
froiand index-only predicate
frofkksm[i] (sort-merge) sort-merge 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

Event 10065 - Restrict Library Cache Dump Output for State Object Dumps
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
1 Address of library object only
2 As level 1 plus library object lock details
3 As level 2 plus library object handle and library object

Level 3 is the default

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

For example

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

Event 10081 - Trace High Water Mark Changes
This event can be used to trace high water mark changes

For example

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

Event 10104 - Dump Hash Join Statistics
This event can be used to hash join statistics. Level 10 is the most detailed

For example

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

Event 10128 - Dump Partition Pruning Information
This event can be used to partition pruning information

For example

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

Levels are

Level Action
0x0001 Dump pruning descriptor for each partitioned object
0x0002 Dump partition iterators
0x0004 Dump optimizer decisions about partition-wise joins
0x0008 Dump ROWID range scan pruning information

There 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_pruning
(
partition_count NUMBER,
iterator VARCHAR2(32),
partition_level VARCHAR2(32),
order_pt VARCHAR2(12),
call_time VARCHAR2(12),
part# NUMBER,
subp# NUMBER,
abs# NUMBER
);

Event 10200 - Dump Consistent Reads
This event can be used to dump consistent reads

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

Event 10201 - Dump Consistent Read Undo Application
This event can be used to dump consistent read undo application

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

Event 10220 - Dump Changes to Undo Header
This event can be used to dump changes to the undo header (transaction table)

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

Event 10221 - Dump Undo Changes
This event can be used to dump undo changes applied. Level 7 is the most detailed

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

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

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

Event 10225 - Dump Changes to Dictionary Managed Extents
This event can be used to dump changes to dictionary-managed extents made in the row cache

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

Event 10241 - Dump Remote SQL Execution
This event can be used to dump remotely executed SQL statements

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

Event 10246 - Trace PMON Process
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
This 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
This 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 Cursors
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 Prefetching
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
This event can be used to enable debugging code for direct path

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

Event 10390 - Dump Parallel Execution Slave Statistics
This event can be used to dump parallel slave statistics

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

Levels are (from messages)

Level Action
0x0001 Slave-side execution messages
0x0002 Coordinator-side execution messages
0x0004 Slave context state changes
0x0008 Slave ROWID range bind variables and xty
0x0010 Slave fetched rows as enqueued to TQ
0x0020 Coordinator wait reply handling
0x0040 Coordinator wait message buffering
0x0080 Slave dump timing
0x0100 Coordinator dump timing
0x0200 Slave dump allocation file number
0x0400 Terse format for debug dumps
0x0800 Trace CRI random sampling
0x1000 Trace signals
0x2000 Trace parallel execution granule operations
0x4000 Force compilation by slave 0

Event 10391 - Dump Parallel Execution Granule Allocation
This event can be used to dump parallel granule allocation / assignment statistics

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

Levels are (from messages)

Level Action
0x0001 Dump summary of each object scanned in parallel
0x0002 Full dump of each object except extent map
0x0004 Full dump of each object including extent map
0x0010 Dump summary of each granule generators
0x0020 Full dump of granule generators except granule instances
0x0040 Full dump of granule generators including granule instances
0x0080 Dump system information
0x0100 Dump reference object for the query
0x0200 Gives timing in kxfralo
0x0400 Trace affinity module
0x0800 Trace granule allocation during query execution
0x1000 Trace object flush
0x2000 Unknown

Event 10393 - Dump Parallel Execution Statistics
This event can be used to dump kxfp statistics after each parallel query

ALTER SESSION SET EVENTS
'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
dumping statistics
---------------------------
Query Sessions 1
Total Messages Sent 0
Data Messages Sent 948
Stream Messages Sent 917
Dialog Messages Sent 26
Null Messages Sent 0
Fast Shared Memory Streams 669
Fast Distributed Stream 0
Stream Mode Credit Ping 0
Unknown Credit Pings 0
Single Credit Pings 252
Double Credit Pings 0
Triple Credit Pings 0
Multiple Credit Pings 0
Total Messages Dequeued 0
Data Messages Dequeued 31
Null Messages Dequeued 0
Immediate Dequeues 1
Posted Dequeues 31
Timed-out Dequeues 0
Implicit Dequeues 255
Total Dequeue Waits 85
Total Dequeue Timeouts 44
Dequeues for Credit (geb) 77
Dequeues for Credit (free) 0
Dequeues for Credit (enq) 39

Event 10500 - Trace SMON Process
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 Creation
This event traces bitmap index creation.

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

Event 10704 - Trace Enqueues
This 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 EVENTS
'10704 trace name context forever, level 1';

Event 10706 - Trace Global Enqueue Manipulation
This event allows RAC global enqueue manipulation to be trace

ALTER SESSION SET EVENTS
'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 Cache
This event allows RAC buffer cache activity to be traced

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

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

Event 10710 - Trace Bitmap Index Access
This event traces bitmap index access. It displays the start ROWID and end ROWID of each bitmap

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

Event 10711 - Trace Bitmap Index Merge Operation
This event traces the bitmap index merge operation.

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

Event 10712 - Trace Bitmap Index OR Operation
This event traces the bitmap index OR operation.

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

Event 10713 - Trace Bitmap Index AND Operation
This event traces the bitmap index AND operation.

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

Event 10714 - Trace Bitmap Index MINUS Operation
This event traces the bitmap index MINUS operation.

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

Event 10715 - Trace Bitmap Index Conversion to ROWIDs Operation
This event traces the bitmap index conversion to ROWIDs operation

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

Event 10716 - Trace Bitmap Index Compress/Decompress
This event traces the bitmap index compress/decompress

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

Event 10717 - Trace Bitmap Index Compaction
This event traces the bitmap index compaction.

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

Event 10719 - Trace Bitmap Index DML
This event traces the bitmap index DML.

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

Event 10730 - Trace Fine Grained Access Predicates
This event traces find grained access (RLS) predicates

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

Event 10731 - Trace CURSOR Statements
This event traces CURSOR statements

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

Levels are

Level Action
1 Print parent query and subquery
2 Print subquery only

Event 10928 - Trace PL/SQL Execution
This event traces PL/SQL execution

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

Event 10938 - Dump PL/SQL Execution Statistics
This event dumps PL/SQL execution statistics.

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

This event currently generates the following output

--NOTICE ---------------------------------------
--PL/SQL TRACE INFORMATION IS NOW IN THE DATABASE
-- To create the trace tables, use the script --
-- rdbms/admin/tracetab.sql under ORACLE_HOME --