ORALCE的執行計劃穩定性
2024-07-21 02:34:35
供稿：網友
什么是執行計劃
所謂執行計劃，顧名思義，就是對一個查詢任務，做出一份怎樣去完成任務的具體方案。舉個生活中的例子，我從珠海要去英國，我可以選擇先去香港然后轉機，也可以先去北京轉機，或者去廣州也可以。 但是到底怎樣去英國劃算，也就是我的費用最少，這是一件值得考究的事情。同樣對于查詢而言，我們提交的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代碼，但是，假如應用程序是第三方開發的，或者我們是在別人的基礎
