【Oracle】Oracle常用EVENT之一

Event 10013 - Monitor Transaction Recovery

在Startup時跟蹤事務恢複

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

Event 10015 - Dump Undo Segment Headers-

在事務恢複後做Dump回退段頭資訊

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

Event 10032 - Dump Sort Statistics

Dump排序的統計資訊

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

Event 10033 - Dump Sort Intermediate Run Statistics

排序過程中，記憶體排序區和臨時表空間的互動情況

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

Event 10045 - Trace Free List Management Operations

FREELIST的管理操作

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

Event 10046 - Enable SQL Statement Trace

跟蹤SQL,有執行計劃,邦定變量和等待的統計資訊,level 12最詳細。

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

LEVEL定義如下：

1:SQL 語句，執行計劃和執行狀态

4:1的内容加上綁定變量資訊

8:1的資訊加上等待事件資訊

12:1＋4＋8

Event 10053 - Dump Optimizer Decisions

在分析SQL語句時,Dump出優化器所做的選擇,級别level 1最詳細

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

1：狀态和估算資訊

2：隻顯示估算資訊

Event 10060 - Dump Predicates

DUMP SQL語句中的斷語資訊。需要在需要DUMP的使用者下建立以下表

CREATE TABLE kkoipt_table

(c1 INTEGER,

c2 VARCHAR2(80));

斷語資訊會寫入該表

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

Event 10065 - Restrict Library Cache Dump Output for State Object Dumps

限制對象狀态DUMP的時候LIBRARY CACHE資訊的詳細程度

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

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

Event 10079 - Dump SQL*Net Statistics-

Dump SQL*NeT的統計資訊

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

Event 10081 - Trace High Water Mark Changes

HWM的改變

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

Event 10104 - Dump Hash Join Statistics

HASH JOIN的統計資訊

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

Event 10128 - Dump Partition Pruning Information

分區表調整資訊

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

Level取值：

1Dump 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

在9.0.1或者後面的版本,在level 2後還需要建立如下的表:

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

);