SQL_Trace 和 10046 +

1、SQL_TRACE

当SQL语句出现性能问题时，我们可以用SQL_TRACE来跟踪SQL的执行情况，通过跟踪，我们可以了解一条SQL或者PL/SQL包的运行情况，SQL_TRACE命令会将SQL执行的整个过程输出到一个trace文件中，我们可以读这个trace 文件来了解在这个SQL执行过程中Oracle 都做了哪些操作。

 可以通过sql命令启动SQL_TRACE，或者在初始化参数里面。

 SQL>alter session set sql_trace=true;

或者

SQL> alter database set sql_trace=true;

 这两条命令的区别：

                在session级别设置，只对当前session进行跟踪；在实例级别database，会对实例上所有的SQL做跟踪，这种方式跟踪的SQL太多，代价是非常大的，所有很少用。如果是在初始化文件里面设置，只需要在参数文件里添加一个sql_trace 参数即可。注意: 通过在全局启用sql_trace，我们可以跟踪到所有后台进程的活动，很多在文档中的抽象说明，通过跟踪文件的实时变化，我们可以                 清晰的看到各个进程之间的紧密协调。

 示例：

 1.       确定当前的trace文件。

1.1   通过设置trace 文件标识

SQL> alter session set tracefile_identifier='安庆怀宁';

会话已更改。

设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。 在Oracle 10g中，SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump.  

到了11g，trace 默认路径在：$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下.

在当前的session中启用：大多数的情况下，我们使用sql_trace跟踪当前进程。通过跟踪当前进程可以发现当前操作的后台数据库递归活动(这在研究数据库新特性时尤其有效)，研究SQL执行，发现后台错误等。在session级启用和停止sql_trace方式如下:启用当前session的跟踪:

  SQL> alter session set sql_trace=true;

    Session altered.

    此时的SQL操作将被跟踪:

     SQL> select count(*) from dba_users;

      COUNT(*)
    ----------
    34

    结束跟踪:

    SQL> alter session set sql_trace=false;

    Session altered.

跟踪其他用户进程

    在很多时候我们需要跟踪其他用户的进程，而不是当前用户，我们可以通过Oracle提供的系统包DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION来完成。

    SET_SQL_TRACE_IN_SESSION程序需要提供三个参数:

    SQL> desc dbms_system
    …
    PROCEDURE SET_SQL_TRACE_IN_SESSION
     Argument Name                Type           In/Out Default?
     -----------------                   -------------       ------ --------
     SID                                  NUMBER          IN
     SERIAL#                        NUMBER          IN
     SQL_TRACE                 BOOLEAN        IN
    …

    通过v$session我们可以获得sid、serial#等信息。

    获得进程信息，选择需要跟踪的进程:

    SQL> select sid,serial#,username from v$session  where username is not null;

           SID    SERIAL#  USERNAME
    ---------- ---------- ------------------------------
             8       2041  SYS
             9        437  EYGLE

    设置跟踪:

    SQL> exec dbms_system.set_sql_trace_in_session(9,437,true)

    PL/SQL procedure successfully completed.

    ….

    可以等候片刻，跟踪session执行任务,捕获sql操作……

    停止跟踪:

    SQL> exec dbms_system.set_sql_trace_in_session(9,437,false)

    PL/SQL procedure successfully completed.

 

1.2直接用如下SQL直接查出，当前的trace文件名。

/* Formatted on 2010/9/1 23:56:24 (QP5 v5.115.810.9015) */

