檢視SQL執行計劃的幾種常用方法

　　不論是做為開發DBA還是維護DBA，總是或多或少地遇到SQL執行效率或者說SQL調優問題，檢視執行計劃是必須的。一般我們可以用3種方法檢視：

　　一、explain plan for (類似在pl/sql中在sql視窗按F5)

　　舉例就足以說明其用法

　　[email protected]>explain plan for

　　2select sysdate from dual;

　　Explained.

　　[email protected]>select * from table(dbms_xplan.display());

　　PLAN_TABLE_OUTPUT

　　----------------------------------------------------------------------------------------------------

　　--------------------------------------------------------------------

　　|Id|Operation|Name|Rows|Bytes|Cost|

　　--------------------------------------------------------------------

　　|0|SELECTSTATEMENT|||||

　　|1|TABLEACCESSFULL|DUAL||||

　　--------------------------------------------------------------------

　　Note:rulebasedoptimization

　　9rowsselected.

　　二、利用TKPROF工具

　　TKPROF是一個用于分析oracle跟蹤檔案并且産生一個更加清晰合理的輸出結果的可執行工具。如果一個系統的執行效率比較低，一個比較好的方法是跟蹤使用者的會話并且使用TKPROF工具的排序功能格式化輸出，進而找出有問題的SQL語句。

　　TKPROF指令後面的選項及輸出檔案各個列的含義在這裡不做詳細的介紹。 google一下就會有很多資料。

　　下面簡單描述一下TKPROF工具的使用步驟：

　　1、在session級别設定sql_trace=true

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

　　Session altered.

         2、獲得sid,serial.

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

         SID SERIAL# USERNAME

        ---------- ---------- ------------------------------

         8 2041 SYS

         9 437  uniguard

　　3 、如果要在pl/sql中對session級别設定true，可以使用dbms_system這個包，這個包要用（SID， SERIAL#）參數：

　　[email protected]> exec dbms_system.set_sql_trace_in_session(9,437,true);   //設定跟蹤:

           ....可以等候片刻，跟蹤session執行任務,捕獲sql操作.......

        [email protected]> exec dbms_system.set_sql_trace_in_session(9,437,true);   //停止跟蹤:

　　4、指定一下生成的trace檔案的名字，便于查找：

　　[email protected]>alter session set tracefile_identifier='yourname';

        查詢trc檔案路徑

       select d.value || '/' || lower(rtrim(i.instance, chr(0))) || '_ora_' ||

           p.spid || '.trc' trace_file_name

        from (select p.spid

                 from sys.v$mystat m, sys.v$session s, sys.v$process p

                 where m.statistic# = 1

                 and s.sid = m.sid

                 and p.addr = s.paddr) p,

        (select t.instance

             from sys.v$thread t, sys.v$parameter v

         where v.name = 'thread'

            and (v.value = 0 or t.thread# = to_number(v.value))) i,

       (select value from sys.v$parameter where name = 'user_dump_dest') d;

　　5、利用TKPROF工具格式化輸出的trace 檔案：

　   [[email protected]~] $tkprof /oracle/admin/orcl/udump/orcl_ora_10266_yourname.trc  /oracle/yourname.txt

　　6、檢視生成的檔案再設定sql_trace=false：

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

　　三、set autotrace on

　　此種方法最常用，關于如何設定sql*plus的autotrace這裡也不做詳細介紹，因為google上面資料确實太多了。有心的朋友可以去找找，保證有一大堆适合你的資料。

　　舉個例子，這種方法簡單易懂：

　　[email protected]>set autotrace on

　　[email protected]>select sysdate from dual;

　　SYSDATE

　　---------

　　25-JUN-08

　　ExecutionPlan

　　----------------------------------------------------------

　　0SELECTSTATEMENTptimizer=CHOOSE

　　10TABLEACCESS(FULL)OF'DUAL'

　　Statistics

　　----------------------------------------------------------

　　0recursivecalls

　　0dbblockgets

　　3consistentgets

　　0physicalreads

　　0redosize

　　522bytessentviaSQL*Nettoclient

　　655bytesreceivedviaSQL*Netfromclient

　　2SQL*Netroundtripsto/fromclient

　　0sorts(memory)

　　0sorts(disk)

　　1rowsprocessed

　　set autotrace off ---------------- 不生成autotrace 報告，這是預設模式

　　set autotrace on explain ------ autotrace隻顯示優化器執行路徑報告

　　set autotrace on statistics -- 隻顯示執行統計資訊

　　set autotrace on ----------------- 包含執行計劃和統計資訊

　　set autotrace traceonly ------ 同set autotrace on，但是不顯示查詢輸出