Oracle Events(个人参考资料)

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 OFFEvents也可以用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 ONDECLARE 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.msgListing 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参考如下图:LevelAction1Print SQL statements, execution plans and execution statistics4As level 1 plus bind variables8As level 1 plus wait statistics12As level 1 plus bind variables and wait statisticsEvent 10053 - Dump Optimizer Decisions---在分析SQL语句时,Dump出优化器所做的选择,级别level 1最具体 ;ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';参考level:LevelAction1Print statistics and computations2Print computations onlyEvent 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参考LevelAction0x0001Dump pruning descriptor for each partitioned object0x0002Dump partition iterators0x0004Dump optimizer decisions about partition-wise joins0x0008Dump 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:LevelAction1Print parent query and subquery2Print subquery onlyEvent 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时的一些参考资料.