使用dbms_system对其它会话启动10046事件

1、首先查看 DBMS_SYSTEM的结构

     从下面的信息看出一共有四个变量，分别是

      SI    用户会话的SID

      SE   用户会话的serial#

      EV   事件类型

      LE    事件级别，其中LE为 0 时表示 跟踪结束

     NM    用户名称

SYS@orcl> desc dbms_system

PROCEDURE SET_EV
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SI                             BINARY_INTEGER          IN
 SE                             BINARY_INTEGER          IN
 EV                             BINARY_INTEGER          IN
 LE                             BINARY_INTEGER          IN
 NM                             VARCHAR2                IN

2、确认当前跟踪文件的路径（本次演示的环境为11G）

SYS@orcl>  SELECT  value  FROM v$diag_info WHERE NAME='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/primary_orcl/orcl/trace/orcl_ora_2802.trc

3、生成跟踪文件

开启对 SID 为 38 的会话跟踪

SYS@orcl> exec dbms_system.set_ev(38,27,10046,12,'test');

在会话 SID 为 38 的用户下执行下面的SQL

TEST@orcl> SELECT * FROM TEST_BTREE;

关闭会话跟踪

SYS@orcl> exec dbms_system.set_ev(38,27,10046,0,'test');

查格式化跟踪文件

[oracle@test-db trace]$ tkprof ./orcl_ora_2802.trc ~/orcl_ora_2802.log sys=no sort=prsela,exeela,fchela

 

格式化后的跟踪文件

[oracle@test-db trace]$ cat ~/orcl_ora_2802.log

TKPROF: Release 11.2.0.3.0 - Development on Tue Dec 18 22:32:59 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: ./orcl_ora_2802.trc
Sort options: prsela  exeela  fchela  
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: d6mgq8gfhvhq5 Plan Hash: 3234823583

SELECT *
FROM
 TEST_BTREE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     4805      0.14       0.40       1026       5754          0       72051
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4807      0.14       0.43       1026       5754          0       72051

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 86  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     72051      72051      72051  TABLE ACCESS FULL TEST_BTREE (cr=5754 pr=1026 pw=0 time=136566 us cost=289 size=6988947 card=72051)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                    4805        0.00          0.03
  asynch descriptor resize                        1        0.00          0.00
  direct path read                               23        0.00          0.00
  SQL*Net message from client                  4804        0.09         53.37

 

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     4805      0.14       0.40       1026       5754          0       72051
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4807      0.14       0.43       1026       5754          0       72051

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                    4805        0.00          0.03
  asynch descriptor resize                        1        0.00          0.00
  direct path read                               23        0.00          0.00
  SQL*Net message from client                  4804        0.09         53.37

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    1  user  SQL statements in session.
    0  internal SQL statements in session.
    1  SQL statements in session.
********************************************************************************
Trace file: ./orcl_ora_2802.trc
Trace file compatibility: 11.1.0.7
Sort options: prsela  exeela  fchela  
       2  sessions in tracefile.
       1  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       1  SQL statements in trace file.
       1  unique SQL statements in trace file.
   14625  lines in trace file.
      54  elapsed seconds in trace file.

与autotrace 生成的执行计划对比，结果一致

TEST@orcl> SELECT * FROM TEST_BTREE;

72051 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3234823583

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 72051 |  6825K|   289   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| TEST_BTREE | 72051 |  6825K|   289   (1)| 00:00:04 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5754  consistent gets
       1026  physical reads
          0  redo size
    8260208  bytes sent via SQL*Net to client
      53352  bytes received via SQL*Net from client
       4805  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72051  rows processed