Events事件是Oracle的重要诊断工具及问题解决办法很多时候需要通过Events设置来屏蔽或者更改Oracle的行为;
有四种类型的Events:
Immediate dumps
Conditional dumps
Trace dumps
Events that change database behaviour
每一个事件都有一个号跟Oracle的错误信息是一样的如和ORA
每一个事件都有一个Level可以是以下:
范围到
位标 x x x x x
标识 =off=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主要是在INITORA文件中做操作:
event=event trace name context forever level level;
(红色部分:event指事件号level指定事件的级别)
一次可以Enable多个事件可以用以下两种方式:
用一个冒号隔开
event = trace name context forever level : trace name context forever level
两个Events分开写
event= trace name context forever level
event= trace name context forever level #一些版本的Oracleevent要一样的大小写
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 (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_SYSTEMSETEV包来实现Enable和Disable
(在做之前要先从V$session视图中获得SID和Serial#)
用以下方式:EXECUTE DBMS_SYSTEMSET_EV(SIDSerial#eventlevel )
如EXECUTE dbms_systemset_ev ();
要Disable则将level改为如: EXECUTE dbms_systemset_ev ();
Listing All Events:(列出所有可用的Events)
大部分的Events number的范围都在到可以用以下命令Dump出所有的信息
SET SERVEROUTPUT ON
DECLARE
err_msg VARCHAR();
BEGIN
dbms_outputenable ();
FOR err_num IN
LOOP
err_msg := SQLERRM (err_num);
IF err_msg NOT LIKE %Message ||err_num|| not found% THEN
dbms_outputput_line (err_msg);
END IF;
END LOOP;
END;
/
在UNIX系统下message文件在底下目录$ORACLE_HOME/rdbms/mesg/orausmsg
在NT系统下message文件在底下目录$ORACLE_HOME/rdbms/mesg/orausmsg
Listing Enabled Events(列出Enabled Events)
用以下命令列出在当前Session已经Enabled Events:
SET linesize
SET feedback OFF
SET SERVEROUTPUT ON
DECLARE err_msg VARCHAR();
BEGIN
dbms_outputenable ();
FOR err_num IN LOOP
err_msg := SQLERRM (err_num);
IF err_msg NOT LIKE %Message ||err_num|| not found% THEN
dbms_outputput_line (err_msg);
END IF;
END LOOP;
END;
/
常用Events的参考:(红色的为最常用的而且对DBA比较有用的Events)
Event Monitor Transaction Recovery在Startup时跟蹤事务恢复
ALTER SESSION SET EVENTS trace name context forever level ;
Event Dump Undo Segment Headers在事务恢复后做Dump回退段头信息
ALTER SESSION SET EVENTS trace name context forever level ;
Event Dump Sort StatisticsDump排序的统计信息level 是最详细的
ALTER SESSION SET EVENTS trace name context forever level ;
Event Dump Sort Intermediate Run Statistics—level (不明白)
ALTER SESSION SET EVENTS trace name context forever level ;
Event Trace Free List Management Operations—跟蹤Freelist
ALTER SESSION SET EVENTS trace name context forever level ;
Event Enable SQL Statement Trace跟蹤SQL有执行计划邦定变量和等待的统计信息level 最详细
ALTER SESSION SET EVENTS trace name context forever level ;
级别level参考如下图:
Level Action
Print SQL statements execution plans and execution statistics
As level plus bind variables
As level plus wait statistics
As level plus bind variables and wait statistics
Event Dump Optimizer Decisions在分析SQL语句时Dump出优化器所做的选择级别level 最详细
ALTER SESSION SET EVENTS trace name context forever level ;
参考level:
Level Action
Print statistics and computations
Print computations only
Event Dump Predicates(参考)
Event Restrict Library Cache Dump Output for State Object Dumps(参考)
Event Dump SQL*Net StatisticsDump SQL*NeT的统计信息
ALTER SESSION SET EVENTS trace name context forever level ;
Event Trace High Water Mark Changes—跟蹤HWM的改变
ALTER SESSION SET EVENTS trace name context forever level ;
Event Dump Hash Join Statistics—Dump HASH JOIN的统计信息用level
ALTER SESSION SET EVENTS trace name context forever level ;
Event Dump Partition Pruning Information—Dump分区表信息
ALTER SESSION SET EVENTS trace name context forever level level;
Level参考
Level Action
x Dump pruning descriptor for each partitioned object
x Dump partition iterators
x Dump optimizer decisions about partitionwise joins
x Dump ROWID range scan pruning information
