SQL_TRACE/10046事件如何使用，详细分析
这是oracle提供用来进行SQL跟踪的强有力的工具，可跟踪解析过程，执行计划，绑定变量，递归调用等等
先执行SQL的TRACE命令，生成TRACE文件，TKPROF格式化，分析文件
alter session set sql_trace=true，启动sql_trace功能。 alter session set events '10046 trace name context forever,level 12'; 开启10046事件。
其中LEVEL代表的是10046事件设置的级别，共4类：
1——启用标准的sql_trace功能，等价于sql_trace
4——level 1加上绑定值
8——level 1 等待时间跟踪
12——leve1 1+leve 4+level 8
以上可以看出10046其实就是sql_trace的增强版。
全局session的修改可用过alter system
如图，生成追踪文件
本机是在该路径下发现：E:\oracle11g\diag\rdbms\simon\simon\trace
然后tkprof 该文件
下面是格式化后的信息内容：
TKPROF: Release 11.1.0.7.0 - Production on Thu Aug 1 14:39:34 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. Trace file: simon_ora_9168.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 ******************************************************************************** ALTER SESSION SET SQL_TRACE=TRUE call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.000.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 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS ******************************************************************************** BEGIN DBMS_SYSTEM.SET_EV(133,6,10046,0,'SIMON'); END; 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 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 1 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS ******************************************************************************** BEGIN DBMS_SYSTEM.SET_EV(133,6,10046,8,'SIMON'); END; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.01 0 0 0 0 Execute 2 0.00 0.00 0 0 0 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.01 0 0 0 2 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS 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 ******************************************************************************** SELECT SID,SERIAL#,USERNAME FROM V$S