oracle執行計劃的使用(EXPLAIN)

對於sql執行的小量高低。我們可以透過執行計劃的資訊基本上可以進行分析檢視該SQL語句執行的時間。連線順序及浪費的資料庫資源等資訊，從而判斷該SQL語句執行的效率如何，下面就簡單的介紹一下執行計劃的使用

[@more@]對於sql執行的小量高低。我們可以透過執行計劃的資訊基本上可以進行分析檢視該SQL語句執行的時間。連線順序及浪費的資料庫資源等資訊，從而判斷該SQL語句執行的效率如何，下面就簡單的介紹一下執行計劃的使用

　　2. Explain使用

　　Oracle RDBMS執行每一條SQL語句，都必須經過Oracle最佳化器的評估。所 以，瞭解最佳化器是如何選擇（搜尋）路徑以及索引是如何被使用的，對最佳化SQL語句有很大的幫助。Explain可以用來迅速方便地查出對於給定SQL語句中的查詢資料是如何得到的即搜尋路徑（我們通常稱為Access Path）。從而使我們選擇最優的查詢方式達到最大的最佳化效果。

　　2.1. 安裝

　　要使用執行計劃首先需要執行相應的指令碼。

　　使用Explain工具需要建立Explain_plan表，這必須先進入相關應用表、檢視和索引的所有者的帳戶內。Oracle的介質中包含有執行此項工作的SQL源程式，例如：

　　ORA_RDBMS： XPLAINPL.SQL （VMS）

　　$ORACLE_HOME/rdbms/admin/utlxplan.sql （UNIX）

　　該指令碼後會生成一個表這個程式會建立一個名為plan_table的表，表結構如下：

　　我們簡單的介紹一下主要的欄位含義：

欄位名 欄位型別 含義
STATEMENT_ID VARCHAR2(30) explain PLAN 語句中所指定的最優STATEMENT_ID 引數值, 如果在EXPLAN PLAN語句中沒有使用SET STATEMENT_ID，那麼此值會被設為NULL。
REMARKS VARCHAR2(80) 與被解釋規劃的各步驟相關聯的註釋最長可達80 位元組
OPERATION VARCHAR2(30) 各步驟所執行內部操作的名稱在某條語句所產生的第一行中該列的可能取值如下DELETE STATEMENT INSERT STATEMENT SELECT STATEMENT UPDATE STATEMENT
OPTIONS VARCHAR2(30) 對OPERATION 列中所描述操作的變種
OBJECT_NODE VARCHAR2(128) 用於訪問物件的資料庫連結database link 的名稱對於使用並行執行的本地查詢該列能夠描述操作中輸出的次序
OBJECT_OWNER VARCHAR2(30) 對於包含有表或索引的架構schema 給出其所有者的名稱
OBJECT_NAME VARCHAR2(30) 表或索引的名稱
OBJECT_INSTANCE INTEGER 根據物件出現在原始original 語句中的次序所給出的相應次序編號就原始的語句文字而論其處理順序為自左至右自外向內景象擴張view
OBJECT_TYPE VARCHAR2(30) 用於提供物件描述性資訊的修飾符例如索引的NON-UNIQUE
OPTIMIZER VARCHAR2(255) 當前最佳化程式的模式
ID INTEGER 分配給執行規劃各步驟的編號
PARENT_ID INTEGER 對ID 步驟的輸出進行操作的下一個執行步驟的ID
POSITION INTEGER 對於具有相同PARENT_ID 的步驟其相應的處理次序
COST INTEGER 根據最佳化程式的基於開銷的方法所估計出的操作開銷值對於使用基於規則方法的語句該列為空該列值沒有特定的測量單位它只是一個用於比較執行規劃開銷大小的權重值
CARDINALITY INTEGER 根據基於開銷的方法對操作所訪問行數的估計值
BYTES INTEGER 根據基於開銷的方法對操作所訪問位元組的估計

　　2.2. 使用

　　2.2.1. 常規使用

　　常規使用語法：

explain PLAN [ SET STATEMENT_ID [=] < string literal > ]
[ INTO < table_name > ]
FOR < sql_statement >
其中：
STATEMENT_ID是一個唯一的字串，把當前執行計劃與儲存在同一PLAN表中的其它執行計劃區別開來。
TABLE_NAME是plan表名，它結構如前所示，你可以任意設定這個名稱。
SQL_STATEMENT是真正的SQL語句。
如：
SQL> explain plan set statement_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 FROM tb_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

　　執行下面語句就可以檢視該語句執行的執行計劃:

