資料庫版本是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

使用dbms_system來對其他會話進行10046事件12級別的跟蹤看不到等待統計資訊

相關文章