ITPub博客

首页 > 数据库 > Oracle > Oracle Events

Oracle Events

原创 Oracle 作者:fanhongjie 时间:2008-05-22 10:49:50 0 删除 编辑
收集一些关于ORACLE内部EVENTS的资料[@more@]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

1

print sql statements, execution plans and execution statistics

4

as level 1 plus bind variables

8

as level 1 plus wait statistics

12

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

1

print statistics and computations

2

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

1

print parent query and subquery

2

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/

以上内容由 华夏名网 收集整理,如转载请注明原文出处,并保留这一部分内容。

来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/73920/viewspace-1004406/ ,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-05-22

  • 博文量
    111
  • 访问量
    685243