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/
以上為個人在做DBA時的一些參考資料.
