10046 trace詳解(1)

---轉載http://blog.sina.com.cn/s/blog_61cd89f60102edlx.html

10046 trace幫助我們解析一條/多條SQL、PL/SQL語句的執行狀態，這些狀態包括：Parse/Fetch/Execute三個階段中遇到的等待事件、消耗的物理和邏輯讀、CPU時間、執行計劃等，它為我們揭示了一條、多條SQL的執行情況，對SQL調優是一個非常好的輔助工具，同時還能幫我們分析一些DDL維護命令的內部工作原理，RMAN、Data Pump Expdp/impdp等工具緩慢問題。

    記得進公司面試時提到這個一樣問題，檢視SQL執行計劃有哪此方法，10046、PL/SQL F5快捷鍵、EXPLAIN PLAN FOR ....、TOAD也可以檢視，其實10053也可以檢視SQL的執行計劃，那麼10046和10053的區別是什麼呢？10053是最常用的ORACLE優化器optimizer跟蹤trace，10053可以作為我們解析優化器為什麼選擇某個執行計劃，其中理由的輔助工具，但並不告訴我們這個執行計劃到底執行的如何。而10046並不解釋optimizer優化器的工作，但它同樣說明了在SQL解析Parse階段所遇到的等待事件和所消耗的CPU資源，以及Execute執行和Fetch階段的各項指標，簡單來說，10046告訴我們SQL插計劃執行情況，10053告訴我們優化器為什麼選擇這個執行計劃。

     通常為了診斷SQL調優類問題，我們需要記錄下這些語句在執行過程中產生的等待以及bind variables(繫結變數)的資訊。 這些資訊可以通過級別為12的10046 trace獲得。下面的例子列舉了在各種場景下，如何設定10046事件。

一、Trace檔案的位置
    從11gR1開始，Oracle引入了新的診斷結構，以引數DIAGNOSTIC_DEST控制存放trace檔案與core檔案的路徑。可以用以下命令，獲取DIAGNOSTIC_DEST的位置：

    SQL> show parameter diagnostic_dest;

    11gR1以前,果是使用者程式，10046 trace檔案會被生成在user_dump_dest下,如果是後臺程式，trace檔案會被生成在background_dump_dest下,下面的命令可以顯示user_dump_dest:

    SQL> show parameter user_dump_dest

    注：通過設定tracefile_identifier，通過這個設定可以幫助我們更容易的找到生成的trace檔案。

二、在Session級開啟trace
    這種方法適用於SQL語句可以在新的session建立後再執行。

    alter session set tracefile_identifier='10046'; 
    alter session set timed_statistics = true;
    alter session set statistics_level=all;--例項級別設定此選項需要注意，會消耗大量的CPU
    alter session set max_dump_file_size = unlimited;
    alter session set events '10046 trace name context forever,level 12'; --常用的級別是12
    執行需要被trace的SQL

    select * from dual;
    exit;
    如果不退出當前session, 可以用以下命令關閉trace:

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

    注意，如果session沒有被徹底地關閉並且跟蹤被停止了，某些重要的trace資訊的可能會丟失。
    注意：這裡我們將"statistics_level"設定為all，這是因為有可能這個引數在系統級不是預設值"TYPICAL"（比如 BASIC）。為了收集效能相關問題的資訊我們需要開啟某個級別的statistics。我們推薦在 session 級將這個引數設定成 ALL 以便於收集更多的資訊，儘管這不是必須的。

三、跟蹤一個已經開始的程式
    如果需要跟蹤一個已經存在session，可以用 oradebug連線到session上，併發起10046 trace。
    首先，用某種方法找到需要被跟蹤的session.
    例如，在SQL*Plus裡，找出目標session的OS的程式ID(spid):
    select p.PID,p.SPID,s.SID
    from v$process p,v$session s
    where s.paddr = p.addr
    and s.sid = &SESSION_ID
    /

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

用以下命令關閉在所有session中的10046跟蹤：

    alter system set events '10046 trace name context off';
五、通過初始化引數設定進行例項層的跟蹤
    設定以下引數並重新啟動例項後，例項上所有的session都會開啟跟蹤。

    event="10046 trace name context forever,level 12"

    如果要關閉可以通過移除這個引數並且重啟例項，或者使用下面的alter system命令可以關閉跟蹤。

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

六、通過logon trigger設定跟蹤
   有的時候當需要跟蹤某個特定使用者的操作時，可以使用logon trigger來開啟跟蹤

   CREATE OR REPLACE TRIGGER SYS.set_trace
   AFTER LOGON ON DATABASE
   WHEN (USER like '&USERNAME')
   DECLARE
   lcommand varchar(200);
   BEGIN
   EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
   EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
   EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
   EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
   END set_trace;
   / 

   需要注意的事，為了能開啟跟蹤session, 執行trigger的使用者需要被顯式地授予'alter session'許可權

   grant alter session to user_xj

七、用SQLT收集trace
    SQLTXPLAIN的Xecute方法生成的診斷檔案中會包含10046 trace，正如XECUTE這個名字所示，SQLT會執行被分析的SQL語句，然後生成一個診斷檔案集（包括10046 trace）。詳見：
    Note:215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly (Doc ID 215187.1)

    用這種方法生成的trace檔案會被包含在SQLT輸出包中， 格式為：

    sqlt_s12345_10046_execute.trc   --其中12345是SQLT報告ID。
八、用DBMS_MONITOR進行跟蹤
    DBMS_MONITOR包提供幾個開啟跟蹤的方法。詳細請見：
    Note:293661.1 Tracing Enhancements In 10g Using DBMS_MONITOR
    如果您執行的是PL/SQL儲存過程或包，那麼可以通過使用PL/SQL profiler判斷PL/SQL執行期間的時間消耗的具體資訊。下面的文件介紹了PL/SQL profiler的使用方法：
    Note:243755.1 Implementing and Using the PL/SQL Profiler

九、其它特定場景下開啟跟蹤的方法
    Note:21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
    Note:1274511.1 General SQL_TRACE / 10046 trace Gathering Examples
    Note:160124.1 How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables
    Note:371678.1 Capture 10046 Traces Upon User Login (without using a trigger)
    Note:1102801.1 How to Get 10046 Trace for Parallel Query
    Note:242374.1 Tracing PX session with a 10046 event or sql_trace
    Note:258418.1 Getting 10046 Trace for Export and Import

十、Trace檔案解析

    Note:199081.1 SQL_TRACE (10046), TKProf and Explain Plan - Overview Reference
    Note:39817.1 Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output
    Note:224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits 

                  generated by EVENT 10046
    NOTE:41634.1 - TKProf Basic Overview
    NOTE:32951.1 - TKProf Interpretation (9i and below)
    NOTE:760786.1 - TKProf Interpretation (9i and above)
    NOTE:214106.1 - Using TKProf to compare actual and predicted row counts
    Note:199083.1 * Master Note: SQL Query Performance Overview
    Note:398838.1 * FAQ: SQL Query Performance - Frequently Asked Questions