10046事件

10046事件
1，10046是SQL_TRACE的扩展，分为如下级别
0级：SQL_TRACE=FASLE
1级：SQL_TRACE=TRUE，这是缺省级别
4级：1级+绑定变量
8级：4级+等待事件
12级：4级+8级,
2，跟踪会话
2.1 第一种方法

SYS@orcl> alter session set max_dump_file_size=unlimited;

Session altered.

SYS@orcl> alter session set timed_statistics=true;

Session altered.

SYS@orcl> alter session set events '10046 trace name context forever,level 12';

Session altered.

SYS@orcl> select count(1) from dba_users;

  COUNT(1)
----------
        87

SYS@orcl> alter session set events '10046 trace name context off';

Session altered.

2.2 使用oradebug,当然他的功能远远不止

SYS@orcl> oradebug session_event 10046 trace name context forever,level 4
Statement processed.
SYS@orcl> select count(1) from zehb_yc1.nms_t_cfg_site_info;

  COUNT(1)
----------
       303

SYS@orcl> oradebug event 10046 trace name context off;
Statement processed.

2.3 第三种方法

SYS@orcl> alter session set sql_trace=true;

Session altered.

SYS@orcl> select count(1) from dba_users;

  COUNT(1)
----------
        87

SYS@orcl> alter session set sql_trace=false;

Session altered.

3，查看跟踪文件

SYS@orcl> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

TRACEFILE
--------------------------------------------------------------------------------
/opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_27728.trc

SYS@orcl> oradebug tracefile_name
/opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_24790.trc

4，格式化跟踪文件
使用tkprof格式化跟踪文件，方便阅读
tkprof /opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_27728.trc 1.txt

vim /home/oracle/1.txt

TKPROF: Release 11.2.0.1.0 - Development on Sat Apr 1 11:17:26 2017

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

Trace file: /opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_24790.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
********************************************************************************

SQL ID: 9tvwrunwqqhgd
Plan Hash: 2201281379
select count(1)
from
 zehb_yc1.nms_t_cfg_site_info
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       1.55          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          1          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       1.55          0          1          0           1

Misses in library cache during parse: 1
...