832
Oracle數據庫12c release 2優化器詳解
序言：優化器是Oracle數據庫最引人入勝的部件之一，因為它對每一個SQL語句的處理都必不可少。優化器為每個SQL語句確定最有效的執行計劃，這是基於給定的查詢的結構，可用的關於底層對象的統計信息，以及所有與優化器和執行相關的特性。
本文來自Oracle 白皮書翻譯（譯者：蘇旭輝 newkid），介紹了在Oracle數據庫12c第二版中與優化器和統計信息相關的所有新特性並且提供了簡單的，可再現的例子，使得你能夠更容易地熟悉它們，尤其是當你從早先的版本進行遷移的時候。它還概括了已有的功能是如何被增強以改善性能和易管理性。
有些Oracle優化器特性已經被從本文移除，並在它們自己的文章中被討論。具體來說，它們是：
-
優化統計信息和優化統計信息顧問
-
SQL計劃管理
-
近似查詢處理
為了得知Oracle優化器的全貌，我們建議你結合參考文獻部分列出的相關文件來閱讀本文。
一、自適應查詢優化
到目前為止，Oracle 12c數據庫中最大的變化是自適應查詢優化。自適應查詢優化是這樣的一組功能，它們使得優化器能夠對執行計劃進行實時調整，並且發現能夠導致更佳的統計信息的額外信息。當現有的統計信息不足以產生一個最佳的計劃，這種新方法是極其有用的。自適應查詢優化包括兩個方麵：
自適應計劃，它著重於改善一個查詢的執行；
自適應統計信息，它利用額外的信息來改善查詢執行計劃。
(圖1:新的自適應查詢優化功能的構成組件)
Oracle數據庫12c第二版默認啟用的自適應功能與Oracle數據庫12c第一版不同。詳細信息請參見下麵的“初始化參數”部分。
二、自適應計劃
優化器在某些條件下會選擇自適應計劃; 例如，當查詢包括連接和複雜謂詞，使得準確地估計基數變得很困難。自適應計劃使得優化器能夠把一個語句的計劃推遲到執行的時候才確定。優化器在它所選擇的計劃（缺省計劃）中植入統計收集器，從而在運行的時候，它能夠判斷基數估算與計劃的操作所實際看到的行數是否有很大的偏差。如果有顯著的區別，那麼這個計劃或者計劃的一部分就會被自動調整，以避免不理想的性能。
三、自適應的連接方式
通過為計劃中的某些分支預先確定多個子計劃，優化器能夠實時調整連接方式。例如，在圖2中優化器的缺省計劃為orders(訂單)和 products(產品)表之間的連接選定的是嵌套循環連接，通過對products表的索引讀取。另一個可選的子計劃也同時被確定，它允許優化器將連接方式切換到哈希連接。在候選計劃中products是通過全表掃描來讀取的。
在初次執行的時候，統計收集器收集了關於這次執行的信息，並且將一部分進入到子計劃的數據行緩存起來。
優化器會確定要收集哪些統計信息，以及如何根據統計的不同值來確定計劃。它會算出一個“拐點”，兩個計劃選項在這個值是一樣好的。
例如，如果當orders表的掃描產生的行數少於10行，則嵌套循環連接是最佳，當 orders 表的掃描產生的行數多於10行，則哈希連接是最佳，那麼這兩個計劃的拐點就是10。優化器會算出這個值，並且配置一個緩存統計收集器，使得它緩存並且計數至10行為止。如果掃描產生了至少10行，那麼連接方式就被確定為哈希連接；否則，它就被確定為嵌套循環連接。在圖2中，統計信息收集器正在監控和緩存來自orders表全掃描的數據行。基於從統計信息收集器中看到的信息，優化器會決定使用哪個子計劃。
在這個例子中，哈希連接被選中，因為來自orders表的行數大於優化器最初的估計。
(圖2:orders 和 products 表之間的連接的自適應執行計劃。左邊是缺省計劃，右邊是選中的計劃)
優化器能夠從嵌套連接切換到哈希連接，反之亦然。可是，如果初始選中的連接方法是排序合並連接，則自適應不會發生。
在缺省情況下，explain plan命令隻會顯示優化器選定的初始（缺省）計劃。而DBMS_XPLAN.DISPLAY_CURSOR函數顯示的是查詢實際使用的計劃。
(圖3: Explain plan 和 DBMS_XPLAN.DISPLAY_CURSOR 為圖2所示的情形所輸出的計劃)
為了看到自適應計劃中所有的操作，包括統計收集器的位置，你必須在DBMS_XPLAN函數中指定額外的格式參數'adaptive'。在這個模式下，計劃的id欄會出現一個額外的“-”記號，指明在計劃中未被采用（非激活）的操作。
(圖4: 在DBMS_XPLAN.DISPLAY_CURSOR中使用'ADAPTIVE'格式參數得到的完整自適應計劃)
如果在“Plan”下拉框中選擇“Full”，SQL監控工具(SQL Monitor)將顯示所有的操作。計劃的非活動部分變灰（參見圖5）。如果點擊“Plan Note”圖標，一個彈出框會被顯示，確認該計劃是一個自適應計劃。
(圖5: SQL Monitor顯示一個自適應計劃)
四、自適應並行分配方法
當一個SQL語句以並行模式執行時，某些操作，例如排序，聚合和連接，它們要求在執行語句的並行服務進程之間重新分配數據。優化器所用的分配方法取決於操作方式，涉及到的並行服務進程數，以及預期的行數。如果優化器對行數估算不準確，那麼選中的分配方法就可能不理想，並可能導致某些並行服務進程得不到充分利用。
隨著新的自適應分配方法"混合型哈希"(HYBRID HASH)的引入，優化器可以將分配方法延遲到執行的時候才確定，此時它對於涉及到的數據行數就有了更多的信息。一個統計收集器被插入到操作之前，如果緩存的數據的實際行數比閾值小，則分配方法將從哈希(HASH)切換到廣播(BROADCAST)。然而，如果緩衝的行數達到了閾值，則分配方法將會是哈希(HASH)。閾值的定義為並行度的兩倍。
圖6顯示了SQL監控工具中的一個執行計劃的例子，它是一個以並行模式執行的EMP和DEPT表之間的連接。一組並行服務進程（生產者，即粉紅色圖標）掃描兩個表並且將數據行送給另一組並行服務進程（消費者，即藍色圖標），該組進程是連接的真正執行者。優化器決定采用混合型哈希(HYBRID HASH)的分配方法。在這個連接中訪問的第一個表是DEPT表。來自DEPT表的數據行被緩存在統計收集器中，見計劃的第六行，直至閾值被超越，或者最後一行被獲取。在那時優化器將會決定采用何種分配方法。
(圖6: SQL監控工具中的一個EMP和DEPT表之間的連接的執行計劃，它使用了自適應分配方法)
要了解在運行時選擇哪個分配方法，查找此信息的最簡單方法是查看SQL監控工具中的OTHER列。 此列在PX SEND HYBRID HASH的行中顯示一個望遠鏡圖標。當你點擊這個圖標時，你可以看到運行時使用的分配方法。
(圖7: 混合型哈希分配法)
這個對話框中顯示的自適應分配方法有三種可能的值：6 = BROADCAST(廣播), 5 = ROUND-ROBIN(循環製), 16 = HASH(哈希)。
五、自適應位圖索引裁剪
當優化器生成了一個星型轉換計劃，它就必須選擇正確的位圖索引組合，以盡可能有效地減少相關的ROWID集合。如果有多個索引，其中的一些可能不會顯著地減少ROWID集合，但是仍然會在查詢執行期間引入可觀的處理成本。自適應計劃因此被用來裁剪索引，這些索引無法顯著地降低過濾匹配的行數。
在帶有adaptive關鍵字的SQL執行計劃中，DBMS_XPLAN.DISPLAY_CURSOR 將會顯示自適應的位圖裁剪，其方式類似於圖3所示的示例。
例如，考慮以下SQL執行計劃，它顯示出位圖索引CAR_MODEL_IDX被裁剪掉：
(圖8: 自適應位圖索引裁剪的例子)
六、自適應統計信息
優化器所確定的執行計劃的質量取決於可用的統計信息的質量。然而，有些查詢謂詞變得過於複雜，以至於無法單獨依賴於基表的統計信息，而現在優化器能夠用自適應統計信息來進行增補。
七、動態統計信息
在一個SQL語句的編譯過程中，優化器會判斷已有的統計信息是否足以產生一個好的執行計劃，或者它該考慮使用動態取樣。動態取樣是為了補償缺失或者不充足的統計信息，如果不這麼做，這樣的信息可能導致非常糟糕的計劃。在查詢中的一個或者多個表的統計信息都缺失的情況下，優化器在優化語句之前就會在這些表上使用動態取樣來收集基本的統計信息。這種情況下收集的統計信息在質量（因為是取樣）和完整性上都不如使用DBMS_STATS包收集到的信息。
從Oracle數據庫 12c第一版開始, 動態取樣被強化為動態統計信息。動態統計信息允許優化器強化現有的統計信息以獲取更加精確的基數估算，不僅僅是為單表的訪問，而且也包含連接和分組(GROUP BY)謂詞。並且，從Oracle數據庫12c第一版開始, 初始化參數OPTIMIZER_DYNAMIC_SAMPLING引入了新的取樣級別11。11級使得優化器能夠自動為任何SQL語句使用動態統計信息，即使所有基本的表統計信息都已經存在。優化器做出使用動態統計的決定，是基於所用謂詞的複雜性，和已經存在的基礎統計信息，以及預期的SQL語句總執行時間。例如，之前的優化器在某些情況下會使用猜測的方法，比如帶有LIKE謂詞和模煳匹配的查詢，而現在則會啟用動態統計信息。
缺省的動態取樣級別是2, 因此，當級別設置為11時，動態取樣啟用的頻率很可能大大超過以往。這會增加語句的解析時間。為了將對性能的影響減到最低，在Oracle數據庫12c第一版中，動態取樣查詢的結果將會被保留在數據庫的服務器結果緩存，從Oracle數據庫12c第二版開始會保留在SQL計劃指令的知識庫中。這就允許多個SQL語句來共享一組由動態取樣收集的統計信息。下麵將要更詳細討論的SQL計劃指令也會利用這種級別的動態取樣。
八、自動重優化
在一個SQL語句的首次執行期間，一個執行計劃如常被生成。在優化的過程中，有些已知的低質量估算種類（例如，對缺少統計信息的表的估算，或者帶有複雜謂詞的表的估算）會被做記號，對產生的遊標的監控會被開啟。如果係統開啟了對一個遊標的反饋監控，計劃中的基數估算就被用來與執行過程中看到的實際基數進行比較。如果估算值被發現和實際基數有顯著區別，則優化器會在下次執行尋求可替換的計劃。優化器會利用前一次執行收集到的信息來幫助確定這個替換計劃。優化器可能將一個查詢重新優化好幾次，每次都學習並且進一步改善計劃。Oracle 12c數據庫支持多種不同形式的重優化。
九、統計信息反饋
統計信息反饋(以前稱為基數反饋，cardinality feedback)是重優化的一種形式，它自動為那些反複執行的具有基數估算誤差的查詢改善計劃。在一個SQL語句的首次執行期間，優化器生成了一個執行計劃，並且決定是否應該為遊標啟動統計信息反饋監控器。統計信息反饋在如下的情形被啟用：缺失統計信息的表，表上有多個合取或者析取謂詞(指用AND或者OR連接起的謂詞), 謂詞包含有複雜操作，使得優化器不能準確估算基數。
在查詢結束之時，優化器將它原來的基數估算和在執行期間觀測到的實際基數進行比較，如果估算值和實際值有顯著差異，它會將正確的值存儲起來供後續使用。它還會創建一個SQL計劃指令，使得其他的SQL語句也能受益於這次初始執行中學到的信息。如果查詢再次執行，優化器會使用糾正過的基數估算值，而不是它原先的估算值，來確定執行計劃。如果它發現初始的估算值是正確的，則不會采取任何額外的措施。在第一次執行之後，優化器關閉了統計信息反饋的監控。
圖9顯示了一個SQL語句受益於統計信息反饋的例子。在這個兩表連接的初次執行中，由於customers表上有多個相關的單列謂詞，優化器將基數低估了8倍。
(圖9: 一個受益於自動重優化的統計信息反饋的SQL語句初次執行的情況)
當估計值和實際返回的行數有很大的差別，這個遊標被標記為IS_REOPTIMIZIBLE（可重優化）並且不會被再次使用。IS_REOPTIMIZIBLE屬性指明這個SQL語句應該在下一次執行的時候被硬解析，所以優化器能夠使用在初次執行時記錄下來的統計信息來確定一個更佳的執行計劃。
(圖10: 在初次執行的統計信息與原有的基數估算有顯著差異之後，遊標被標識為可重優化)
一個SQL計劃指令同樣被創建，這是為了確保下次如果在customers表使用了相似的謂詞的SQL語句被執行，優化器會注意到這些列之間的相關性。
在第二次執行，優化器使用了來自初次執行的統計信息來確定一個具有不同連接順序的新計劃。在生成執行計劃的過程中對統計信息反饋的使用情況被注明於執行計劃下麵的備注部分。
(圖11: 新生成的計劃使用來自初次執行的統計信息)
新計劃沒有標識為IS_REOPTIMIZIBLE，所以它將被這個SQL語句的所有後續執行所使用。
(圖12: 新生成的計劃標識為不可重優化)
十、性能反饋
重優化的另一種形式為性能反饋，當自動並行度(AutoDOP)在自適應模式下被啟用，這會有助於改善重複執行的SQL語句的並行度的選擇（參見第25頁上的OPTIMIZER_ADAPTIVE_STATISTICS內容）。
（注：關於自動並行度請參見參考文章5 “Oracle數據庫12c並行執行基礎知識”）
當自動並行度(AutoDOP)在自適應模式下被啟用，在一個SQL語句的首次執行過程中，優化器會決定語句是否應該在並行模式下執行；如果是，應該使用哪種並行度。並行度的選擇是基於語句的預計性能表現。對於優化器決定並行執行的任何SQL語句，額外的性能監視器同樣在初次執行的時候被打開，
在初次執行結束時，優化器選擇的並行度，和根據語句初次執行期間的實際性能統計信息（例如CPU時間）計算出來的並行度，被加以比較。如果兩個值有顯著差別，那麼語句被標識為可重優化，初次執行的性能統計信息被作為反饋存儲起來，以幫助為後續的執行計算出一個更加合適的並行度。
如果性能反饋被用於一個SQL語句，它會在計劃下方的備注部分被注明，如圖13所示。
(圖13: 一個SQL語句的執行計劃，性能反饋發現它串行執行會更好)
文章轉自數據和雲公眾號，原文鏈接
最後更新：2017-07-18 10:02:53