SQL_TRACE及tkprof的使用

来源：互联网发布：草图大师mac版怎么样编辑：程序博客网时间：2024/04/29 05:16

sql_trace是oracle提供的一个非常好的跟踪工具，主要用来检查数据库的异常情况，通过跟踪数据库的活动，找到有问题的语句。

一、概述：
SQL_TRACE是Oracle的一个非常强大的工具。打开SQL_TRACE就可以逐步捕获任何一个会话的数据库活动，或者捕获整个数据库的活动，并将数据库活动记录成跟踪文件。每次使用完之后需要关闭跟踪,否则会降低系统的性能。

二、用法：
   1、文件跟踪的分类：
      跟踪DBA可以采用两种方式进行跟踪：
    . 跟踪整个数据库实例。只需要简单的修改参数文件(pfile/spfile)参数 SQL_TRACE = TRUE ，然后重新启动数据库即可。在全局启用SQL_TRACE会导致所有进程的活动被跟踪，包括后台进程及所有用户进程,这样也会数据库导致性能下降比较明显。
    . 会话级跟踪。SQL_TRACE的通常使用方式是仅跟踪一个会话。被跟踪的会话可以是您自己的，也可以是其它用户的会话。如果是自己的会话，只需要在SQL*PLUS中运行一下命令即可：
      SQL> alter session set sql_trace = true;
      类似的如果取消对会话的跟踪，运行一下命令：
      SQL> alter session set sql_trace = false;

      如果需要跟踪一个特定的会话，首先需要获取会话的SID和Serial#，这些信息可以在视图V$SESSION中获得，一旦知道了这两个参数，就可以运行一下命令：
      SQL> execute SYS.dbms_system.set_sql_trace_in_session(13,9,true);
      同样也可以使用这个过程关闭会话跟踪：
    SQL> execute SYS.dbms_system.set_sql_trace_in_session(13,9,false);

2、跟踪文件的位置：
     一旦为会话激活了SQL_TRACE，ORACLE就会在udump管理区创建跟踪文件，文件的目标位置由参数user_dump_dest来确定。每个操作都不会覆盖原来的文件，新的跟踪记录将会被追加到文件末尾。通常情况下，可以根据文件的修改时间判断目录下哪个文件是最新的文件。

得到trc文件的路径

SQL> show parameter user_dump_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string H:/ORACLE/PRODUCT/10.2.0/ADMIN/MYTEST/UDUMP

得到trc文件的具体名称

SQL> select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||p.spid|| '.trc' 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;

TRACE_FILE_NAME
--------------------------------------------------------------------------------
H:/ORACLE/PRODUCT/10.2.0/ADMIN/MYTEST/UDUMP/mytest_ora_5820.trc

3、计时信息：
    为了最大限度的利用跟踪文件，应该打开计时标志,通过参数TIMED_STATISTICTS=TRUE进行设置，这样可以对每个SQL语句的执行时间等进行记录，这个功能对系统性能的负担很小。
    打开会话的计时信息:
    SQL> alter session set timed_statistics = true ;
    打开数据库系统的计时信息
    SQL> alter system set timed_statistics = true ;

4、TKPROF：
    通过前三步的设置已经知道如何生成SQL跟踪文件了，ORACLE生成的跟踪文件阅读起来很困难（也就是易读性很差），可以看跟踪文件的一部分，执行以下SQL语句：
SQL> alter session set sql_trace = true;

Session altered
SQL> select b.goods_return_no,
2         b.company_code,
3         b.departure_no,
4         b.departure_line_no
5    from som_goods_return_master t, som_goods_return_detail b
6   where t.goods_return_no = b.goods_return_no
7     and t.company_code = 'A0'
8     and b.goods_return_date = to_date('20091013', 'yyyy-mm-dd')
9     and b.create_emp_no = 'Z004';

GOODS_RETURN_NO COMPANY_CODE DEPARTURE_NO DEPARTURE_LINE_NO
--------------- ------------ ------------ -----------------
CCS2938012      A0           GGS29D0670   05
CCS2938012      A0           GGS29D0670   06
CCS2938012      A0           GGS29D0670   07
CCS2938048      A0           GGS29D1296   01
CCS2938080      A0           GGS29C8581   01
CCS2938080      A0           GGS29C8581   02
CCS2938080      A0           GGS29C8581   03
CCS2938080      A0           GGS29C8581   04
CCS2938080      A0           GGS29C8581   05
CCS2937983      A0           GGS29D1406   01
CCS2938012      A0           GGS29D0670   01
CCS2938012      A0           GGS29D0670   02
CCS2938012      A0           GGS29D0670   03
CCS2938012      A0           GGS29D0670   04
CCS2938080      A0           GGS29C8581   06

15 rows selected

SQL> alter session set sql_trace = false;

Session altered

执行完后，查看跟踪文件中这个语句的内容，基本看不懂。

ORACLE提供了一个格式化跟踪文件的工具 - TKPROF( Transient Kernel Profiler ),通过这个工具能将SQL文件转化为分析人员容易理解的格式。
一般TKPROF工具的使用的简单方法,只用到了两个关键字:跟踪文件名和输出文件名 (TKPROF的具体请参阅其他资料):

    TKPROF <trace file> <output file>

    在命令行模式下运行

C:/Documents and Settings/zhenhuan_yu>tkprof H:/ORACLE/PRODUCT/10.2.0/ADMIN/MYTEST/UDUMP/mytest_ora_5820.trc h:/report.txt

TKPROF: Release 10.2.0.1.0 - Production on 星期五 5月 21 17:01:20 2010

查看这个report

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.07       0.08          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0        692          0          15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3      0.07       0.09          0        695          0          15

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54

Rows     Row Source Operation
------- ---------------------------------------------------
     15 NESTED LOOPS (cr=692 pr=0 pw=0 time=449 us)
     15   TABLE ACCESS FULL SOM_GOODS_RETURN_DETAIL (cr=675 pr=0 pw=0 time=138 us)
     15   INDEX UNIQUE SCAN SOM_GOODS_RETURN_MASTER_PK (cr=17 pr=0 pw=0 time=167 us)(object id 51207)

这个基本就能理解了

使用explain似乎也没什么意义，该有的都有过了

C:/Documents and Settings/zhenhuan_yu>tkprof H:/ORACLE/PRODUCT/10.2.0/ADMIN/MYTEST/UDUMP/mytest_ora_5820.trc h:/report.txt explain=yuzh/yuzh

TKPROF: Release 10.2.0.1.0 - Production on 星期五 5月 21 17:10:16 2010

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.07       0.08          0          3          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0        692          0          15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3      0.07       0.09          0        695          0          15

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54