eygle大师的微信讲堂昨天开课，第一堂课和大家分享了一些学习oracle的基本方法，其中提到了使用sql_trace和10046事件。sql_trace是oracle提供的用于进行sql跟踪的手段，是强有力的辅助诊断工具。在日常的数据库问题诊断和解决中，sql_trace是非常常用的方法。
对于这个工具，我很早就听过，但是从来就没用过，“纸上得来终觉浅，绝知此事要躬行”，操练起来。
1.环境准备
我们在oracle11g中进行测试。
sql>
sql> select * from v$version;
banner
--------------------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.3.0 - production
pl/sql release 11.2.0.3.0 - production
core 11.2.0.3.0 production
tns for linux: version 11.2.0.3.0 - production
nlsrtl version 11.2.0.3.0 - production
sql>
2.启用sql_trace
在oracle中初始化设置中sql_trace默认是关闭的，它可以作为初始化参数在全局启用，也可以通过命令行方式在具体session启用。
1． 在全局启用
在参数文件(pfile/spfile)中指定:
sql_trace =true
在全局启用sql_trace会导致所有进程的活动被跟踪，包括后台进程及所有用户进程，这通常会导致比较严重的性能问题，所以在生产环境中要谨慎使用，这个参数在10g之后是动态参数，可以随时调整，在某些诊断中非常有效。
提示: 通过在全局启用sql_trace，，我们可以跟踪到所有后台进程的活动，很多在文档中的抽象说明，通过跟踪文件的实时变化，我们可以清晰的看到各个进程之间的紧密协调。
2． 在当前session级设置
大多数时候我们使用sql_trace跟踪当前进程.通过跟踪当前进程可以发现当前操作的后台数据库递归活动(这在研究数据库新特性时尤其有效)，研究sql执行，发现后台错误等。
我在测试中启用session级别的sql_trace，如下所示。
sql>
sql> show parameter sql_trace
name type value
------------------------------------ ----------- ------------------------------
sql_trace boolean false
sql>
sql> alter session set sql_trace=true;
session altered.
sql>
sql> show parameter sql_trace
name type value
------------------------------------ ----------- ------------------------------
sql_trace boolean true
sql>
3.连接soctt用户，执行查询语句
登陆scott用户，执行两条简单的查询语句。
[oracle@hoegh admin]$ sqlplus scott/tiger
sql*plus: release 11.2.0.3.0 production on wed may 27 09:59:48 2015
copyright (c) 1982, 2011, oracle. all rights reserved.
connected to:
oracle database 11g enterprise edition release 11.2.0.3.0 - production
with the partitioning, olap, data mining and real application testing options
sql> select * from cat;
table_name table_type
------------------------------ -----------
bonus table
dept table
emp table
salgrade table
sql> select * from dept;
deptno dname loc
---------- -------------- -------------
10 accounting new york
20 research dallas
30 sales chicago
40 operations boston
4.生成trace文件
plustrace角色
和oracle10g一样，11g中plustrace角色默认也是disabled的。如果使用非授权用户打开oracle trace功能会得到以下的错误。
sql>
sql> show user
user is \scott\
sql>
sql> set autotrace on
sp2-0618: cannot find the session identifier. check plustrace role is enabled
sp2-0611: error enabling statistics report
sql>
这时需要执行$oracle_home/sqlplus/admin/plustrce.sql脚本，手工创建plustrace角色，在此不做演示。因为我们更多的时候是需要跟踪其他用户的进程，而很多这样的用户可能没有被授予或者不允许授予plustrace角色。这时可以使用dbms_system包来实现对进程的跟踪，这儿需要提供用户进程的sid和serial#。
10046事件
在这儿就不得不提到10046事件，10046事件是oracle提供的内部事件，是对sql_trace的增强。
10046事件可以设置以下四个级别：
1 - 启用标准的sql_trace功能,等价于sql_trace
4 - level 1 加上绑定值(bind values)
8 - level 1 + 等待事件跟踪
12 - level 1 + level 4 + level 8
和sql_trace类似，10046事件可以在全局设置，也可以在session级设置。
生成trace文件
首先，我们通过查询v$session视图获取scott用户进程的sid和serial#；
然后执行dbms_system.set_ev过程来实现对进程的跟踪。
sql>
sql> select sid,serial#,username from v$session where username=\'scott\';
sid serial# username
---------- ---------- ------------------------------
21 2615 scott
sql>
sql> exec dbms_system.set_ev(21,2615,10046,12,\'scott\');
pl/sql procedure successfully completed.
sql>
5.查看trace文件
存放目录
在11g中trace文件的存放目录有了变化，其中，11gr1 或 11gr1 以上版本可以通过查询diagnostic_dest参数获得；而11gr1以前版本则是通过user_dump_dest参数来指定。
sql> show parameter diagnostic_dest
name type value
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/oracle
sql>
在测试数据库中，trace文件的具体路径为：/u01/app/oracle/diag/rdbms/hoegh/hoegh/trace/。