SELECT      d.VALUE

         || '/'

         || LOWER (RTRIM (i.INSTANCE, CHR (0)))

         || '_ora_'

         || p.spid

         || '.trc'

            AS "trace_file_name"

  FROM   (SELECT   p.spid

            FROM   v$mystat m, v$session s, v$process p

           WHERE   m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

         (SELECT   t.INSTANCE

            FROM   v$thread t, v$parameter v

           WHERE   v.NAME = 'thread'

                   AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

         (SELECT   VALUE

            FROM   v$parameter

           WHERE   NAME = 'user_dump_dest') d;

 

SQL> SELECT    d.VALUE

  2        || '/'

  3        || LOWER (RTRIM (i.INSTANCE, CHR (0)))

  4        || '_ora_'

  5        || p.spid

  6        || '.trc' as "trace_file_name"

  7    FROM (SELECT p.spid

  8            FROM v$mystat m, v$session s, v$process p

  9          WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

 10        (SELECT t.INSTANCE

 11            FROM v$thread t, v$parameter v

 12          WHERE v.NAME = 'thread'

 13            AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

 14        (SELECT VALUE

 15            FROM v$parameter

 16          WHERE NAME = 'user_dump_dest') d;

 

trace_file_name

--------------------------------------------------------------------------------

d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3048.trc

 

2.       启动SQL_TRACE

SQL> alter session set sql_trace=true;

会话已更改。

 

3.       进行相关事务操作

SQL> select * from t;

 

4.       关闭SQL_TRACE

SQL> alter session set sql_trace=false;

会话已更改。

 

注意，这里是显示的关闭SQL_TRACE，在session级别，也可以直接退出SQLPLUS来终止SQL_TRACE。

如何查看该session的sql_trace参数呢？

SQL> ALTER session SET SQL_TRACE=TRUE; (implicitly sets event 10046 at level 1)
SQL> exec dbms_system.read_ev(10046,:lev);
SQL> print lev
得到的数据 lev  应该为 1
表示 sql_trace=true,     lev为0  则sql_trace=false

instance 级别的sql_trace
alter system set sql_trace=true  scope=spfile; 

 or  

alter system set sql_trace=true scope=both; 

show parameter sql_trace  就可以查看sql_trace 的置 

2、10046 事件

 10046 事件主要用来跟踪SQL语句，它并不是ORACLE 官方提供给用户的命令，在官方文档上也找不到事件的说明信息。 但是用的却比较多，因为10046事件获取SQL的信息比SQL_TRACE 更多。 更有利于我们对SQL的判断。

 

10046 事件按照收集信息内容，可以分成4个级别：

                Level 1： 等同于SQL_TRACE 的功能

                Level 4： 在Level 1的基础上增加收集绑定变量的信息

                Level 8： 在Level 1 的基础上增加等待事件的信息

                Level 12：等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息。

 

1、 对当前session 使用10046事件：

                SQL>alter session set events ‘10046 trace name context forever, level 12’; --启动10046事件

                执行相关事务

                SQL>alter session set events ‘10046 trace name context off’; -- 关闭10046事件

 

该事件收集的信息也是放在trace文件中，查看trace文件的方法，参考：TKPROF 工具。

 

2、对其他的会话进行跟踪：

   之前说的都是对当前session进行跟踪，在生产环境中，可能需要对其他session进行跟踪，有如下2种方法：

 1、用SQL_TRACE跟踪

SQL> select sid,serial# from v$session where SID=267;

       SID    SERIAL#

---------- ----------

       267        996

 SQL> execute dbms_system.set_sql_trace_in_session(267,996,true);  -- 启动SQL_TRACE

PL/SQL 过程已成功完成。

 SQL> execute dbms_system.set_sql_trace_in_session(267,996,false); -- 关闭SQL_TRACE

PL/SQL 过程已成功完成。

 2、 使用10046 事件跟踪

SQL> exec dbms_monitor.session_trace_enable(267,996,waits=>true,binds=>true);  -- 启动trace

PL/SQL 过程已成功完成。

 

SQL> exec dbms_monitor.session_trace_disable(267,996); -- 关闭trace

PL/SQL 过程已成功完成。

 

注意：

                如果一条SQL语句中包含了通过DBLINK进行的数据操作，我们想对这条SQL进行trace跟踪，在本地只能够trace到本地执行的SQL信息，而对于远程的SQL语句，由于它运行在远端的数据库上，我们要获得它的信息，需要到远端的数据库上，找到运行这条SQL语句的session，然后对它做Trace。 另外，这条SQL语句的执行计划也只能从远端数据库上捕获到。

 

总之，当SQL语句操作出现性能问题时，我们可以用SQL_TRACE 或者10046事件进行跟踪是最合适的。 如果是数据库整体性能下降，就需要使用statspack或者AWR对数据库进行分析。Oracle AWR 介绍http://blog.csdn.net/changyanmanman/article/details/7746584

 

 

3、使用oradebug 生成10046 事件

SYS@anqing1(rac1)> oradebug setmypid

SYS@anqing1(rac1)> oradebug event 10046trace name context forever,level 8;

SYS@anqing1(rac1)> oradebug event 10046trace name context off;

SYS@anqing1(rac1)> oradebugtracefile_name

/u01/app/oracle/admin/anqing/udump/anqing1_ora_17800.trc          

 

            Oracle oradebug 命令 使用说明：http://blog.csdn.net/changyanmanman/article/details/7005169