用EXPLAIN PLAN 分析SQL語(yǔ)句

http://blog.csdn.net/kj021320/archive/2006/08/19/1096021.aspx

如何生成explain plan?
　　解答:運(yùn)行utlxplan.sql. 建立plan 表

　　針對(duì)特定SQL語(yǔ)句，使用 explain plan set statement_id = 'tst1' into plan_table

　　運(yùn)行utlxplp.sql 或 utlxpls.sql察看explain plan

EXPLAIN PLAN 是一個(gè)很好的分析SQL語(yǔ)句的工具,它甚至可以在不執(zhí)行SQL的情況下分析語(yǔ)句.通過(guò)分析,我們就可以知道ORACLE是怎么樣連接表,使用什么方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱.

你需要按照從里到外,從上到下的次序解讀分析的結(jié)果. EXPLAIN PLAN分析的結(jié)果是用縮進(jìn)的格式排列的, 最內(nèi)部的操作將被最先解讀,如果兩個(gè)操作處于同一層中,帶有最小操作號(hào)的將被首先執(zhí)行.

NESTED LOOP是少數(shù)不按照上述規(guī)則處理的操作, 正確的執(zhí)行路徑是檢查對(duì)NESTEDLOOP提供數(shù)據(jù)的操作,其中操作號(hào)最小的將被最先處理.

譯者按:

通過(guò)實(shí)踐, 感到還是用SQLPLUS中的SET TRACE 功能比較方便.

舉例:

SQL> list

1 SELECT *

2 FROM dept, emp

3* WHERE emp.deptno = dept.deptno

SQL> set autotrace traceonly /*traceonly 可以不顯示執(zhí)行結(jié)果*/

SQL> /

14 rows selected.

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF 'EMP'

3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

Statistics

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

0 recursive calls

2 db block gets

30 consistent gets

0 physical reads

0 redo size

2598 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

通過(guò)以上分析,可以得出實(shí)際的執(zhí)行步驟是:

1. TABLE ACCESS (FULL) OF 'EMP'

2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'

4. NESTED LOOPS (JOINING 1 AND 3)

注: 目前許多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了極其方便的EXPLAINPLAN工具.也許喜歡圖形化界面的朋友們可以選用它們.
----------------------------------------------------------------------------

對(duì)于sql執(zhí)行的小量高低.我們可以通過(guò)執(zhí)行計(jì)劃的信息基本上可以進(jìn)行分析查看該SQL語(yǔ)句執(zhí)行的時(shí)間.連接順序及浪費(fèi)的數(shù)據(jù)庫(kù)資源等信息,從而判斷該SQL語(yǔ)句執(zhí)行的效率如何,下面就簡(jiǎn)單的介紹一下執(zhí)行計(jì)劃的使用
2.        Explain使用
OracleRDBMS執(zhí)行每一條SQL語(yǔ)句，都必須經(jīng)過(guò)Oracle優(yōu)化器的評(píng)估。所以，了解優(yōu)化器是如何選擇(搜索)路徑以及索引是如何被使用的，對(duì)優(yōu)化SQL語(yǔ)句有很大的幫助。Explain可以用來(lái)迅速方便地查出對(duì)于給定SQL語(yǔ)句中的查詢數(shù)據(jù)是如何得到的即搜索路徑(我們通常稱為Access Path)。從而使我們選擇最優(yōu)的查詢方式達(dá)到最大的優(yōu)化效果。
2.1.       安裝
要使用執(zhí)行計(jì)劃首先需要執(zhí)行相應(yīng)的腳本。
使用Explain工具需要?jiǎng)?chuàng)建Explain_plan表，這必須先進(jìn)入相關(guān)應(yīng)用表、視圖和索引的所有者的帳戶內(nèi)。Oracle的介質(zhì)中包含有執(zhí)行此項(xiàng)工作的SQL源程序，例如：
ORA_RDBMS: XPLAINPL.SQL(VMS)
$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)
該腳本后會(huì)生成一個(gè)表這個(gè)程序會(huì)創(chuàng)建一個(gè)名為plan_table的表，表結(jié)構(gòu)如下：
我們簡(jiǎn)單的介紹一下主要的字段含義：
字段名       字段類型        含義
STATEMENT_ID        VARCHAR2(30)        explain PLAN語(yǔ)句中所指定的最優(yōu)STATEMENT_ID 參數(shù)值, 如果在EXPLAN PLAN語(yǔ)句中沒(méi)有使用SETSTATEMENT_ID，那么此值會(huì)被設(shè)為NULL。  
REMARKS        VARCHAR2(80)       與被解釋規(guī)劃的各步驟相關(guān)聯(lián)的注釋最長(zhǎng)可達(dá)80 字節(jié)
OPERATION        VARCHAR2(30)       各步驟所執(zhí)行內(nèi)部操作的名稱在某條語(yǔ)句所產(chǎn)生的第一行中該列的可能取值如下DELETE STATEMENT INSERT STATEMENTSELECT STATEMENT UPDATE STATEMENT
OPTIONS        VARCHAR2(30)       對(duì)OPERATION 列中所描述操作的變種
OBJECT_NODE        VARCHAR2(128)       用于訪問(wèn)對(duì)象的數(shù)據(jù)庫(kù)鏈接database link 的名稱對(duì)于使用并行執(zhí)行的本地查詢?cè)摿心軌蛎枋霾僮髦休敵龅拇涡?br>OBJECT_OWNER       VARCHAR2(30)        對(duì)于包含有表或索引的架構(gòu)schema 給出其所有者的名稱
OBJECT_NAME       VARCHAR2(30)        表或索引的名稱
OBJECT_INSTANCE        INTEGER       根據(jù)對(duì)象出現(xiàn)在原始o(jì)riginal 語(yǔ)句中的次序所給出的相應(yīng)次序編號(hào)就原始的語(yǔ)句文本而論其處理順序?yàn)樽宰笾劣易酝庀騼?nèi)景象擴(kuò)張view
OBJECT_TYPE       VARCHAR2(30)        用于提供對(duì)象描述性信息的修飾符例如索引的NON-UNIQUE
OPTIMIZER       VARCHAR2(255)        當(dāng)前優(yōu)化程序的模式
ID        INTEGER        分配給執(zhí)行規(guī)劃各步驟的編號(hào)
PARENT_ID       INTEGER        對(duì)ID 步驟的輸出進(jìn)行操作的下一個(gè)執(zhí)行步驟的ID
POSITION       INTEGER        對(duì)于具有相同PARENT_ID 的步驟其相應(yīng)的處理次序
COST        INTEGER       根據(jù)優(yōu)化程序的基于開(kāi)銷的方法所估計(jì)出的操作開(kāi)銷值對(duì)于使用基于規(guī)則方法的語(yǔ)句該列為空該列值沒(méi)有特定的測(cè)量單位它只是一個(gè)用于比較執(zhí)行規(guī)劃開(kāi)銷大小的權(quán)重值
CARDINALITY        INTEGER        根據(jù)基于開(kāi)銷的方法對(duì)操作所訪問(wèn)行數(shù)的估計(jì)值
BYTES       INTEGER        根據(jù)基于開(kāi)銷的方法對(duì)操作所訪問(wèn)字節(jié)的估計(jì)

