如何使用SQL_TRACE和10046事件
- WBOY원래의
- 2016-06-07 15:57:341220검색
提到了使用SQL_TRACE和10046事件。SQL_TRACE是Oracle提供的用于进行SQL跟踪的手段，是强有力的辅助诊断工具。在日常的数据库问题
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/。