用EXPLAIN PLAN 分析SQL語(yǔ)句
http://blog.csdn.net/kj021320/archive/2006/08/19/1096021.aspx
如何生成explain plan?
解答:運行utlxplan.sql. 建立plan 表
針對特定SQL語(yǔ)句，使用 explain plan set statement_id = 'tst1' into plan_table
運行utlxplp.sql 或 utlxpls.sql察看explain plan
EXPLAIN PLAN 是一個(gè)很好的分析SQL語(yǔ)句的工具,它甚至可以在不執行SQL的情況下分析語(yǔ)句.通過(guò)分析,我們就可以知道ORACLE是怎么樣連接表,使用什么方式掃描表(索引掃描或全表掃描)以及使用到的索引名稱(chēng).
你需要按照從里到外,從上到下的次序解讀分析的結果. EXPLAIN PLAN分析的結果是用縮進(jìn)的格式排列的, 最內部的操作將被最先解讀,如果兩個(gè)操作處于同一層中,帶有最小操作號的將被首先執行.
NESTED LOOP是少數不按照上述規則處理的操作, 正確的執行路徑是檢查對NESTEDLOOP提供數據的操作,其中操作號最小的將被最先處理.
譯者按:
通過(guò)實(shí)踐, 感到還是用SQLPLUS中的SET TRACE 功能比較方便.
舉例:
SQL> list
1 SELECT *
2 FROM dept, emp
3* WHERE emp.deptno = dept.deptno
SQL> set autotrace traceonly /*traceonly 可以不顯示執行結果*/
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í)際的執行步驟是:
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工具.也許喜歡圖形化界面的朋友們可以選用它們.
----------------------------------------------------------------------------
對于sql執行的小量高低.我們可以通過(guò)執行計劃的信息基本上可以進(jìn)行分析查看該SQL語(yǔ)句執行的時(shí)間.連接順序及浪費的數據庫資源等信息,從而判斷該SQL語(yǔ)句執行的效率如何,下面就簡(jiǎn)單的介紹一下執行計劃的使用
2. Explain使用
OracleRDBMS執行每一條SQL語(yǔ)句，都必須經(jīng)過(guò)Oracle優(yōu)化器的評估。所以，了解優(yōu)化器是如何選擇(搜索)路徑以及索引是如何被使用的，對優(yōu)化SQL語(yǔ)句有很大的幫助。Explain可以用來(lái)迅速方便地查出對于給定SQL語(yǔ)句中的查詢(xún)數據是如何得到的即搜索路徑(我們通常稱(chēng)為Access Path)。從而使我們選擇最優(yōu)的查詢(xún)方式達到最大的優(yōu)化效果。
2.1. 安裝
要使用執行計劃首先需要執行相應的腳本。
使用Explain工具需要創(chuàng )建Explain_plan表，這必須先進(jìn)入相關(guān)應用表、視圖和索引的所有者的帳戶(hù)內。Oracle的介質(zhì)中包含有執行此項工作的SQL源程序，例如：
ORA_RDBMS: XPLAINPL.SQL(VMS)
$ORACLE_HOME/rdbms/admin/utlxplan.sql (UNIX)
該腳本后會(huì )生成一個(gè)表這個(gè)程序會(huì )創(chuàng )建一個(gè)名為plan_table的表，表結構如下：
我們簡(jiǎn)單的介紹一下主要的字段含義：
字段名 字段類(lèi)型 含義
STATEMENT_ID VARCHAR2(30) explain PLAN語(yǔ)句中所指定的最優(yōu)STATEMENT_ID 參數值, 如果在EXPLAN PLAN語(yǔ)句中沒(méi)有使用SETSTATEMENT_ID，那么此值會(huì )被設為NULL。
REMARKS VARCHAR2(80) 與被解釋規劃的各步驟相關(guān)聯(lián)的注釋最長(cháng)可達80 字節
OPERATION VARCHAR2(30) 各步驟所執行內部操作的名稱(chēng)在某條語(yǔ)句所產(chǎn)生的第一行中該列的可能取值如下DELETE STATEMENT INSERT STATEMENTSELECT STATEMENT UPDATE STATEMENT
OPTIONS VARCHAR2(30) 對OPERATION 列中所描述操作的變種
OBJECT_NODE VARCHAR2(128) 用于訪(fǎng)問(wèn)對象的數據庫鏈接database link 的名稱(chēng)對于使用并行執行的本地查詢(xún)該列能夠描述操作中輸出的次序
OBJECT_OWNER VARCHAR2(30) 對于包含有表或索引的架構schema 給出其所有者的名稱(chēng)
OBJECT_NAME VARCHAR2(30) 表或索引的名稱(chēng)
OBJECT_INSTANCE INTEGER 根據對象出現在原始original 語(yǔ)句中的次序所給出的相應次序編號就原始的語(yǔ)句文本而論其處理順序為自左至右自外向內景象擴張view
OBJECT_TYPE VARCHAR2(30) 用于提供對象描述性信息的修飾符例如索引的NON-UNIQUE
OPTIMIZER VARCHAR2(255) 當前優(yōu)化程序的模式
ID INTEGER 分配給執行規劃各步驟的編號
PARENT_ID INTEGER 對ID 步驟的輸出進(jìn)行操作的下一個(gè)執行步驟的ID
POSITION INTEGER 對于具有相同PARENT_ID 的步驟其相應的處理次序
COST INTEGER 根據優(yōu)化程序的基于開(kāi)銷(xiāo)的方法所估計出的操作開(kāi)銷(xiāo)值對于使用基于規則方法的語(yǔ)句該列為空該列值沒(méi)有特定的測量單位它只是一個(gè)用于比較執行規劃開(kāi)銷(xiāo)大小的權重值
CARDINALITY INTEGER 根據基于開(kāi)銷(xiāo)的方法對操作所訪(fǎng)問(wèn)行數的估計值
BYTES INTEGER 根據基于開(kāi)銷(xiāo)的方法對操作所訪(fǎng)問(wèn)字節的估計
2.2. 使用
2.2.1. 常規使用
常規使用語(yǔ)法：
explain PLAN [ SETSTATEMENT_ID [=] < string literal > ]
[ INTO < table_name> ]
FOR < sql_statement >
其中：
STATEMENT_ID是一個(gè)唯一的字符串，把當前執行計劃與存儲在同一PLAN表中的其它執行計劃區別開(kāi)來(lái)。
TABLE_NAME是plan表名，它結構如前所示，你可以任意設定這個(gè)名稱(chēng)。
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
執行下面語(yǔ)句就可以查看該語(yǔ)句執行的執行計劃:
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)跟蹤顯示執行計劃及相關(guān)信息
SQL>set timing on --顯示執行時(shí)間
SQL>setautorace on ?C顯示執行計劃
SQL>set autorace on ?C顯示執行計劃
SQL>setautotrace traceonly ?C只顯示執行計劃即不顯示查詢(xún)出來(lái)的數據
設置完畢后執行SQL語(yǔ)句就會(huì )顯示執行計劃信息及相應的統計信息(需要設置顯示該選項)
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)遞歸次數
0 db blockgets―請求的數據塊在buffer能滿(mǎn)足的個(gè)數
6675 consistent gets--邏輯IO用于讀表并計算行數, 數據請求總數在回滾段Buffer中
45 physical reads?C從磁盤(pán)讀到Buffer Cache數據塊數量
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用于讀表并計算行數, 數據請求總數在回滾段Buffer中
45 physical reads?C從磁盤(pán)讀到Buffer Cache數據塊數量
的數值比較小則該語(yǔ)句對對數據庫的性能比較高。
2.2.3. PL/SQL和TOAD中使用
如果在PL/SQL中使用選擇要查詢(xún)語(yǔ)句顯示執行計劃，則只需要SQLWINDOWS 窗口里面輸入要查詢(xún)的SQL語(yǔ)句，然后選擇按鍵F5或者在菜單TOOLS?D?D>Explain Plan菜單按鍵就可以在執行計劃窗口查看該語(yǔ)句的執行計劃。
在TOAD語(yǔ)句中在執行當前的SQL窗口中選擇下方的ExplainPlanTAB頁(yè)即可以查看要執行語(yǔ)句的執行計劃信息。
2.3. 限制
雖然任何SQL語(yǔ)句都可以用explain解釋?zhuān)珜τ跊](méi)有查詢(xún)的INSERT，UPDATE，DELETE操作來(lái)說(shuō)，這個(gè)工具并沒(méi)有太大的用處。沒(méi)有子查詢(xún)的INSERT操作不會(huì )創(chuàng )建執行計劃，但沒(méi)有WHERE子句或子查詢(xún)的UPDATE和DELETE操作會(huì )創(chuàng )建執行計劃，因為這些操作必須先找出所要的記錄。
另外，如果你在SQL語(yǔ)句中使用其它類(lèi)型如sequence等，explain也能揭示它的用法。
explain真正的唯一的限制是用戶(hù)不能去解釋其它用戶(hù)的表，視圖，索引或其它類(lèi)型，用戶(hù)必須是所有被解釋事物的所有者，如果不是所有者而只有select權限，explain會(huì )返回一個(gè)錯誤。
聯(lián)系客服