2.2.        使用
2.2.1.        常規(guī)使用
常規(guī)使用語(yǔ)法：
explain PLAN [ SETSTATEMENT_ID [=] < string literal > ]
[ INTO < table_name> ]
FOR < sql_statement >
其中：
STATEMENT_ID是一個(gè)唯一的字符串，把當(dāng)前執(zhí)行計(jì)劃與存儲(chǔ)在同一PLAN表中的其它執(zhí)行計(jì)劃區(qū)別開(kāi)來(lái)。
TABLE_NAME是plan表名，它結(jié)構(gòu)如前所示，你可以任意設(shè)定這個(gè)名稱。
SQL_STATEMENT是真正的SQL語(yǔ)句。
如：
SQL> explain plan setstatement_id='test1' for 
  2     SELECT a.soctermbegin,
 3            a.soctermend,
  4            a.dealserialno,
 5            a.levydataid,
  6            a.dealtotal,
 7            e.categoryitemcode,
  8            row_number()over(PARTITION BY a.levydataid ORDER BY 1) AS theRow
  9       FROMtb_soc_packdealdata   a,
10            tb_Lvy_TaxDataBillMap c,
11           Tb_lvy_BillData       d,
12            tb_soc_levydetaildata e
13     WHERE a.levydataid = c.datafrompointer(+)
14        AND c.billdataid= d.billdataid(+)
15        AND a.levydataid = e.levydataid
16       AND a.packdealstatuscode = '10'
17        AND (a.datastatus <>'9' OR a.datastatus is NULL)
18        AND (d.billstatus IS NULL OR
19           (d.billstatus <> '2' AND d.billstatus <> '8'))
20       AND a.Insurcode = '6010952'
21  ;
Explained
執(zhí)行下面語(yǔ)句就可以查看該語(yǔ)句執(zhí)行的執(zhí)行計(jì)劃:
SQL> SELECTA.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
  2  FROMPLAN_TABLE  a
  3  WHERE STATEMENT_ID='test1'
  4  ORDER BY Id;
