【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

);