10046 事件跟踪

10046事件是oracle提供的内部跟踪事件，是对SQL_TRACE的曾强，提供10046可以通知oracle内核开启SQL_TRACE类的跟踪。使用步骤如下：
1.开启跟踪：SQL> alter session set events ‘10046 trace name context forever , level 12’;
2.执行跟踪内容：SQL> select * from workitem t where rownum<5;
3.停止跟踪：SQL> alter session set events ‘10046 trace name context off’;
4.tkprof格式化：切换到跟踪日志文件所在路径下，通过tkprof格式化
C:\Users\WXH-NOTE>f:

F:\>cd f:\app\wxh-note\diag\rdbms\orcl\orcl\trace
f:\app\WXH-NOTE\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_16184.trc trace.txt print=100 record=sql.txt sys=no

5.分析跟踪日志

TKPROF: Release 11.2.0.1.0 - Development on 星期四 2月 16 19:26:06 2017


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


Trace file: orcl_ora_16184.trc
Sort options: default


********************************************************************************
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






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


OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS


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        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0


Misses in library cache during parse: 0


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       40.18         40.18




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_16184.trc
Trace file compatibility: 11.1.0.7
Sort options: default


       1  session 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.
      31  lines in trace file.
       0  elapsed seconds in trace file.