ORACLE 12C 優化器的一些新特性總結（一）

在對 oracle 優化器的學習認知過程中，我逐漸發現優化器是 Oracle 資料庫非常神奇的元件，它直接決定著一個資料庫產品是否工作是否能高效。優化器為每個 SQL 語句確定最有效的執行計劃。 ORACLE 隨著每個新版本的釋出，優化器都會進化，利用新功能以及新的統計資訊來生成更好的執行計劃。 Oracle 12c 資料庫在優化器方面確實做出了很大進步。

本文將 Oracle 12c 資料庫中優化器和統計相關的新特性進行總結，以便能夠更容易地熟悉它們。

一、自適應查詢優化

在 Oracle 12c 資料庫眾多特性中，自適應查詢優化是較大的功能變化了。它使優化器能夠對執行計劃進行實時調整。當現有的統計資訊不足以產生一個優化的計劃，它能夠及時的找到導致更佳的統計資訊的額外資訊。自適應查詢優化包括兩個方面：

自適應計劃，它著重於改善一個查詢的初次執行；

自適應統計資訊，它為後續的執行提供了額外的資訊。

 

( 自適應查詢優化功能的元件 )

1 ．自適應計劃

自適應計劃讓 oracle 優化器在解析 SQL 語句後延遲去產生一個最終的執行計劃，直到語句執行的時候才決定最終的執行計劃。優化器會預先生成多個執行計劃並預設分配一個預設的執行計劃，並在這個執行計劃中植入統計收集器，從而在語句執行的時候，通過統計收集器來判斷預先給出的基數估算與執行的實際結果的行數是否存在很大的偏差，如果這個偏差很大，那麼，這個執行計劃在 SQL 語句的首次執行時就能被自動調整，從而避免錯誤的執行計劃造成的效能問題。

1.1   自適應的連線方式

O racle 12c 優化器在生成執行計劃時，在某些執行路徑上預先分配多個子的執行計劃，即可能是不同的連線方式或不同的訪問表的方式，從而使優化器能夠實時地去調整連線的方式。

以下通過實驗例子來解析這一過程：

分別建立兩個表，並插入一定的資料。

 

執行一條關聯查詢該兩個表的語句，並生成相應執行計劃：

 

在執行計劃的提示中會說明這是一條自適應的執行計劃，且這是當前情況下所選擇的最終執行計劃，為了看到自適應計劃中所有的操作，包括統計收集器的位置，我們在 DBMS_XPLAN 函式中指定額外的格式引數 '+adaptive' 。在這個模式下， id 欄會出現一個額外的 (-) 記號，指明在計劃中未被採用（非啟用）的操作。

 

( 在 DBMS_XPLAN.DISPLAY_CURSOR 中使用 '+adaptive' 格式引數得到的完整自適應計劃 )

 

在以上執行計劃中顯示了非活動的執行計劃訪問路徑，非活動的的執行計劃時候 tab 1 表與 tab 2 表進行 hash  join ，而依照目前的統計情況以及實際執行情況來看， tab 1 表與 tab 2 表選擇 nest  loop 的方式看來更優，因此優化器選了 nest  loop 的連線方式。

現在我們繼續往該兩個表中插入更多資料，使當前的執行計劃並不符合實際的執行情況：

 

現在表中有更多的行匹配初始過濾條件，從 1 行的驅動集到 10001 行的驅動集。此 時 ，巢狀迴圈聽起來不那麼 優 越。而此 時 ，我 們 依舊使用舊的 統計 資訊，我 們 再來看自適 應計 劃的工作成果：

SET LINESIZE 200 PAGESIZE 100

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

 

 

在以上我 們 可以看到基於統計資訊的基數估計建立了相同的自適應計劃，但最終計劃利用了 hash 連線，    因 為優 化器檢測到不正確的基數估計 在執行 語 句時使用 hash 連線的子計劃來代替了 nest  loop   連 接。

並且在以上例子中，統計收集器監控並快取表的資訊。基於在統計收集器中看到的資訊，優化器會最終確定採用哪個子計劃。在這個例子中，在資料驟然增加的情況下，雜湊連線被選為最終計劃，因為表的行數大於優化器最初的估計。但是，如果初始選中的連線方法是排序合併連線，則自適應不會發生。

以下將過程使用示意圖形式表現：

 

在 oracle 12c   的 V$SQL 檢視中中增加了一個新的列 (IS_RESOLVED_ADAPTIVE_PLAN) 來指明一個 SQL 語句是否有自適應計劃，以及該計劃是否已經完全被確定。

如果 IS_RESOLVED_ADAPTIVE_PLAN 被設定為 'Y', 這意味著計劃不僅是自適應的，而且最終計劃已被選定。可是，如果 IS_RESOLVED_ADAPTIVE_PLAN 被設定為 'N', 這指明瞭選定的計劃是自適應的，但是最終計劃仍未被確認。 'N' 僅僅在一個查詢的初始執行階段中可見，在此之後，自適應計劃的這個值總是為 'Y' 。對於非自適應計劃這個列被設定為 NULL 。

 

同時 oracle  12c 提供了初始化引數 OPTIMIZER_ADAPTIVE_REPORTING_ONLY ，當設定為 TRUE ，把自適應連線方式置於報告模式。在這個模式下，開啟自適應連線方式所需的資訊會被收集，但是改變計劃的任何動作都不會發生。預設情況下該引數是 FALSE 。這個資訊可以在自適應計劃的報告中被檢視，可以在檢視執行計劃時引數 '+report' 來檢視 .

 

 

