数据库版本是10.2.0.1.0和10.2.0.4.0
SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.04.0 - Prod
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered
SQL> select count(*) from obj$;
COUNT(*)
----------
51486
SQL>
SQL> select
2 d.value||'/'||lower(rtrim(i.instance,
3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
4 from ( select p.spid
5 from v$mystat m,
6 v$session s,v$process p
7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
8 ( select t.instance from v$thread t,v$parameter v
9 where v.name = 'thread' and
10 (v.value = 0 or t.thread# = to_number(v.value))) i,
11 ( select value from v$parameter
12 where name = 'user_dump_dest') d
13 /
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_2487.trc
对当前会话使用10046级别为12级的跟踪可以看到等待事件信息
select count(*)
from
obj$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.04 0.06 23 133 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.06 23 133 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=133 pr=23 pw=0 time=61480 us)
51486 INDEX FAST FULL SCAN I_OBJ1 (cr=133 pr=23 pw=0 time=1252937 us)(object id 36)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.01 0.01
db file sequential read 3 0.01 0.01
db file scattered read 7 0.00 0.00
********************************************************************************
下面来跟踪其它会话
SQL> exec dbms_system.set_ev(147,57,10046,12,'SYS'); PL/SQL procedure successfully completed SQL> exec dbms_system.set_sql_trace_in_session(147,57,true); PL/SQL procedure successfully completed SQL> exec dbms_system.set_sql_trace_in_session(147,57,false); PL/SQL procedure successfully completed SQL> exec dbms_system.set_ev(147,57,10046,0,'SYS');
但是在跟踪文件中没有等待信息
select count(*)
from
obj$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.03 0 133 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.03 0.03 0 133 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=133 pr=0 pw=0 time=33599 us)
51486 INDEX FAST FULL SCAN I_OBJ1 (cr=133 pr=0 pw=0 time=876016 us)(object id 36)
但是在全局启用10046级别为12级的跟踪在生成的跟文件中可以看到等待事件信息
select cols,audit$,textlength,intcols,property,flags,rowid
from
view$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 317 0.06 0.05 0 19 0 0
Execute 438 0.09 0.08 0 0 0 0
Fetch 438 0.08 0.33 56 1314 0 438
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1193 0.23 0.48 56 1333 0 438
Misses in library cache during parse: 20
Misses in library cache during execute: 20
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID VIEW$ (cr=3 pr=3 pw=0 time=20069 us)
1 INDEX UNIQUE SCAN I_VIEW1 (cr=2 pr=2 pw=0 time=10796 us)(object id 99)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 56 0.01 0.30
SQL*Net message to client 32 0.00 0.00
SQL*Net message from client 32 0.00 0.00
********************************************************************************
而使用dbms_support来进行跟踪在生成的跟踪文件中也能看到等待事件信息
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\dbmssupp.sql
Package created
SQL> exec sys.dbms_support.start_trace_in_session(147,59,true,true);
PL/SQL procedure successfully completed
SQL> exec sys.dbms_support.stop_trace_in_session(147,59);
PL/SQL procedure successfully completed
select count(*)
from
col$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.14 0.29 130 138 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.14 0.30 130 138 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=138 pr=130 pw=0 time=296924 us)
56008 INDEX FAST FULL SCAN I_COL3 (cr=138 pr=130 pw=0 time=1431460 us)(object id 47)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.02 0.02
db file sequential read 1 0.02 0.02
db file scattered read 18 0.02 0.21
使用dbms_monitor来跟其他会话在生成的跟踪文件中也能看到等待事件
SQL> exec dbms_monitor.session_trace_enable(147,61,true,true);
PL/SQL procedure successfully completed
SQL> exec dbms_monitor.session_trace_disable(147,61);
PL/SQL procedure successfully completed
select count(*)
from
ind$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.03 2 9 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.04 2 9 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=9 pr=2 pw=0 time=37846 us)
2345 INDEX FAST FULL SCAN I_IND1 (cr=9 pr=2 pw=0 time=107104 us)(object id 39)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.01 0.01
db file sequential read 2 0.03 0.03
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 2
Fetch 3 0.00 0.03 2 9 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.01 0.04 2 9 0 5
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 8 0.00 0.00
SQL*Net message from client 7 4.37 4.40
db file sequential read 2 0.03 0.03