【優化】Oracle 執行計劃

1，什麼是執行計劃

所謂執行計劃，顧名思義，就是對一個查詢任務，做出一份怎樣去完成任務的詳細方案。舉個生活中的例子，我從珠海要去英國，我可以

選擇先去香港然後轉機，也可以先去北京轉機，或者去廣州也可以。但是到底怎樣去英國划算，也就是我的費用最少，這是一件值得考究

的事情。同樣對於查詢而言，我們提交的SQL僅僅是描述出了我們的目的地是英國，但至於怎麼去，通常我們的SQL中是沒有給出提示資訊

的，是由資料庫來決定的。

　　我們先簡單的看一個執行計劃的對比：

　　SQL> set autotrace traceonly

　　執行計劃一：

　　SQL> select count(*) from t;
　　COUNT(*)
　　----------
　　24815
　　Execution Plan
　　0　　　SELECT STATEMENT ptimizer=CHOOSE
　　1　　0　 SORT (AGGREGATE)
　　2　　1　　 TABLE Access (FULL) OF 'T'

　　執行計劃二：

　　SQL> select count(*) from t;
　　COUNT(*)
　　24815
　　Execution Plan
　　0　　　SELECT STATEMENT ptimizer=CHOOSE (Cost=26 Card=1)
　　1　　0　 SORT (AGGREGATE)
　　2　　1　　 INDEX (FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=26 Card=28180)

　　這兩個執行計劃中，第一個表示求和是通過進行全表掃描來做的，把整個表中資料讀入記憶體來逐條累加;第二個表示根據表中索引，把

整個索引讀進記憶體來逐條累加，而不用去讀表中的資料。但是這兩種方式到底哪種快呢?通常來說可能二比一快，但也不是絕對的。這是一

個很簡單的例子演示執行計劃的差異。對於複雜的SQL(表連線、巢狀子查詢等)，執行計劃可能幾十種甚至上百種，但是到底那種最好呢?

我們事前並不知道，資料庫本身也不知道，但是資料庫會根據一定的規則或者統計資訊(statistics)去選擇一個執行計劃，通常來說選擇的是

比較優的，但也有選擇失誤的時候，這就是這次討論的價值所在。

Oracle優化器模式

　　Oracle優化器有兩大類，基於規則的和基於代價的，在SQLPLUS中我們可以檢視init檔案中定義的預設的優化器模式。

　　SQL> show parameters optimizer_mode
　　NAME　　　　　　　　　　　　　　　　 TYPE　　VALUE
　　optimizer_mode　　　　　　　　　　 string　 CHOOSE
　　SQL>

　　這是Oracle8.1.7 企業版，我們可以看出，預設安裝後資料庫優化器模式為CHOOSE,我們還可以設定為 RULE、

FIRST_ROWS,ALL_ROWS。可以在init檔案中對整個instance的所有會話設定，也可以單獨對某個會話設定：

　　SQL> ALTER SESSION SET optimizer_mode　= RULE;
　　會話已更改。
　　SQL>　ALTER SESSION SET optimizer_mode　= FIRST_ROWS;
　　會話已更改。
　　SQL>　ALTER SESSION SET optimizer_mode　= ALL_ROWS;
　　會話已更改。

　　基於規則的查詢，資料庫根據表和索引等定義資訊，按照一定的規則來產生執行計劃;基於代價的查詢，資料庫根據蒐集的表和索引的

資料的統計資訊(通過analyze 命令或者使用dbms_stats包來蒐集)綜合來決定選取一個資料庫認為最優的執行計劃(實際上不一定最優)。

RULE是基於規則的，CHOOSE表示如果查詢的表存在蒐集的統計資訊則基於代價來執行(在CHOOSE模式下Oracle採用的是 FIRST_ROWS)

，否則基於規則來執行。在基於代價的兩種方式中，FIRST_ROWS指執行計劃採用最少資源儘快的返回部分結果給客戶端，對於排序分頁

頁顯示這種查詢尤其適用，ALL_ROWS指以總體消耗資源最少的方式返回結果給客戶端。

　　基於規則的模式下，資料庫的執行計劃通常比較穩定。但在基於代價的模式下，我們才有更大的機會選擇最優的執行計劃。也由於

Oracle的很多查詢方面的特性必須在基於代價的模式下才能體現出來，所以我們通常不選擇RULE(並且Oracle宣稱從 Oracle 10i版本資料庫

開始將不再支援 RULE)。既然是基於代價的模式，也就是說執行計劃的選擇是根據表、索引等定義和資料的統計資訊來決定的，這個統計

資訊是根據 analyze 命令或者dbms_stats包來定期蒐集的。首先存在著一種可能，就是由於蒐集資訊是一個很消耗資源和時間的動作，尤

其當表資料量很大的時候，因為蒐集資訊是對整個表資料進行重新的完全統計，所以這是我們必須慎重考慮的問題。我們只能在伺服器空

