使用dbms_system来对其他会话进行10046事件12级别的跟踪看不到等待统计信息

数据库版本是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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-753904/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26015009/viewspace-753904/