Oracle Events(个人参考资料)_oracle

oracle Internal Events:

Introduction:(简介)

有四种类型的Events:

Immediate dumps

Conditional dumps

Trace dumps

Events that change database behaviour

每一个事件都有一个号跟Oracle的错误信息是一样的.如10046和ORA-10046

每一个事件都有一个Level,可以是以下:

范围1到10

位标 0x01 0x02 0x04 0x08 0x10

标识 0=off,1=on

ID号对象ID(object id),内存地址(memory address)

要注意的是,Events在每一个版本之间都有所改变.有一些存在的事件可能存在争议性或者已经不可用了,往往这些事件号会由新的事件所替代掉.也要注意在当前的版本中message file不一定可以反映出Events.

很多Events都会影响数据库的行为,一些测试Events极有可能导致数据库DOWN掉.所以,在没有Oracle Support的前提下,最好不要在PRO系统上做Events操作.DEV系统上如果要做Events最好先做个数据库的全备份.

Enabling Events(Enable事件)

Events可以在Instance一级Enabled,主要是在INIT.ORA文件中做操作:

event=’event trace name context forever, level level’;

(红色部分:event指事件号.level指定事件的级别)

一次可以Enable多个事件,可以用以下两种方式:

1. 用一个冒号隔开

event = “10248 trace name context forever, level 10:10249 trace name context forever, level 10”

2. 两个Events分开写

event=”10248 trace name context forever, level 10″

event=”10249 trace name context forever, level 10″#一些版本的Oracle，event要一样的大小写

Events也可以在Instance一级用ALTER SYSTEM命令来Enable:

ALTER SYSTEM SET EVENTS ‘event trace name context forever, level level’;

　在Instance一级用以下Disable

ALTER SYSTEM SET EVENTS ‘event trace name context off’;

Events也可以在Session一级用ALTER SESSION命令来Enable:

ALTER SESSION SET EVENTS ‘event trace name context forever, level level’;

在Session一级用以下命令Disable:

ALTER SESSION SET EVENTS ‘event trace name context off’;

Events在其他的Session用ORADEBUG来Enable:

在一个Process中实现Enable:

ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level

在某个进程中Enable:

ORADEBUG SETORAPID 8(PID进程号)

ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level

以下命令Disable:

ORADEBUG EVENT event TRACE NAME CONTEXT OFF

在Session中实现Enable:

ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level

在Session中实现Disable:

ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT OFF

Events也可以用DBMS_SYSTEM.SETEV包来实现Enable和Disable

