Oracle性能分析1：开启SQL跟踪和获取trace文件

wre2

当Oracle查询出现效率问题时，我们往往需要了解问题所在，这样才能针对问题给出解决方案。Oracle提供了SQL执行的trace信息，其中包含了SQL语句的文本信息，一些执行统计，处理过程中的等待，以及解析阶段（如生成执行计划）产生的信息。这些信息有助于你分解sql语句的服务时间和等待时间，并了解所用资源和同步点的详细信息，从而帮助你诊断存在的性能问题。
这篇文章介绍了怎么开启SQL跟踪和获取trace文件，详细信息如下。
开启SQL跟踪

从内部技术细节看，SQL跟踪是基于10046调试事件的，下面是支持的等级：

0
禁止调试事件
1
调试事件是激活的。针对每个被处理的数据库调用，给定如下信息：SQL语句、响应时间、服务时间
处理的行数、处理的行数、逻辑读数量、物理读与写的数量、执行计划以及一些额外信息
4
如果等级1，包括绑定变量的额外信息。主要是数据类型、精度以及每次执行时所用的值
8
同等级1，加上关于等待时间的细节信息。为了处理过程中的每个等待，提供如下信息：等待时间的名字、持续时间，以及一些额外的参数，可标明所等待的资源
12
同时启动等级4和等级8
在Oracle 9i或者之前，下面SQL语句针对所在会话激活SQL跟踪：


    alter session set sql_trace = true  

还可通过dbms_session包中的set_sql_trace存储过程，或者通过dbms_system包的set_sql_tarce_in_session存储过程方法，但这些都只是在等级1激活SQL跟踪，在实践中用处不大，就不详述了。
更有用的是指定级别的方式，下面是对所在会话开始等级12的SQL跟踪：


    alter session set events '10046 trace name context forever, level 12'  

对应的对所在会话禁止SQL跟踪的语句如下：


    alter session set events '10046 trace name context off'  

也可以通过dbms_system包中的set_ev存储过程，这里也不详述了，我下面重点讲讲Oracle 10g之后提供的方法。
Oracle 10g之后提供了dbms_monitor包来开启或关闭SQL跟踪，提供了在会话、客户端、组件以及数据库层级开启SQL跟踪方法，注意只有dba角色的用户才允许使用。
会话级

下面的PL/SQL为ID为122，序列号为6734的会话开启第8级的SQL跟踪：


    begin  
      dbms_monitor.session_trace_enable(session_id => 122,  
                                        serial_num => 6734,  
                                        waits      => true,  
                                        binds      => false);  
    end;  

session_id
session标识，对应v$session视图中的SID列，下面是获取当前会话id的方法：


    select userenv('sid') from dual  

serial_num
对应v$session视图中的SERIAL#列，由于SID会重用，当SID被重用时，SERIAL#增加，获取方法如下：


    select serial# from v$session where sid = 122  

waits
对应v$session视图中的SQL_TRACE_WAITS，表示等待事件跟踪是否被激活，默认为true。

binds
对应v$session视图中的SQL_TRACE_BINDS，表示绑定跟踪是否被激活，默认false。

当执行成功后，v$session视图中的SQL_TRACE被修改为ENABLED，SQL_TRACE_WAITS和SQL_TRACE_BINDS为你设置的对应值。
下面的PL/SQL用于关闭SQL跟踪：


    begin  
      dbms_monitor.session_trace_disable(session_id => 122, serial_num => 6734);  
    end;  

客户端级

下面的PL/SQL调用为所有具有指定客户端标记的会话开启第8级的SQL跟踪：


    begin  
      dbms_monitor.client_id_trace_enable(client_id => 'test',  
                                          waits     => true,  
                                          binds     => false);  
    end;  

需要注意客户端标记区分大小写，可以通过下面的方法看是否设置成功：


    select primary_id as client_id, waits, binds  
      from dba_enabled_traces  
     where trace_type = 'CLIENT_ID'  

当设置成功后，每次查询前指定对应的客户端标记就可以开启SQL跟踪，指定客户端标记的方法如下：


    begin  
      DBMS_SESSION.SET_IDENTIFIER('test');  
    end;  
    //该会话的SQL跟踪已经开启  

当你为一个session设置了标记后，可以在v$session的client_identifier列查看该标记。
下面的PL/SQL用于关闭SQL跟踪：


    begin  
      dbms_monitor.client_id_trace_disable(client_id => 'test');  
    end;  

