ORALCE的執行計劃穩定性

　　什麼是執行計劃
　　
　　所謂執行計劃顧名思義就是對一個查詢任務做出一份怎樣去完成任務的詳細方案舉個生活中的例子我從珠海要去英國我可以選擇先去香港然後轉機也可以先去北京轉機或者去廣州也可以但是到底怎樣去英國劃算也就是我的費用最少這是一件值得考究的事情同樣對於查詢而言我們提交的SQL僅僅是描述出了我們的目的地是英國但至於怎麼去通常我們的SQL中是沒有給出提示信息的是由數據庫來決定的
　　
　　我們先簡單的看一個執行計劃的對比
　　
　　SQL> set autotrace traceonly
　　
　　執行計劃一
　　
　　SQL> select count(*) from t;
　　
　　COUNT(*)
　　
　　
　　
　　Execution Plan
　　
　　　　　SELECT STATEMENT Optimizer=CHOOSE
　　
　　　　　 SORT (AGGREGATE)
　　
　　　　　　 TABLE ACCESS (FULL) OF T
　　
　　執行計劃二
　　
　　SQL> select count(*) from t;
　　
　　COUNT(*)
　　
　　
　　
　　Execution Plan
　　
　　
　　
　　　　　SELECT STATEMENT Optimizer=CHOOSE (Cost= Card=)
　　
　　　　　 SORT (AGGREGATE)
　　
　　　　　　 INDEX (FULL SCAN) OF T_INDEX (NONUNIQUE) (Cost= Card=)
　　
　　這兩個執行計劃中第一個表示求和是通過進行全表掃描來做的把整個表中數據讀入內存來逐條累加第二個表示根據表中索引把整個索引讀進內存來逐條累加而不用去讀表中的數據但是這兩種方式到底哪種快呢？通常來說可能二比一快但也不是絕對的這是一個很簡單的例子演示執行計劃的差異對於復雜的SQL（表連接嵌套子查詢等）執行計劃可能幾十種甚至上百種但是到底那種最好呢？我們事前並不知道數據庫本身也不知道但是數據庫會根據一定的規則或者統計信息（statistics）去選擇一個執行計劃通常來說選擇的是比較優的但也有選擇失誤的時候這就是這次討論的價值所在
　　
　　ORACLE優化器模式
　　
　　ORACLE優化器有兩大類基於規則的和基於代價的在SQLPLUS中我們可以查看init文件中定義的缺省的優化器模式
　　
　　SQL> show parameters optimizer_mode
　　
　　NAME　　　　　　　　　　　　　　　　 TYPE　　VALUE
　　
　　
　　
　　optimizer_mode　　　　　　　　　　 string　 CHOOSE
　　
　　SQL>
　　
　　這是ORACLE 企業版我們可以看出默認安裝後數據庫優化器模式為CHOOSE我們還可以設置為 RULEFIRST_ROWSALL_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 i版本數據庫開始將不再支持 RULE)既然是基於代價的模式也就是說執行計劃的選擇是根據表索引等定義和數據的統計信息來決定的這個統計信息是根據 analyze 命令或者dbms_stats包來定期搜集的首先存在著一種可能就是由於搜集信息是一個很消耗資源和時間的動作尤其當表數據量很大的時候因為搜集信息是對整個表數據進行重新的完全統計所以這是我們必須慎重考慮的問題我們只能在服務器空閒的時候定期的進行信息搜集這說明我們在一段時期內統計信息可能和數據庫本身的數據並不吻合另外就是ORACLE的統計數據本身也存在著不精確部分（詳細參考ORACLE DOCUMENT）更重要的一個問題就是及時統計數據相對已經比較准確但是ORACLE的優化器的選擇也並不是始終是最優的方案這也倚賴於ORACLE對不同執行計劃的代價的計算規則（我們通常是無法知道具體的計算規則的）這好比我們決定從香港還是從北京去英國車票機票等實際價格到底是怎麼核算出來的我們並不知道或者說我們現在了解的價格信息在我們乘車前往的時候真實價格跟我們的預算已經發生了變化所有的因素都將影響我們的整個開銷
　　
　　執行計劃穩定性能帶給我們什麼
　　
　　ORACLE存在著執行計劃選擇失誤的可能這也是我們經常遇見的一些現象比如總有人說我的程序在測試數據庫中跑的很好但在產品數據庫上就是跑的很差甚至後者硬件條件比前者還好這到底是為什麼？硬件資源統計信息參數設置都可能對執行計劃產生影響由於因素太多我們總是對未來懷著一種莫名的恐懼我的產品數據庫上線後到底跑的好不好？於是ORACLE提供了一種穩定執行計劃的能力也就是把在測試環境中的運行良好的執行計劃所產生的OUTLINES移植到產品數據庫使得執行計劃不會隨著其他因素的變化而變化
　　
　　那麼OUTLINES是什麼呢？先要介紹一個內容ORACLE提供了在SQL中使用HINTS來引導優化器產生我們想要的執行計劃的能力這在多表連接復雜查詢中特別有效HINTS的類型很多可以設置優化器目標(RULECHOOSEFIRST_ROWSALL_ROWS)可以指定表連接的順序可以指定使用哪個表的哪個索引等等可以對SQL進行很多精細的控制通過這種方式產生我們想要的執行計劃的這些HINTSORACLE可以存儲這些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代碼但是如果應用程序是第三方開發的或者我們是在別人的基礎
From:http://tw.wingwit.com/Article/program/Oracle/201311/17544.html