1.2 自適應並行分配方法

當一個 SQL 語句以並行模式執行時，排序，聚合和連線等這些操作要在在執行語句的並行服務程式之間重新分配資料。此時優化器所用的分配方法取決於操作型別，涉及到的並行服務程式數，以及預期的行數。在以往其他版本的 oracle   資料庫中，如果優化器對行數估算不準確，那麼選中的分配方法就可能不理想，結果某些並行服務程式就可能得不到充分利用。這樣無疑是給資料庫效能造成不必要的浪費。

一般情況下 sql 執行並行時一般有兩種方式：廣播和 hash 分發，在 oracle  11g 及以前版本，這兩種方法是兩個獨立的選項，優化器如果選擇 a 就不能再執行 b 了。

在 oracle 11g 及以前的版本中， oracle 優化器在生成並行的執行計劃時 優化器會根據 hash join 左邊和右邊估算的 cardinality ，並行度等資訊，選擇具體何種分發方式，一旦出現 以上所說的估算不準，分配錯誤，在 sql 開始執行後便無法改變，除非取消執行。

   Broadcast 分發：作為生產者的 PX 程式通過廣播的方式，把 hash   join 左邊的結果集分發給每 個作為消費者的 PX 程式。一般適用於 hashjoin 左邊結果集比右邊小得多的場景。

  Hash 分發：把 hashjoin 的左邊和右邊 ( 兩個資料來源 ) ，通過同樣 hash 函式重新分發，切 分為 N 個工作單元 ( 假設 DoP=N) ，再進行 join ，目的是減少 PX 程式進行 join 操作時，需要連線的資料量。

Oracle 12c 引入了一種稱為混合雜湊 HYBRID HASH) 的自適應並行分發方法，根據統計收集器的結果，對分發方法的確定延遲到執行時間，此時它對於涉及到的資料行數就有了更多的資訊。和自適應連線方法不同，自適應連線方法是在第一次語句第一次執行時，而自適應並行分配方法用於語句的每次執行。

一個統計收集器被插入到操作的前面，如果快取的資料的實際行數比閾值小，則分配方法將從雜湊 (HASH) 切換到廣播 (BROADCAST) 。然而，如果緩衝的行數達到了閾值，則分配方法將會是雜湊 (HASH) 。閾值的定義為並行度的兩倍。

以下通過示例來進行說明：

在以上使用的 tab 1 和 tab2 表之間的連線的 查詢 語句 ，我 們 新增一個並行提示：

 

    我們可以看到使用新的 PX SEND HYBRID HASH 分配方法，允許對分發方法的決定被延遲到執行時間。  

    在以上執行計劃中 5-8 及 10-12 行以 PX SEND HYBRID HASH 開始的執行路徑為 並行服務程式（生產者），其掃描兩個表並且將資料送給另一組並行服務程式（消費者），由第 4 行及 9 行來 px  re ceive ，並由 1-3 行進行並行連線。

  優 化器決定採用混合型雜湊 (HYBRID HASH) 的分配方法。在 這 個 連 接中 訪問 的第一個表是 TAB1 表。來自 TAB1 表的資料行被 緩 存在 統計 收集器中， 見計 劃的第 6 行，直到超 過閥值 或者取得最後一行。在那 時優 化器將會決定採用何種分配方法。

   （ 1 ） 使用雜湊的分配方法 ( 快取行數比閾值大 ) ：   

      在我們以上的例子中，我們的並行度設定成了 16 ，從執行計劃話中看，從 TAB1 表掃描返回的行數是 10001 ,   閾值則是 32 行（ 2X16 ）。這裡遠遠大於閥值，從 TAB1 表返回的 10001 行資料將會以雜湊 (HASH) 的方式分配到負責完成連線的 16 個並行服務程式，既然來自 TAB1 表的資料行採用了雜湊 (HASH) 分配法，來自 TAB2 表的資料也會以雜湊 (HASH) 方法進行分配。

（ 2 ） 使用廣播的分配方法 ( 快取行數比閾值小 ) ：   

ORACLE 12C 比以前版本更聰明之處就在於原先給了多重選擇，但在某些關鍵時機能做出最終決定，我們假定以上例子中的並行度被設定為 64, 從 TAB1 表掃描返回的行數是 25 行 , 閾值則是 128 行（ 2X64 ）。此時便未達到閾值，於是從 TAB1 表返回的 25 行將會被廣播到負責完成連線的 64 個並行服務程式。

在來自 TAB1 表的資料行採用了廣播 (BROADCAST) 的分配方法，來自 TAB2 表的資料行將會通過迴圈制 (ROUND-ROBIN) 的方法進行分配。這意味著來自 TAB1 表的一行資料將會輪流傳送給 6 個並行服務程式中的一個進行連線，直至所有的資料行都分配完畢。

1. 3   小結

  通過總結的過程，我們可以更深入瞭解 oracle   優化器的某些工作過程， SQL 的效能問題是一個大型資料庫永遠的主題， oracle  12c   新的查詢優化自適應方法使得優化器能夠對執行計劃作出實時調整，並且發現能夠導致更佳的統計資訊的額外資訊。利用這些資訊，和已有的統計資訊一起，能夠使得優化器對環境有更多的瞭解，並且允許它每次都選擇一個優化的執行計劃，這確實實現了一個巨大的飛躍。

以上主要對 oracle  12c   優化器新特性自適應查詢優化中的自適應執行計劃部分進行總結分析，在後面將對自適應統計資訊特性部分以及其他新的特性進行研究總結。

 

 

 

 

 

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