SQL調優是很多Oracle DBA和開發人員的重要工作。一個高效的SQL改寫調優，可以大幅度最佳化執行計劃，提高執行效率，進而增強關鍵用例模組的可用性和滿意度。

進行SQL調優中不可缺少的操作就是獲取指定SQL的執行計劃。在目前的Oracle版本中，有很多可以使用的執行計劃獲取方法。本篇就加以總結，供需要的朋友不時之需。

1、方便易用的explain plan

Explain plan命令在Oracle中，可以對後面的SQL語句進行直接的解析，將執行計劃儲存在一個plan_table的中間表中。之後透過dbms_xplan包的方法進行獲取。

ü 確定plan_table的安裝

使用explain plan命令的一個前提就是系統中存在plan_table資料表。如果沒有的話，需要進行指令碼呼叫安裝。

--如果不存在，就生成

SQL> @?/rdbms/admin/catplan.sql

程式包體已建立。

沒有錯誤。

這裡注意兩個細節：

首先，呼叫指令碼中的?表示ORACLE_HOME目錄。如果是使用sqlplus工具，可以直接使用？代指該目錄。其他如PL/SQL Developer第三方工具不支援；

其次，如果是Oracle 10g以上的版本，使用指令碼名稱為catplan.sql。如果是如9i的版本，使用指令碼名稱為utlxplan.sql。如果在高版本Oracle上使用低版本的plan_table結構，可能在生成執行計劃中報錯“Plan Table version too old”錯誤。

ü 使用explain plan for命令生成執行計劃並顯示

SQL> set linesize 10000;

SQL> set wrap off;

SQL> set pagesize 10000;

SQL> explain plan for select * from scott.emp where empno=7839;

已解釋。

之後使用dbms_xplan工具包將生成的執行計劃展示出。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2949544139

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

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

| 0 | SELECT STATEMENT | | 1 | 35 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 35 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("EMPNO"=7839)

已選擇14行。

該語句顯示出索引執行計劃。

ü 顯示詳細執行計劃資訊

上面直接呼叫，是顯示出分析的SQL最簡單的執行計劃。可以透過設定format引數，顯示出關於計劃的更詳細資訊。

SQL> select * from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

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

Plan hash value: 2949544139

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

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

| 0 | SELECT STATEMENT | | 1 | 35 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 35 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1 / EMP@SEL$1

2 - SEL$1 / EMP@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

INDEX(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))

OUTLINE_LEAF(@"SEL$1")

ALL_ROWS

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

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

2 - access("EMPNO"=7839)

Column Projection Information (identified by operation id):

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

1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],

"EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7],

"EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]

2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]

已選擇41行。

新增了format引數，Oracle將更加詳細的執行計劃資訊返回，包括Outline資訊、結果集合對映等內容。

ü Explain plan for細節

Explain plan for使用比較方便，特別是可以支援在pl/sql developer等第三方開發工具中使用的特性，比較吸引人。不過，explain plan在使用的時候，要注意一些潛在問題：

首先，explain plan for是單純對SQL語句進行最佳化器分析，獲取產生到的執行計劃。這個過程中，並沒有真正執行。所以，生成的執行計劃有時候會有bug，而且進行統計的資訊情況沒有autotrace高；

其次，explain plan for由於只是對執行計劃進行估計。所以在有繫結變數的SQL時，生成的執行計劃並不準確；

2、 獲取“剛剛”的執行計劃display_cursor

使用dbms_xplan包，還可以獲取剛剛執行過的SQL執行計劃資訊。

SQL> select * from scott.emp where empno=7900;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7900 JAMES CLERK 7698 03-12月-81 950 30

SQL> select * from table(dbms_xplan.display_cursor); //獲取剛剛的執行計劃；

PLAN_TABLE_OUTPUT

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

SQL_ID 66nkfdw21rc9j, child number 0

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

select * from scott.emp where empno=7900

Plan hash value: 2949544139

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

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

| 0 | SELECT STATEMENT | | | | 1 (100)| |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 35 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| |

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

Predicate Information (identified by operation id):

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

2 - access("EMPNO"=7900)

已選擇19行。

直接呼叫display_cursor，不指定sql_id，就可以將剛剛當前會話執行的SQL命令執行計劃從library cache中抽取出來。

注意：display_cursor也支援format引數，可以進行詳細執行計劃資訊的抽取。

此外還有一點，就是這種方法獲取剛剛執行過的SQL執行計劃，只能在sqlplus或者sqlplusw上使用。如果是pl/sql developer等第三方工具，可能不適用。

(注意：在pl/sql developer下使用存在問題)

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

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

SQL_ID 9m7787camwh4m, child number 0

begin :id := sys.dbms_transaction.local_transaction_id; end;

NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0

Please verify value of SQL_ID and CHILD_NUMBER;

It could also be that the plan is no longer in cursor cache (check v$sql_p

8 rows selected

3、autotrace工具使用

本人以為autotrace工具是獲取執行計劃資訊較為完整的工具。優勢在於使用該工具可以獲取到執行SQL過程中的讀寫、呼叫遞迴和排序分組消耗。

在之前的Blog中，筆者已經撰寫過一篇關於autotrace較為詳細的文章，有興趣的讀者可以參考：《Autotrace工具使用——小工具，大用場》（http://space.itpub.net/17203031/viewspace-686535）。

在下篇中，我們會介紹直接從shared_pool中抽取執行計劃，和從AWR報告庫中抽取。最後介紹使用10046事件跟蹤執行計劃。

介紹幾種獲取SQL執行計劃的方法（上）

相關文章