Oracle常用诊断事件

事件说明例子

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′;

LEVEL定义如下：

1：状态和估算信息

2：只显示估算信息

Event 10060 Dump PredicatesDUMP 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取值：

1 Dump pruning descriptor for each partitioned object

0×0002 Dump partition iterators

0×0004 Dump optimizer decisions about partition-wise joins

0×0008 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

);

事件说明例子

Event 10200 Dump Consistent Reads

DUMP一致读的信息ALTER SESSION SET EVENTS ’10200 trace name context forever, level 1′;

Event 10201 Dump Consistent Read Undo Application

DUMP一致性读涉及UNDO信息的内容ALTER SESSION SET EVENTS ’10201 trace name context forever, level 1′;

Event 10220 Dump Changes to Undo Header

Dump出Undo头信息的改变ALTER SESSION SET EVENTS ’10220 trace name context forever, level 1′;

Event 10221 Dump Undo Changes

Dump Undo的改变ALTER SESSION SET EVENTS ’10221 trace name context forever, level 7′;

Event 10224 Dump Index Block Splits / Deletes

索引块的分裂和D删除信息ALTER SESSION SET EVENTS ’10224 trace name context forever, level 1′;

Event 10225 Dump Changes to Dictionary Managed Extents

DUMP字段管理的扩展变化ALTER SESSION SET EVENTS ’10225 trace name context forever, level 1′;Event 10231全表扫描时跳过坏块，在有坏块的情况下做数据拯救时很有用ALTER SYSTEM SET EVENTS ’10231 trace name context forever,level 10′;

Event 10241 Dump Remote SQL Execution

远程SQL语句的执行信息ALTER SESSION SET EVENTS ’10241 trace name context forever, level 1′;Event 10246 Trace PMON Process跟踪PMON进程只能修改参数，不能用ALTER SYSTEM

event = “10246 trace name context forever, level 1″

Event 10248 Trace Dispatcher Processes

跟踪DISPATCHER的工作情况event = “10248 trace name context forever, level 10″

Event 10249 Trace Shared Server (MTS) Processes-

跟踪共享服务器的工作情况event = “10249 trace name context forever, level 10″

Event 10270 Debug Shared Cursors

跟踪共享CURSORS的情况event = “10270 trace name context forever, level 10″

Event 10299 Debug Prefetching

跟踪表数据块和索引数据块的PREFETCHINGevent = “10299 trace name context forever, level 1″

Event 10357 Debug Direct Path

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

Event 10390 Dump Parallel Execution Slave Statistics

跟踪并行操作中的SLAVE的状态ALTER SESSION SET EVENTS ’10390 trace name context forever, level 1;

Event 10391-Dump Parallel Execution Granule Allocation

跟踪并行操作的粒度ALTER SESSION SET EVENTS ’10391 trace name context forever, level 2′;

Event 10393 Dump Parallel Execution Statistics

跟踪并行操作的状态（每个SLAVE单独列出状态）ALTER SESSION SET EVENTS ’10393 trace name context forever, level 1′;

Event 10500 Trace SMON Process

跟踪SMON进程event = “10500 trace name context forever, level 1″

Event 10608 Trace Bitmap Index Creation

跟踪BITMAP索引创建的详细过程ALTER SESSION SET EVENTS ’10608 trace name context forever, level 10′;

Event 10704 Trace Enqueues

跟踪锁的使用情况ALTER SESSION SET EVENTS ’10704 trace name context forever, level 1′;

Event 10706 Trace Global Enqueue Manipulation

跟踪全局锁的使用情况ALTER SESSION SET EVENTS ’10706 trace name context forever, level 1′;

Event 10708 Trace RAC Buffer Cache

跟踪RAC环境下的BUFFER CACHEALTER SESSION SET EVENTS ’10708 trace name context forever, level 10′;事件说明例子

Event 10710 Trace Bitmap Index Access

跟踪位图索引的访问情况ALTER SESSION SET EVENTS ’10710 trace name context forever, level 1′;

Event 10711 Trace Bitmap Index Merge Operation

跟踪位图索引合并操作ALTER SESSION SET EVENTS ’10711 trace name context forever, level 1′;

Event 10712 Trace Bitmap Index OR Operation

跟踪位图索引或操作情况ALTER SESSION SET EVENTS ’10712 trace name context forever, level 1′;

Event 10713 Trace Bitmap Index AND Operation

跟踪位图索引与操作ALTER SESSION SET EVENTS ’10713 trace name context forever, level 1′;

Event 10714 Trace Bitmap Index MINUS Operation

跟踪位图索引minus操作ALTER SESSION SET EVENTS ’10714 trace name context forever, level 1′;

Event 10715 Trace Bitmap Index Conversion to ROWIDs Operation

跟踪位图索引转换ROWID操作ALTER SESSION SET EVENTS ’10715 trace name context forever, level 1′;

Event 10716 Trace Bitmap Index Compress/Decompress

跟踪位图索引压缩和解压缩情况ALTER SESSION SET EVENTS ’10716 trace name context forever, level 1′;

Event 10717 Trace Bitmap Index Compaction

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

Event 10719 Trace Bitmap Index DML

跟踪位图索引列的DML操作（引起位图索引改变的DML操作）ALTER SESSION SET EVENTS ’10719 trace name context forever, level 1′;

Event 10730 Trace Fine Grained Access Predicates

跟踪细粒度审计的断语ALTER SESSION SET EVENTS ’10730 trace name context forever, level 1′;Event 10731 Trace CURSOR Statements跟踪CURSOR的语句情况ALTER SESSION SET EVENTS ’10731 trace name context forever, level level’;

LEVEL定义

1 Print parent query and subquery

2 Print subquery only

Event 10928 Trace PL/SQL Execution

跟踪PL/SQL执行情况ALTER SESSION SET EVENTS ’10928 trace name context forever, level 1′;Event 10938 Dump PL/SQL Execution Statistics跟踪PL/SQL执行状态。使用前需要执行rdbms/admin下的tracetab.sqlALTER SESSION SET EVENTS ’10938 trace name context forever, level 1′;flush_cache刷新BUFFER CACHEALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;DROP_SEGMENTS手工删除临时段。当这些临时段无法自动清除的时候可以手工清除alter session set events ‘immediate trace name DROP_SEGMENTS level ts#+1′;

ts#是指要删除临时段的表空间的ts#

1楼xiangsir3天前 21:30

我想知道官方文档里哪有这方面文档？求教

Re: laoshangxyc3天前 09:19

回复xiangsirn没有