如何使用SQL_TRACE和10046事件

提到了使用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/。