【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

);