组件级

下面的PL/SQL调用为所有具有指定客户端标记的会话开启第8级的SQL跟踪：


    begin  
      dbms_monitor.serv_mod_act_trace_enable(service_name  => 'ly',  
                                             module_name   => 'PL/SQL Developer',  
                                             action_name   => 'SQL 窗口 - 新建',  
                                             waits         => true,  
                                             binds         => false,  
                                             instance_name => null);  
    end;  

参数中的service_name对应v$session视图的service_name，module_name对应v$session视图的的module，action_name对应v$session视图的action，查询方式如下：


    SELECT sid, serial#,  
                client_identifier, service_name, action, module  
           FROM V$SESSION  

设置之后可以通过如下方法查看设置：


    select primary_id    as service_name,  
           qualifier_id1 as module_name,  
           qualifier_id2  as action_name,  
           waits,  
           binds  
      from dba_enabled_traces  
     where trace_type = 'SERVICE_MODULE_ACTION'  

下面的PL/SQL用于关闭SQL跟踪：


    begin  
      dbms_monitor.serv_mod_act_trace_disable(service_name  => 'ly',  
                                              module_name   => 'PL/SQL Developer',  
                                              action_name   => 'SQL 窗口 - 新建',  
                                              instance_name => null);  
    end;  

数据库级

下面的PL/SQL调用开启了数据库的12级SQL跟踪：


    begin  
      dbms_monitor.database_trace_enable(waits         => true,  
                                         binds         => true,  
                                         instance_name => null);  
    end;  

下面的方法查看设置是否成功：


    select instance_name,  
           waits,  
           binds  
      from dba_enabled_traces  
     where trace_type = 'DATABASE'  

下面的PL/SQL用于关闭SQL跟踪：


    begin  
      dbms_monitor.database_trace_disable(instance_name => null);  
    end;  

trace文件中的计时信息

下面的语句用于为trace文件提供计时信息：


    alter session set timed_statistics = true  

一般情况下默认都为true，如果不提供计时信息，trace文件就没有什么用了，因此开启SQL跟踪之前，最好确认一下参数被设置为true。
获取生成的trace文件

开启SQL跟踪后，会生成一个trace文件，通过初始化参数user_dump_dest配置其所在目录，该参数的值可以通过下面方法获取到：


    select name, value from v$parameter where name = 'user_dump_dest'  

但如果我们需要定位到具体的文件，则需要了解trace文件的命名。trace文件的名字是独立于版本和平台的，在大部分常见的平台下，命名结构如下：

{instance name}_{process name}_{process id}.trc

1）instance name
初始化参数instance_name的小写值。通过v$instance视图的instance_name列可以得到这个值。
2）process name
产生跟踪文件进程的名字的小写值。对于专有服务器进程，使用ora，对于共享服务器进程，可以通过v$diapatcher或v$shared_server视图的name列获得。对于并行从属进程，可以通过v$px_process视图server_name列获得，对于其他多数后台进程来说，可以通过v$bgprocess视图的name列获得。
3）process id
操作系统层面的进程标记。这个值可以通过v$process视图的spid列获取。
根据这些信息，可以通过下面的方式获取trace文件名：


    select s.SID,  
           s.SERVER,  
           lower(case  
                   when s.SERVER in ('DEDICATED', 'SHARED') then  
                    i.INSTANCE_NAME || '_' || nvl(pp.SERVER_NAME, nvl(ss.NAME, 'ora')) || '_' ||  
                    p.SPID || '.trc'  
                   else  
                    null  
                 end) as trace_file_name  
      from v$instance      i,  
           v$session       s,  
           v$process       p,  
           v$px_process    pp,  
           v$shared_server ss  
     where s.PADDR = p.ADDR  
       and s.SID = pp.SID(+)  
       and s.PADDR = ss.PADDR(+)  
       and s.TYPE = 'USER'  
       and s.SID = 'your sid'  
     order by s.SID  

将上面的'your sid'替换为你的session的sid就可以查出指定session生成的trace文件的名字，session的sid在v$session视图中得到，或者直接查询当前session的sid：


    select userenv('sid') from dual  

将路径（user_dump_dest）和文件名结合在一起，我们就得到了trace文件的完整路径。

而在Oracel 11g中，查询当前会话生成的trace文件则非常简单：


    select value from v$diag_info where name = 'Default Trace File'