oracle SQL跟踪：sql_trace 和 10046事件

秋枫2656

使 SQL_TRACE设置 SQL跟踪非常简单，SQL_TRACE 是一个参数，只需要在会话层将它设
置为TRUE，就开启了 SQL跟踪，Oracle将会把 SQL 语句的执行过程记录到跟踪文件中。通
过查阅跟踪文件，将利于你了解 SQL语句的执行过程，这将帮助你调优、排故 SQL语句。
  跟踪文件的位置在 user_dump_dest 初始化参数中，如下方法可以显示出跟踪文件的位
置：
SQL> show parameter user_dump_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      E:\ORACLE\PRODUCT\10.2.0\ADMIN
                                                 \JJONE\UDUMP
  在我的主机中，跟踪文件在 E:\ORACLE\PRODUCT\10.2.0\ADMIN\JJONE\UDUMP中。

下面我们练习一次 SQL跟踪。
步1：启用跟踪：
SQL> alter session set sql_trace=true;
会话已更改。
  注意要在会话层设置此参数，不要在实例层设置。
步2：执行需要跟踪的 SQL语句
SQL> select * from ui1 where id=1;
        ID NAME
---------- -----
         1 1
  我们在此随变执行个什么语句都行，Oracle会将此语句的执行过程记录到跟踪文件中。  
步3：关闭跟踪
SQL> alter session set sql_trace=false;
会话已更改。
  注意，在执行完想要跟踪的语句后，要马上关闭跟踪。以免不必要的语句的执行过程也
被记录到跟踪文件中。这将影响阅读跟踪文件的结果。
步4：查阅跟踪文件：
  到 E:\ORACLE\PRODUCT\10.2.0\ADMIN\JJONE\UDUMP 目录中，找到日期最新的文件，它
就是Oracle刚刚生成的跟踪文件。在本例中，它的名字是“jjone_ora_3600.trc” 。打开它
查看，此时它的信息还不是太容易理解，Oracle 为了帮助我们阅读 SQL 的跟踪文件，专门
提供了一个TKPROF工具，在操作系统命令提示符下（注意不是在 SQLPLUS中），输入如下命
令：
C:\>tkprof E:\ORACLE\PRODUCT\10.2.0\ADMIN\JJONE\UDUMP\jjone_ora_3600.trc sql1.txt
TKPROF: Release 10.2.0.1.0 - Production on 星期三 10月 15 12:01:00 2008
Copyright (c) 1982， 2005， Oracle.  All rights reserved.
  tkporf工具的使用非常简单，只需输入：“tkprof 跟踪文件 目标文件”即可，Tkprof
将会把跟踪文件处理为更容易阅读的格式，并把处理结果保存到你指定的目标文件中。目标
文件也是普通的文本型文件，可以使用任何文本编辑软件打开。 ORACLE 交流第一群：48949977                                                   
                                 TG-903 Oracle 性能调优  22
  在目标文件中，我们搜索UI1，可以方便的找到所跟踪的语句，你将看到如下信息：
select *  
from
ui1 where id=1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.02          3          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.04          3          4          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 66   
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID UI1 (cr=4 pr=3 pw=0 time=27320 us)
      1   INDEX RANGE SCAN UI1_ID (cr=3 pr=2 pw=0 time=23332 us)(object id 52405)
...
  在你的语句之下，首先是一个表格，它通常有三行组成，它显示该查询各阶段的重要的
执行统计数据。这三行代表查询的三个主要阶段：
解析（Parse）：此阶段是Oracle的优化器为SQL语句生成执行计划的阶段。
执行（Execute）：此阶段是服务器进程按照执行计划执行语句的阶段。
抓取（Fetch）：此阶段是服务器进程从表中抓取结果的阶段。只有 Select 语句才需要此阶
段，Update、Insert、Delete等这些DML语句并不需要抓取行。
  这个表格通常包括八列，这八列意义如下：
Call：说明了语句执行的每个阶段。
Count：此阶段的执行次数。
CPU：完成此阶段工作所耗的 CPU时间，单位是毫秒。
Elapsed：完成此阶段工作所耗费的 CPU时间再加上等待的时间，单位也是毫秒。
Disk：完成此阶段工作所用的物理读次数。
Query：完成此阶段工作所用的一致读次数。
Current：完成此阶段工作所用的当前读次数。Query 和 Current加起来就是逻辑读。
Rows：完成此阶段工作所操作的行数。
接下来一行：“Misses in library cache during parse:”说明硬解析的次数，本例中语句
的执行进行了一次硬解析。
“Optimizer mode”是优化器模式
  另外，我们在目标文件中还会发现大量的其他语句，注意，我们在跟踪其间，只执行了
一条SQL语句，跟踪文件中其他的语句，其实就是所谓的递归调用。也就是为了完成我们的
语句，Oracle内部执行的一些其他的 SQL语句。
  通过观察跟踪文件的目标文件，我们可以观察到比较精确的 CPU时间、物理读逻辑读等
信息，这有助于我们了解一条 SQL语句是否有效。
----
跟踪方法二：10046事件
我们用10046事件也能完成 SQL跟踪，只需要将“alter session set sql_trace=true”
换成“alter session set events ‘10046 trace name context forever，level 1’”，
就可以完成一模一样的工作，这条语句也是开启SQL 跟踪。结束跟踪的语句是：
alter session set events ‘10046 trace name context off’;