使用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

目录
相关文章
|
5月前
|
存储 编解码 前端开发
会话跟踪技术
会话跟踪技术
41 2
|
4月前
|
Oracle 关系型数据库
oracle收集统计信息,游标失效时间
Dbms_stats Invalidates Cursors in Auto_invalidate mode
44 0
|
Oracle 关系型数据库
10G自动收集统计信息修改
10G自动收集统计信息修改
114 0
10G自动收集统计信息修改
|
SQL Oracle 关系型数据库
下一篇
无影云桌面