oracle internal events:
introduction:(簡(jiǎn)介)
有四種類(lèi)型的events:
immediate dumps
conditional dumps
trace dumps
events that change database behaviour
每一個(gè)事件都有一個(gè)號(hào)跟oracle的錯(cuò)誤信息是一樣的.如10046和ora-10046
每一個(gè)事件都有一個(gè)level,可以是以下:
范圍1到10
位標(biāo) 0x01 0x02 0x04 0x08 0x10
標(biāo)識(shí) 0=off,1=on
id號(hào) 對(duì)象id(object id),內(nèi)存地址(memory address)
要注意的是,events在每一個(gè)版本之間都有所改變.有一些存在的事件可能存在爭(zhēng)議性或者已經(jīng)不可用了,往往這些事件號(hào)會(huì)由新的事件所替代掉.也要注意在當(dāng)前的版本中message file不一定可以反映出events.
很多events都會(huì)影響數(shù)據(jù)庫(kù)的行為,一些測(cè)試events極有可能導(dǎo)致數(shù)據(jù)庫(kù)down掉.所以,在沒(méi)有oracle support的前提下,最好不要在pro系統(tǒng)上做events操作.dev系統(tǒng)上如果要做events最好先做個(gè)數(shù)據(jù)庫(kù)的全備份.
enabling events(enable事件)
events可以在instance一級(jí)enabled,主要是在init.ora文件中做操作:
event='event trace name context forever, level level';
(紅色部分:event指事件號(hào).level指定事件的級(jí)別)
一次可以enable多個(gè)事件,可以用以下兩種方式:
1. 用一個(gè)冒號(hào)隔開(kāi)
event = "10248 trace name context forever, level 10:10249 trace name context forever, level 10"
2. 兩個(gè)events分開(kāi)寫(xiě)
event="10248 trace name context forever, level 10"
event="10249 trace name context forever, level 10"#一些版本的oracle，event要一樣的大小寫(xiě)
events也可以在instance一級(jí)用alter system命令來(lái)enable:
alter system set events 'event trace name context forever, level level';
在instance一級(jí)用以下disable
alter system set events 'event trace name context off';
events也可以在session一級(jí)用alter session命令來(lái)enable:
alter session set events 'event trace name context forever, level level';
在session一級(jí)用以下命令disable:
alter session set events 'event trace name context off';
events在其他的session用oradebug來(lái)enable:
在一個(gè)process中實(shí)現(xiàn)enable:
oradebug event event trace name context forever, level level
在某個(gè)進(jìn)程中enable:
oradebug setorapid 8(pid進(jìn)程號(hào))
oradebug event event trace name context forever, level level
以下命令disable:
oradebug event event trace name context off
在session中實(shí)現(xiàn)enable:
oradebug session_event event trace name context forever, level level
在session中實(shí)現(xiàn)disable:
oradebug session_event event trace name context off
events也可以用dbms_system.setev包來(lái)實(shí)現(xiàn)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系統(tǒng)下message文件在底下目錄$oracle_home/rdbms/mesg/oraus.msg
在nt系統(tǒng)下message文件在底下目錄$oracle_home/rdbms/mesg/oraus.msg
listing enabled events(列出enabled events)
用以下命令列出在當(dāng)前session已經(jīng)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的參考:(紅色的為最常用的而且對(duì)dba比較有用的events)
event 10013 - monitor transaction recovery------在startup時(shí)跟蹤事務(wù)恢復(fù)
alter session set events '10013 trace name context forever, level 1';
event 10015 - dump undo segment headers----在事務(wù)恢復(fù)后做dump回退段頭信息
alter session set events '10015 trace name context forever, level 1';
event 10032 - dump sort statistics----dump排序的統(tǒng)計(jì)信息,level 10是最詳細(xì)的
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,有執(zhí)行計(jì)劃,邦定變量和等待的統(tǒng)計(jì)信息,level 12最詳細(xì)
alter session set events '10046 trace name context forever, level 12';
級(jí)別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語(yǔ)句時(shí),dump出優(yōu)化器所做的選擇,級(jí)別level 1最詳細(xì)
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的統(tǒng)計(jì)信息
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的統(tǒng)計(jì)信息,用level 10
alter session set events '10104 trace name context forever, level 10';
event 10128 - dump partition pruning information—dump分區(qū)表信息
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--設(shè)置在做全表掃描時(shí)跳過(guò)損壞的數(shù)據(jù)塊（在做exp時(shí)，如果有壞塊，設(shè)置這個(gè)可以使exp導(dǎo)出時(shí)跳過(guò)壞塊，使得部分?jǐn)?shù)據(jù)能可以使用）
alter system set events '10231 trace name context forever,level 10';
event 10241 - dump remote sql execution—dump遠(yuǎn)程sql語(yǔ)句的執(zhí)行信息
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—跟蹤隊(duì)列
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
資料來(lái)源http://www.juliandyke.com/
以上為個(gè)人在做dba時(shí)的一些參考資料.
