ORALCE的執行計劃穩定性(zt)

什麼是執行計劃

        所謂執行計劃，顧名思義，就是對一個查詢任務，做出一份怎樣去完成任務的詳細方案。舉個生活中的例子，我從珠海要去英國，我可以選擇先去香港然後轉機，也可以先去北京轉機，或者去廣州也可以。但是到底怎樣去英國划算，也就是我的費用最少，這是一件值得考究的事情。同樣對於查詢而言，我們提交的SQL僅僅是描述出了我們的目的地是英國，但至於怎麼去，通常我們的SQL中是沒有給出提示資訊的，是由資料庫來決定的。

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

SQL> set autotrace traceonly

執行計劃一：

SQL> select count(*) from t;

  COUNT(*)

----------

     24815

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'T'

執行計劃二：

SQL> select count(*) from t;

  COUNT(*)

----------

     24815

Execution Plan

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

   0      SELECT STATEMENT Optimizer=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寫的好不好，就相當於我們同樣是要想去英國，但是我的起點在珠海，你的起點卻在西藏的最邊緣偏僻的一個地方，那不管你做怎樣的最優路線選擇，你都不如我在珠海去英國所花費的代價小。由於這個原因，通常如果是我們自己設計程式，我們可以嘗試著修改SQL程式碼，但是，如果應用程式是第三方開發的，或者我們是在別人的基礎上進行的二次開發，比如我們的ERP系統是SAP的，那就算我們在資料庫中發現SQL有嚴重的效率問題，我們也無力對應用程式進行修改。但是，我們可以在資料庫中捕獲這些SQL，然後為這些SQL產生一個良好的執行計劃的OUTLINES，在利用執行計劃穩定性來把SQL和產生的良好執行計劃的OUTLINES做繫結。這樣就可以在不修改原始碼的基礎上提高程式的執行效率。這也是惟一的辦法。

  怎麼使用執行計劃穩定性

我們先以一個最簡單的例子演示怎麼使用執行計劃穩定性

首先我們得建立一個category,把我們所想穩定下來的執行計劃放在這個category下，這是一種執行計劃的分類，我們可以建立很多category，但是我們的每個session只能選擇其中一個category以使用其中的定製好的執行計劃。

通常我們採用一種最簡單的方式來進行這個過程：

首先，為了生成執行和觀察執行計劃，我們建立一個儲存執行計劃的表。

SQL> @E:oracleora81RDBMSADMINutlxplan;

表已建立。

這個指令碼utlxplan.sql 在 $ORACLE_HOMERDBMSADMIN目錄下

然後建立一個實驗表。

SQL> create table t as select * from all_objects;

表已建立。

SQL> create index t_index on t(object_id);

索引已建立。（注意我們建立索引的欄位是非空欄位）

這裡開始開啟執行計劃跟蹤。

SQL> set autotrace on

SQL> select count(*) from t;

  COUNT(*)

----------

     30658

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'T'

Statistics

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

          0  recursive calls

          0  db block gets

        422  consistent gets

        418  physical reads

          0  redo size

        370  bytes sent via SQL*Net to client

        425  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

這裡我們可以看見生成的執行計劃,查詢進行了全表掃描，後面其實還跟了一系列的查詢執行的時候的統計資訊，但由於這不在我們的討論範圍之內，所以我們將忽略這些資訊。

然後我們蒐集這個表的統計資訊，之後在執行查詢發現執行計劃已經發生了變化，不再是全表掃描而是根據索引進行掃描。

SQL> analyze table t compute statistics;

表已分析。

SQL> select count(*) from t;

  COUNT(*)

----------

     30658

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1)

   1    0   SORT (AGGREGATE)

   2    1     INDEX (FAST FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=8Card=30658)

從這裡開始，我們將嘗試建立一個category。

一直到會話結束或者set create_stored_outlines = false 之間的所有查詢，我們都將為這些查詢生成並保留一個執行計劃，如下，這些執行計劃儲存在my_demo這個分類中。

SQL> alter session set create_stored_outlines = my_demo;

會話已更改。

SQL> select count(*) from t;

  COUNT(*)

----------

     30658

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1)

   1    0   SORT (AGGREGATE)

   2    1     INDEX (FAST FULL SCAN) OF 'T_INDEX' (NON-UNIQUE) (Cost=8Card=30658)

SQL> alter session set  create_stored_outlines = false;

會話已更改。

在這裡我們刪除表的統計資訊，然後再執行查詢看看。

SQL> analyze table t delete statistics;

表已分析。

SQL> select count(*) from t;

  COUNT(*)

----------

     30658

Execution Plan

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

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'T'

我們發現這個時候執行計劃已經恢復成全表掃描。

於是我們嘗試使session使用我們生成的category在執行查詢。

SQL>  alter session set use_stored_outlines = my_demo;

會話已更改。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-84225/，如需轉載，請註明出處，否則將追究法律責任。