使用sql_trace/10046事件進行資料庫診斷

      sql_trace/10046事件是oracle提供的用于進行sql跟蹤的手段，其内容包括sql的解析過程、sql的執行計劃、綁定變量的使用、會話發生的等待事件。

    在使用sql_trace/10046事件前，有兩個參數需要注意：

[email protected]> show parameter max_dump_file_size  --對trace檔案的大小限制       

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                   string      UNLIMITED
s[email protected]> show parameter timed_statistics    --對重要資訊的收集

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE
 
           

      sql_trace的作用域：

    1）對所有使用者：

        i）在參數檔案中指定sql_trace=true

       ii）alter system set sql_trace=true/false

    2) 對目前使用者：

        啟用目前session的跟蹤：alter session set sql_trace=true;

        結束跟蹤：alter session set sql_trace=false;

    3）對其他使用者：

        選擇跟蹤的程序（使用者）

[email protected]> select sid,serial#,username from v$session where username is not null;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       142         14 HR
       159          5 SYS
           

        設定跟蹤：

[email protected]> exec dbms_system.set_sql_trace_in_session(142,14,true) 

PL/SQL procedure successfully completed.
           

        停止跟蹤：

[email protected]> exec dbms_system.set_sql_trace_in_session(142,14,false)

PL/SQL procedure successfully completed.
           

    *************************************↖(^ω^)↗**********************************

    10046事件是對sql_trace的增強，可分4個級别，具體如下：

    1）level 1：等價于sql_trace

    2）level 4：level 1+綁定值

    3）level 8：level 1+等待事件跟蹤

    4）level 12：等價于level 1+level 4+level 8

    10046事件的作用域：

    1）對所有使用者

        i）在參數檔案中修改：event=“10046 trace name context forever，level 12”

       ii）alter system set events ‘10046 trace name context forever，level 8';

            / alter system set events '10046 trace name context off';

    2）對目前使用者

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

        alter session set events '10046 trace name context off';

    3）對其他使用者

        查詢要跟蹤的對象

[email protected]> select sid,serial#,username from v$session where username is not null;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       142         14 HR
       159          5 SYS
           

        執行跟蹤

[email protected]> exec dbms_system.set_ev(142,14,10046,8,'hr');

PL/SQL procedure successfully completed.
           

        結束跟蹤

[email protected]> exec dbms_system.set_ev(142,14,10046,0,'hr');

PL/SQL procedure successfully completed.
           

    *********************************~(@^_^@)~*************************************

    由于trace檔案的格式比較難以閱讀，我們可以通過TKPROF工具進行格式化，最終生成符合我們閱讀習慣的sql trace 檔案。

    格式如下：

    tkprof  tracefile  outputfile  [options]

    其中，option選項的說明和使用，我們可以通過在bash環境羅列：

[[email protected] ~]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor
           

    我們通過一個例子來介紹對tkprof的使用：

SQL> show parameter timed_statistics


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
timed_statistics                     boolean     TRUE
SQL> show parameter max_dump_file_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                   string      UNLIMITED


           

[email protected]> alter session set sql_trace=true;

[email protected]> select * from hr.departments; 

[email protected]> alter session set sql_trace=false; --最好是關閉跟蹤，減小對性能的開銷

[email protected]> exit;  --在運作完查詢後，要退出sql*plus（斷開連接配接），這樣才能完全關閉跟蹤檔案，使trace檔案的所有資訊都是有效的。
           

    然後，我們就可以使用tkprof：

[[email protected] ~]$ tkprof /u01/app/oracle/admin/orcl/udump/orcl_ora_5467.trc tkprof_think.txt

TKPROF: Release 10.2.0.1.0 - Production on Fri Jul 27 11:18:13 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


[[email protected] ~]$ ls
tkprof_think.txt