分类： Oracle
2011-08-31 16:53:50
一. 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进行跟踪，在实例级别，会对实例上所有的SQL做跟踪，这种方式跟踪的SQL太多，代价是非常大的，所有很少用。如果是在初始化文件里面设置，只需要在参数文件里添加一个sql_trace 参数即可。
设置trace 文件标识
SQL> alter session set tracefile_identifier='Your_Wanted_Name';
会话已更改。
设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。 在Oracle 10g中，SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump.
到了11g，trace 默认路径在：$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下.
SQL> show parameter dump
NAME TYPE VALUE
background_dump_dest string /u01/app/oracle/diag/rdbms/orc
lspc/orclspc/trace
user_dump_dest string /u01/app/oracle/diag/rdbms/orc
lspc/orclspc/trace
直接用如下SQL直接查出，当前的trace文件名。
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;
用oradebug来查看trace file
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orclspc/orclspc/trace/orclspc_ora_18405.trc
二． TKPROF 工具
SQL_TRACE 生成最原始的trace文件的可读性比较差，所以通常我们使用tkprof 工具来处理trace文件。 Tkprof 工具是Oracle 自带的一个工具，用于处理原始的trace文件，它的作用主要是合并汇总trace文件中的一些项，规范化文件的格式，使文件更具有可读性。
注意：tkprof 工具只能用在处理SQL_TRACE和10046事件产生的trace，其他事件如10053不能处理。
2.1 explain=user/password
在trace文件中输入SQL的执行计划，需要注意的是，如果不使用explain，在trace 文件中我们看到的是SQL实际的执行路劲。 如果使用了explain，tkprof在trace文件中不但输入SQL的实际执行路径，还会生成该SQL的执行计划。
2.2 sys=no
如果设置为yes，在trace 文件中将输入所有的SYS用户的操作，也包含用户SQL语句引发的递归SQL。
如果为no，则不输出这些信息。
不过默认情况下是yes，实际上设置为no后，trace文件具有更佳的可读性，因此一般在用tkprof工具时都手工的把该参数设置为no。
2.3 aggregate=yes|no
默认情况下，tkprof工具将所有相同的SQL在输入文件中做合并，如果设置为no，则分别列出每个SQL的信息。一般合并后看起来比较简洁，如果需要查看每一个SQL单独的信息，可以把aggregate设置为no。
2.4 生成的trace文件
tkprof /u01/app/oracle/diag/rdbms/orclspc/orclspc/trace/orclspc_ora_18405.trc a.txt
2.5 查看trace 文件
在2.4中，我们看到了tkprof生成的报告，这个报告是一个汇总的结果集，如果想确切的知道SQL 语句的每一步执行是如果操作的，就需要分析原始的trace文件。 这个trace 虽然没有tkprof工具处理之后易读，但是却能够清楚的知道SQL在那个点做了什么，以及SQL是如何工作的，这对与理解SQL语句的执行过程非常有用。
这个文件的可读性要差很多。 对这里面的一些参数做些说明：
PARSING IN CURSOR 部分：
Len: 被解析SQL的长度
Dep: 产生递归SQL的深度
Uid：user id
Otc: Oracle command type 命令的类型
Lid: 私有用户id
Tim：时间戳
Hv： hash value
Ad：SQL address
PARSE,EXEC,FETCH 部分
C: 消耗的CPU time
E：elapsed time 操作的用时
P: physical reads 物理读的次数
Cr: consistent reads 一致性方式读取的数据块
Cu：current 方式读取的数据块
Mis：cursor misss in cache 硬分析次数
R: -rows 处理的行数
Dep: depth 递归SQL的深度
Og： optimizer goal 优化器模式
Tim：timestamp时间戳
STATS 部分：
Id: 执行计划的行源号
Cnt：当前行源返回的行数
Pid：当前行源号的父号
Pos：执行计划中的位置
Obj：当前操作的对象id（如果当前行原始一个对象的话）
Op：当前行源的数据访问操作
三． 10046 事件
Oracle 的事件很多。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, 即同时收集绑定变量信息和等待事件信息。
3.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文件中
3.2对其他的会话进行跟踪
之前说的都是对当前session进行跟踪，在生产环境中，可能需要对其他session进行跟踪，有如下2种方法：
3.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 过程已成功完成。
3.2.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语句操作出现性能问题时，我们可以用SQL_TRACE 或者10046事件进行跟踪是最合适的。 如果是数据库整体性能下降，就需要使用statspack或者AWR对数据库进行分析。
