SQL_TRACE/10046事件如何使用，详细分析
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.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 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$SESSION WHERE USERNAME='SIMON'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 0 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.01 0.00 0 0 0 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
2 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=0 size=121 card=1)
2 NESTED LOOPS (cr=0 pr=0 pw=0 time=56 us cost=0 size=108 card=1)
2 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=48 us cost=0 size=82 card=1)
2 FIXED TABLE FIXED INDEX X$KSLWT (ind:1) (cr=0 pr=0 pw=0 time=0 us cost=0 size=26 card=1)
2 FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)
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 4.22 4.22
********************************************************************************
alter session set events '10046 trace name context forever,level 12'
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
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
SQL*Net message from client 1 2.84 2.84
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 6 0.01 0.01 0 0 0 0
Execute 7 0.00
- 更多SQLServer疑问解答：
- 配置MSSQL复制指定快照文件夹提示：不是有效的路径或文件名
- 详细解读varchar和Nvarchar区别
- SQL SERVER 2005 同步复制技术
- 进程未能大容量复制到表 解决方法
- MSSql实例教程:MSSql数据库同步
- SQLServer2000同步复制技术实现（分发和订阅）
- sqlserver
- SQLSERVER
- 测试 sqlserver 最大用户数连接
- 写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键,注意：ID可能不是连续的
- sqlserver2008的安装问题。
- 为什么安装的SQL server 2008我的没有MSSQLSERVER协议啊？
- 怎样把exel表里的数据复制到sqlserver表里
- sqlserver数据库主键和外键问题
- sqlserver 中的左表连接查询和右表连接查询有啥不同？有什么用？