(在做之前要先从V$session视图中获得SID和Serial#)

用以下方式:EXECUTE DBMS_SYSTEM.SET_EV(SID,Serial#,event,level, ”)

如EXECUTE dbms_system.set_ev (9,29,10046,8,”);

要Disable则将level改为0,如: EXECUTE dbms_system.set_ev (9,29,10046,0,”);

Listing All Events:(列出所有可用的Events)

大部分的Events number的范围都在10000到10999.可以用以下命令Dump出所有的信息

SET SERVEROUTPUT ON

DECLARE

err_msg VARCHAR2(120);

BEGIN

dbms_output.enable (1000000);

FOR err_num IN 10000..10999

LOOP

err_msg := SQLERRM (-err_num);

IF err_msg NOT LIKE ‘%Message ‘||err_num||’ not found%’ THEN

dbms_output.put_line (err_msg);

END IF;

END LOOP;

END;

在UNIX系统下message文件在底下目录$ORACLE_HOME/rdbms/mesg/oraus.msg

在NT系统下message文件在底下目录$ORACLE_HOME/rdbms/mesg/oraus.msg

Listing Enabled Events(列出Enabled Events)

用以下命令列出在当前Session已经Enabled Events:

SET SERVEROUTPUT ON

DECLARE

l_level NUMBER;

BEGIN

FOR l_event IN 10000..10999

LOOP

dbms_system.read_ev (l_event,l_level);

IF l_level > 0 THEN

dbms_output.put_line (‘Event ‘||TO_CHAR (l_event)||

‘ is set at level ‘||TO_CHAR (l_level));

END IF;

END LOOP;

END;

常用Events的参考:(红色的为最常用的而且对DBA比较有用的Events)

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排序的统计信息,level 10是最详细的

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

Event 10033 – Dump Sort Intermediate Run Statistics—level 10(不明白)

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参考如下图:

Level

Action

Print SQL statements, execution plans and execution statistics

As level 1 plus bind variables

As level 1 plus wait statistics

As level 1 plus bind variables and wait statistics

Event 10053 – Dump Optimizer Decisions—在分析SQL语句时,Dump出优化器所做的选择,级别level 1最详细

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

参考level:

Level

Action

Print statistics and computations

Print computations only

Event 10060 – Dump Predicates—(参考http://www.juliandyke.com/)

Event 10065 – Restrict Library Cache Dump Output for State Object Dumps-(参考http://www.juliandyke.com/)

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—Dump HASH JOIN的统计信息,用level 10

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

Event 10128 – Dump Partition Pruning Information—Dump分区表信息

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

Level参考

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

在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—(不明白)

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—Dump索引块的Split和Delete信息

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

Event 10225 – Dump Changes to Dictionary Managed Extents—Dump出在Row Cache中,字典管理的Extents的改变

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

Event 10231–设置在做全表扫描时跳过损坏的数据块（在做EXP时，如果有坏块，设置这个可以使EXP导出时跳过坏块，使得部分数据能可以使用）

ALTER SYSTEM SET EVENTS ‘10231 trace name context forever,level 10’;

Event 10241 – Dump Remote SQL Execution—Dump远程SQL语句的执行信息

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

Event 10246 – Trace PMON Process—只能在init.ora中做,不能用ALTER SYSTEM做

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

Event 10248 – Trace Dispatcher Processes—在init.ora中做(9iDump到udump目录中)

event = “10248 trace name context forever, level 10”

Event 10249 – Trace Shared Server (MTS) Processes—在init.ora中做(9iDump到udump目录中)

event = “10249 trace name context forever, level 10”

Event 10270 – Debug Shared Cursors—(不明白)

event = “10270 trace name context forever, level 10”

Event 10299 – Debug Prefetching—(参考http://www.juliandyke.com/)

event = “10299 trace name context forever, level 1”

Event 10357 – Debug Direct Path—(参考http://www.juliandyke.com/)

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

Event 10390 – Dump Parallel Execution Slave Statistics–(参考http://www.juliandyke.com/)

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

Event 10391-Dump Parallel Execution Granule Allocation—

(参考http://www.juliandyke.com/)

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

Event 10393 – Dump Parallel Execution Statistics–(参考http://www.juliandyke.com/)

ALTER SESSION SET EVENTS ‘10393 trace name context forever, level 1’;

Event 10500 – Trace SMON Process–在init.ora中做

event = “10500 trace name context forever, level 1”

Event 10608 – Trace Bitmap Index Creation—跟踪二位图索引

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-(参考http://www.juliandyke.com/)

ALTER SESSION SET EVENTS ‘10706 trace name context forever, level 1’;

Event 10708 – Trace RAC Buffer Cache—跟踪RAC的Buffer Cache

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

Event 10710 – Trace Bitmap Index Access–(参考http://www.juliandyke.com/)

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—

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

Event 10715 – Trace Bitmap Index Conversion to ROWIDs Operation—

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—

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—

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

Levels are:

Level

Action

Print parent query and subquery

Print subquery only

Event 10928 – Trace PL/SQL Execution—

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

Event 10938 – Dump PL/SQL Execution Statistics—

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

一些其他的Events：

ALTER SESSION SET EVENTS ‘immediate trace name flush_cache’;–Flush the Buffer cache

资料来源http://www.juliandyke.com/

以上为个人在做DBA时的一些参考资料.

欢迎大家阅读《Oracle Events(个人参考资料)_oracle》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]‍，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：Oracle Events(个人参考资料)_oracle

Hi，您需要填写昵称和邮箱！