閒的時候定期的進行資訊蒐集。這說明我們在一段時期內，統計資訊可能和資料庫本身的資料並不吻合;另外就是Oracle的統計資料本身也

存在著不精確部分(詳細參考Oracle DOCUMENT)，更重要的一個問題就是及時統計資料相對已經比較準確，但是Oracle的優化器的選擇也

並不是始終是最優的方案。這也倚賴於Oracle對不同執行計劃的代價的計算規則(我們通常是無法知道具體的計算規則的)。這好比我們決定

從香港還是從北京去英國，車票、機票等實際價格到底是怎麼核算出來的我們並不知道，或者說我們現在瞭解的價格資訊，在我們乘車前

往的時候，真實價格跟我們的預算已經發生了變化。所有的因素，都將影響我們的整個開銷。

　　執行計劃穩定效能帶給我們什麼

　　Oracle存在著執行計劃選擇失誤的可能。這也是我們經常遇見的一些現象，比如總有人說我的程式在測試資料庫中跑的很好，但在產

品資料庫上就是跑的很差，甚至後者硬體條件比前者還好，這到底是為什麼?硬體資源、統計資訊、引數設定都可能對執行計劃產生影響。

由於因素太多，我們總是對未來懷著一種莫名的恐懼，我的產品資料庫上線後到底跑的好不好?於是Oracle提供了一種穩定執行計劃的能力

，也就是把在測試環境中的執行良好的執行計劃所產生的OUTLINES移植到產品資料庫，使得執行計劃不會隨著其他因素的變化而變化。

　　那麼OUTLINES是什麼呢?先要介紹一個內容，Oracle提供了在SQL中使用HINTS來引導優化器產生我們想要的執行計劃的能力。這在

多表連線、複雜查詢中特別有效。HINTS的型別很多，可以設定優化器目標(RULE、CHOOSE、FIRST_ROWS、ALL_ROWS)，可以指定表

連線的順序，可以指定使用哪個表的哪個索引等等，可以對SQL進行很多精細的控制。通過這種方式產生我們想要的執行計劃的這些

HINTS,Oracle可以儲存這些HINTS，我們稱之為OUTLINES。通過STORE OUTLINES可以使得我們擁有以後產生相同執行計劃的能力，也

就是使我們擁有了穩定執行計劃的能力。

　　這裡想給出一個附加的說明就是，實際上，我們通過工具改寫SQL，比如使用SQL　EXPERT改寫後的SQL，這些不僅僅是加了HINTS

而且文字都已經發生了變化的SQL，也可以儲存OUTLINES，並可被應用到應用中。但這不是一定生效，我們必須測試檢查是否生效。但由

於就算給了錯誤的OUTLINES,資料庫在執行的時候，也只是忽略過去重新生成執行計劃而不會返回錯誤，所以我們才敢放心的這麼使用。

當然在Oracle文件中並沒有指明可以這樣做，文件中只是說明，如果存在OUTLINES的同時又在SQL中加了HINTS，則會使用OUTLINES而

忽略HINTS。這個功能在LECCO將釋出的產品中會使用這一功能，這樣可以將SQL EXPERT的改寫SQL的能力和穩定執行計劃的能力結合起

來，那麼我們就對不能更改原始碼的應用具有了相當強大的SQL優化能力。

　　也許我們會有疑問，假如穩定了執行計劃，那還蒐集統計資訊幹嗎?這是因為幾個原因造成的，首先，現在的執行計劃對於未來發生了

變化的資料未必就是合適的，存在著當前的執行計劃不滿足未來資料的變化後的效率，而新的統計資訊的情況下所產生的執行計劃也並不

是全部都合理的。那這個時候，我們可以採用新蒐集的統計資訊，但是卻對新統計資訊下不良的執行計劃採用Oracle提供的執行計劃穩定

性這個能力固定執行計劃，這樣結合起來我們可以建立滿意的高效的資料庫執行環境。

　　我們還需要關注的一個東西，Oracle提供的dbms_stats包除了具有蒐集統計資訊的能力，還具有把資料庫中統計資訊(statistics)

export/import的能力，還具有隻蒐集統計資訊而使得統計資訊不應用於資料庫的能力(把統計資訊蒐集到一個特定的表中而不是立即生效)

，在這個基礎上我們就可以把統計資訊export出來再import到一個測試環境中，再執行我們的應用，在測試環境中我們觀察最新的統計信

息會導致哪些執行計劃發生變化(DB EXPERT的Plan Version Tracer是模擬不同環境並自動檢查不同環境中執行計劃變化的工具)，是變好了

還是變差了。我們可以把變差的這一部分在測試環境中使用hints或者利用工具(SQL EXPERT是在重寫SQL這一領域目前最強有力的工具)產

生良好的執行計劃的SQL，利用這些SQL可以產生OUTLINES,然後在產品資料庫應用最新的統計資訊的同時移植進這些OUTLINES。