OPERATION       OPTIONS        OBJECT_NAME                    OBJECT_TYPEID  PARENT_ID
------------------------------------------------------------- ------------- ----------
SELECTSTATEMENT                                                           0
WINDOW          SORT                                                      1          0
FILTER                                                                    2          1
NESTED LOOPS    OUTER                                                     3          2
NESTEDLOOPS     OUTER                                                    4          3
NESTEDLOOPS                                                              5          4
TABLE ACCESS     FULL          TB_SOC_PACKDEALDATA                        6          5
TABLEACCESS     BY INDEX ROWID TB_SOC_LEVYDETAILDATA                     7          5
INDEX            RANGE SCAN    IND_DATAID_LEVSOC              NON-UNIQUE  8          7
TABLEACCESS     BY INDEX ROWID TB_LVY_TAXDATABILLMAP                     9          4
INDEX            RANGE SCAN    TBLVYTAXDATABIL_DATAFROMPOINTE NON-UNIQUE 10          9
TABLEACCESS     BY INDEX ROWID TB_LVY_BILLDATA                          11          3
INDEX            UNIQUE SCAN   TBLVYBILLDATA_BILLDATAID       UNIQUE  
2.2.2.        自動(dòng)顯示使用
在SQLPLUS中自動(dòng)跟蹤顯示執(zhí)行計(jì)劃及相關(guān)信息
SQL>set timing on  --顯示執(zhí)行時(shí)間
SQL>setautorace on ?C顯示執(zhí)行計(jì)劃
SQL>set autorace on ?C顯示執(zhí)行計(jì)劃
SQL>setautotrace traceonly ?C只顯示執(zhí)行計(jì)劃即不顯示查詢出來(lái)的數(shù)據(jù)
設(shè)置完畢后執(zhí)行SQL語(yǔ)句就會(huì)顯示執(zhí)行計(jì)劃信息及相應(yīng)的統(tǒng)計(jì)信息(需要設(shè)置顯示該選項(xiàng))
SQL> select nvl(sum(t.taxdue), 0)
  2            from tb_lvy_sbzs100 t, tb_lvy_declaredoc a, tb_lvy_declaredoc b
 3            where a.dossiercode = 'SB02041108'
  4              anda.pages = 123
  5              and a.remarkid = b.remarkid
 6              AND A.REMARKID IS NOT NULL
  7              andb.declaredocid = t.declaredocid;
NVL(SUM(T.TAXDUE),0)
--------------------
                  0
已用時(shí)間:  00: 00: 04.07
Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=110)
  1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=6 Card=1Bytes=110)
   3    2       MERGE JOIN (CARTESIAN) (Cost=4 Card=1Bytes=74)
   4    3         TABLE ACCESS (FULL) OF 'TB_LVY_SBZS100'(Cost=2 Card =1 Bytes=31)
   5    3         BUFFER (SORT) (Cost=2Card=1 Bytes=43)
   6    5           TABLE ACCESS (FULL) OF'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=43)
   7    2       TABLEACCESS (BY INDEX ROWID) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=36)
  8    7         INDEX (UNIQUE SCAN) OF 'TBLVYDECLAREDOC_DECLAREDOCID'(UNIQUE)
Statistics
----------------------------------------------------------
         0  recursive calls --循環(huán)遞歸次數(shù)
          0  db blockgets―請(qǐng)求的數(shù)據(jù)塊在buffer能滿足的個(gè)數(shù)
       6675  consistent gets--邏輯IO用于讀表并計(jì)算行數(shù), 數(shù)據(jù)請(qǐng)求總數(shù)在回滾段Buffer中
         45  physical reads?C從磁盤讀到Buffer Cache數(shù)據(jù)塊數(shù)量
          0  redo size ?C產(chǎn)生的redo日志大小
       217  bytes sent via SQL*Net to client
        276  bytes receivedvia SQL*Net from client
          2  SQL*Net roundtrips to/fromclient
          1  sorts (memory)
          0  sorts (disk)
         1  rows processed
SQL>
如果6675  consistent gets--邏輯IO用于讀表并計(jì)算行數(shù), 數(shù)據(jù)請(qǐng)求總數(shù)在回滾段Buffer中
45        physical reads?C從磁盤讀到Buffer Cache數(shù)據(jù)塊數(shù)量
的數(shù)值比較小則該語(yǔ)句對(duì)對(duì)數(shù)據(jù)庫(kù)的性能比較高。

2.2.3.        PL/SQL和TOAD中使用
如果在PL/SQL中使用選擇要查詢語(yǔ)句顯示執(zhí)行計(jì)劃，則只需要SQLWINDOWS 窗口里面輸入要查詢的SQL語(yǔ)句，然后選擇按鍵F5或者在菜單TOOLS?D?D>Explain Plan菜單按鍵就可以在執(zhí)行計(jì)劃窗口查看該語(yǔ)句的執(zhí)行計(jì)劃。
在TOAD語(yǔ)句中在執(zhí)行當(dāng)前的SQL窗口中選擇下方的ExplainPlanTAB頁(yè)即可以查看要執(zhí)行語(yǔ)句的執(zhí)行計(jì)劃信息。
2.3.        限制
雖然任何SQL語(yǔ)句都可以用explain解釋，但對(duì)于沒(méi)有查詢的INSERT，UPDATE，DELETE操作來(lái)說(shuō)，這個(gè)工具并沒(méi)有太大的用處。沒(méi)有子查詢的INSERT操作不會(huì)創(chuàng)建執(zhí)行計(jì)劃，但沒(méi)有WHERE子句或子查詢的UPDATE和DELETE操作會(huì)創(chuàng)建執(zhí)行計(jì)劃，因?yàn)檫@些操作必須先找出所要的記錄。
另外，如果你在SQL語(yǔ)句中使用其它類型如sequence等，explain也能揭示它的用法。
explain真正的唯一的限制是用戶不能去解釋其它用戶的表，視圖，索引或其它類型，用戶必須是所有被解釋事物的所有者，如果不是所有者而只有select權(quán)限，explain會(huì)返回一個(gè)錯(cuò)誤。