SQL> SELECT A.OPERATION,OPTIONS,OBJECT_NAME,OBJECT_TYPE,ID,PARENT_ID
2 FROM PLAN_TABLE a
3 WHERE STATEMENT_ID='test1'
4 ORDER BY Id;
OPERATION OPTIONS OBJECT_NAME OBJECT_TYPEID PARENT_ID
---------------- --------------------------------------------- ------------- ----------
SELECT STATEMENT 0
WINDOW SORT 1 0
FILTER 2 1
NESTED LOOPS OUTER 3 2
NESTED LOOPS OUTER 4 3
NESTED LOOPS 5 4
TABLE ACCESS FULL TB_SOC_PACKDEALDATA 6 5
TABLE ACCESS BY INDEX ROWID TB_SOC_LEVYDETAILDATA 7 5
INDEX RANGE SCAN IND_DATAID_LEVSOC NON-UNIQUE 8 7
TABLE ACCESS BY INDEX ROWID TB_LVY_TAXDATABILLMAP 9 4
INDEX RANGE SCAN TBLVYTAXDATABIL_DATAFROMPOINTE NON-UNIQUE 10 9
TABLE ACCESS BY INDEX ROWID TB_LVY_BILLDATA 11 3
INDEX UNIQUE SCAN TBLVYBILLDATA_BILLDATAID UNIQUE

　　2.2.2. 自動顯示使用

　　在SQLPLUS中自動跟蹤顯示執行計劃及相關資訊
　　SQL>set timing on --顯示執行時間
　　SQL>set autorace on ?C顯示執行計劃
　　SQL>set autorace on ?C顯示執行計劃
　　SQL>set autotrace traceonly ?C只顯示執行計劃即不顯示查詢出來的資料

　　設定完畢後執行SQL語句就會顯示執行計劃資訊及相應的統計資訊(需要設定顯示該選項)

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 and a.pages = 123
5 and a.remarkid = b.remarkid
6 AND A.REMARKID IS NOT NULL
7 and b.declaredocid = t.declaredocid;
NVL(SUM(T.TAXDUE),0)
--------------------
0

　　已用時間: 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=1 Bytes=110)
3 2 MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=74)
4 3 TABLE ACCESS (FULL) OF 'TB_LVY_SBZS100' (Cost=2 Card =1 Bytes=31)
5 3 BUFFER (SORT) (Cost=2 Card=1 Bytes=43)
6 5 TABLE ACCESS (FULL) OF 'TB_LVY_DECLAREDOC' (Cost=2 Card=1 Bytes=43)
7 2 TABLE ACCESS (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 --迴圈遞迴次數
0 db block gets―請求的資料塊在buffer能滿足的個數
6675 consistent gets --邏輯IO用於讀表並計算行數, 資料請求總數在回滾段Buffer中
45 physical reads ?C從磁碟讀到Buffer Cache資料塊數量
0 redo size ?C產生的redo日誌大小
217 bytes sent via SQL*Net to client
276 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>

　　如果6675 consistent gets --邏輯IO用於讀表並計算行數, 資料請求總數在回滾段Buffer中

　　45 physical reads ?C從磁碟讀到Buffer Cache資料塊數量的數值比較小則該語句對對資料庫的效能比較高。

　　2.2.3. PL/SQL和TOAD中使用

　　如果在PL/SQL中使用選擇要查詢語句顯示執行計劃，則只需要SQL WINDOWS 視窗裡面輸入要查詢的SQL語句，然後選擇按鍵F5或者在選單TOOLS?D?D>Explain Plan 選單按鍵就可以在執行計劃視窗檢視該語句的執行計劃。

　　在TOAD語句中在執行當前的SQL視窗中選擇下方的Explain PlanTAB頁即可以檢視要執行語句的執行計劃資訊。

　　2.3. 限制

　　雖然任何SQL語句都可以用explain解釋，但對於沒有查詢的INSERT，UPDATE，DELETE操作來說，這個工具並沒有太大的用處。沒有子查詢的INSERT操作不會建立執行計劃，但沒有WHERE子句或子查詢的UPDATE和DELETE操作會建立執行計劃，因為這些操作必須先找出所要的記錄。

　　另外，如果你在SQL語句中使用其它型別如sequence等，explain也能揭示它的用法。

　　explain真正的唯一的限制是使用者不能去解釋其它使用者的表，檢視，索引或其它型別，使用者必須是所有被解釋事物的所有者，如果不是所有者而只有select許可權，explain會返回一個錯誤。