　　最後說一下我們不得不使用執行計劃穩定效能力的場合。我們假定Oracle的優化器的選擇都是準確的，但是優化器選擇的基礎就是我

們的SQL,這些SQL才從根本上決定了執行效率，這是更重要的一個優化的環節。SQL是基礎(當然資料庫的設計是基礎的基礎)，一個SQL寫

的好不好，就相當於我們同樣是要想去英國，但是我的起點在珠海，你的起點卻在西藏的最邊緣偏僻的一個地方，那不管你做怎樣的最優

路線選擇，你都不如我在珠海去英國所花費的代價小。

2，怎麼生成的

1.Explain plan
explain plan for
select * from aa;
檢視結果：
select * from table(dbms_xplan.display());
2.Autotrace Set timing on --記錄所用時間
Set autot trace --自動記錄執行計劃
3.SQL_TRACE
ORACLE SQL_TRACE

“SQL TRACE”是Oracle提供的用於進行SQL跟蹤的手段，是強有力的輔助診斷工具。在日常的資料庫問題診斷和解決中，“SQL TRACE”是

非常常用的方法。

一般，一次跟蹤可以分為以下幾步：

1、界定需要跟蹤的目標範圍，並使用適當的命令啟用所需跟蹤。

2、經過一段時間後，停止跟蹤。此時應該產生了一個跟蹤結果檔案。

3、找到跟蹤檔案，並對其進行格式化，然後閱讀或分析。

本文就“SQL TRACE”的這些使用作簡單探討，並通過具體案例對SQL_TRACE的使用進行說明。

3，怎麼檢視執行計劃

從Oracle10g開始，可以通過EXPLAIN PLAN FOR檢視DDL語句的執行計劃了。

在9i及以前版本，Oracle只能看到DML的執行計劃，不過從10g開始，通過EXPLAIN PLAN FOR的方式，已經可以看到DDL語句的執行計劃

了。

這對於研究CREATE TABLE AS SELECT、CREATE MATERIALIZED VIEW AS SELECT以及CREATE INDEX，ALTER INDEX REBUILD等語

句有很大的幫助。

舉個簡單的例子，Oracle的文件上對於索引的建立有如下描述：

The optimizer can use an existing index to build another index. This results in a much faster index build.

如果看不到DDL的執行計劃，只能根據執行時間的長短去猜測Oracle的具體執行計劃，但是這種方法沒有足夠的說服力。但是通過DDL的執

行計劃，就使得結果一目瞭然了。

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已建立。

SQL> EXPLAIN PLAN FOR
2 CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);

已解釋。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 3035241083

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 57915 | 3732K| 75 (2)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IND_T_NAME | | | | |
| 2 | SORT CREATE INDEX | | 57915 | 3732K| | |
| 3 | TABLE ACCESS FULL | T | 57915 | 3732K| 41 (3)| 00:00:01 |
-------------------------------------------------------------------------------------

Note
-----
- estimated index size: 5242K bytes

已選擇14行。

SQL> CREATE INDEX IND_T_OWNER_NAME ON T(OWNER, OBJECT_NAME);

索引已建立。

SQL> EXPLAIN PLAN FOR
2 CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);

已解釋。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 517242163

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 57915 | 3732K| 75 (2)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IND_T_NAME | | | | |
| 2 | SORT CREATE INDEX | | 57915 | 3732K| | |
| 3 | INDEX FAST FULL SCAN| IND_T_OWNER_NAME | | | | |
-------------------------------------------------------------------------------------------

Note
-----
- estimated index size: 5242K bytes

已選擇14行。

SQL> SET AUTOT ON
SQL> CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);

索引已建立。

注意，檢視DDL的執行計劃需要使用EXPLAIN PLAN FOR，AUTOTRACE對於DDL是無效的。

4，如何讀懂執行計劃：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'USER_INFO'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'USER_NUM_TABLE'
5 3 INDEX (RANGE SCAN) OF 'PK_USER_INFO' (UNIQUE)
請問以上執行計劃語句是如何看的？語句的執行順序是什麼？
讓我們來解釋一下怎麼看吧,左邊的兩列數字，第一列表示這條計劃的編號，第二列是這條計劃的父計劃的編號；如果一條計劃有子計劃，

那麼先要執行其子計劃;在這個例子中：從第一條編號為0的（SELECT STATEMENT ptimizer=CHOOSE）開始，他有個子計劃1（SORT

(AGGREGATE)），然後1有個子計劃2，2有子計劃3， 3 有子計劃4和5，4是3的第一個子計劃，所以先執行4（TABLE ACCESS (FULL)

OF 'USER_NUM_TABLE'），再執行5(INDEX (RANGE SCAN) OF 'PK_USER_INFO' (UNIQUE))，4和5執行完返回到其父計劃3（NESTED

LOOPS），3把4和5取到的rows進行nested loops，結果再返回到2，再到1排序，再到0select.