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

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

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

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

 

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

以下對自適應統計資訊部分進行總結介紹。

自適應統計資訊

在 oracle 中，優化器所選擇的執行計劃的質量主要取決於可用的統計資訊的質量。然而，但有些查詢謂詞變得過於複雜導致無法單獨依賴於基表的統計資訊，這就是為什麼我們常常遇見一些複雜的 SQL 在統計資訊沒有問題的情況下而仍會選擇較差的執行計劃的原因，而現在在 oracle  12c 中優化器能夠用自適應統計資訊來進行增補。

自適應統計資訊主要包含三個方面：動態取樣（動態統計資訊），自動重優化和 SQL 計劃指令。

1 、動態統計資訊

當一個 SQL 語句在執行編譯時， 統計資訊缺失、過期或者不足時， CBO 優化器選擇的執行計劃就有可能不是最優的甚至是最差 的， 動 態取樣正是為了補償不充足的統計資訊，如果不這麼做，這樣的資訊可能導致非常糟糕的計劃。

一表的統計資訊都缺失的情況下，優化器會先在這些表上使用動態取樣來收集基本的統計資訊。這種情況下收集的統計資訊在質量和完整性上都不如使用 DBMS_STATS 包收集到的資訊。資料庫中預設動態取樣的級別為 2 ，便是以上這種情況。

 

動態取樣的級別

在 Oracle 12c 資料庫中 , 動態取樣被進行加強改造為動態統計資訊，動態統計資訊使優化器在現有的統計資訊上得到更加精準的基數估算。這不僅僅是針對某個單表的訪問，而且也包括針對連線和分組計算。

初始化引數 OPTIMIZER_DYNAMIC_SAMPLING 引入了新的取樣級別 11 。

11 級表示讓 oracle CBO 能夠自行決定是否為 SQL 語句選擇使用動態統計資訊，即使相關的表已存在統計資訊。 CBO 是基於現有的統計資訊， SQL 所用謂詞的複雜度以及預期的執行時間來作出是否使用動態統計資訊的決定。

在以前， CBO 在某些情況下有時在生成執行計劃時會只是做出猜測評估，而現在可以通過動態統計資訊的得到更精準的計算。

 

動態的統計資訊讓執行計劃的評估更加準確，在沒有動態取樣的情況下，優化器只能通過猜測：

 

 

動態統計 資訊 最大的優點是，在優化器選擇執行計劃時，對統計資訊缺失或者統計不夠準確的物件，能夠動態地收集統計資訊，從而獲得相對好的執行計劃， 並且 12c 中優化器還能夠自行 將查詢的結果將會作為動態統計資訊保留在快取中 ，供其他 SQL 來共享這些統計資訊。

但是， 我們知道任何新功能都是一把雙刃劍，有優點也有缺點，對於動態統計也一樣。 當級別設定為 11 時，動態取樣啟用的頻率很可能超過以往。並且 使硬解析時候的解析時間可能變得更長，而且如果大量的動態收集操作發生時可能影響到資料庫全體效能。

 

 

2 、自動重優化

自動重優化和之前所介紹的自適應計劃不同的是，自適應計劃是在初次執行之前給出正確的執行計劃，而自動重優化是在初次執行之後，在以後的執行中去修改執行計劃。

在一個 SQL 語句的初次執行結束之時， CBO 會利用初次執行期間收集到的資訊來決定是否需要自動重優化。如果執行的結果和優化器原有的估計值相差太大，則 CBO 會在下次執行尋求替換的計劃。 CBO 會利用前一次執行收集到的資訊來幫助確定這個替換計劃。 CBO 可能將一個查詢重新優化好幾次，每次都學習並且進一步改善計劃。

Oracle 12c 資料庫只要有以下幾種重優化的方式。

2.1 、統計資訊反饋

統計資訊反饋，它自動為那些反覆執行的具有基數估算誤差的查詢改善計劃。在一個 SQL 語句的首次執行期間 CBO 生成執行計劃時決定是否應該為遊標啟動統計資訊反饋監視器。

統計資訊反饋在以下情況下被啟用：缺失統計資訊的表，表上有多個合取或者析取謂詞 ( 指 AND 或者 OR 連線的謂詞 ), 謂詞包含有複雜操作，使得 CBO 不能準確估算基數。

在查詢結束之時， CBO 將它原來的基數估算和在執行期間觀測到的實際基數進行比較，如果估算值和實際值有較大的差異，它會將正確的值儲存起來供後續使用。如果查詢再次執行，優化器會使用糾正過的基數估算值，而不是它原先的估算值，來確定執行計劃。如果它發現初始的估算值是正確的，則不會採取任何額外的措施。

以下通過例子來演示統計資訊反饋：

   可以看出首次生成的執行計劃中 計算出的基數（ E-Rows ）與 實際 的基數（ A-Rows ）的因數 為 8 （ 63 ： 500） ，這是存在差 誤 的 ，這說明這裡執行計劃還不是最準確的。

 

 

在初次執行之後，優化器將它原來的基數估算和計劃中的操作實際返回的行數進行比較。估計值和實際返回的行數有很大的差別，所以這個遊標被標記為 IS_REOPTIMIZIBLE （可重優化）並且不會被再次使用。 IS_REOPTIMIZIBLE 屬性指明這個 SQL 語句應該在下一次執行的時候被硬解析，所以優化器能夠使用在初次執行時記錄下來的統計資訊來確定一個更佳的執行計劃。

 

在第二次執行，優化器使用了來自初次執行的統計資訊來確定一個具有不同連線順序的新計劃。在生成執行計劃的過程中對統計資訊反饋的使用情況被註明於執行計劃下面的備註部分。

 

 

在第二次使用基數反饋後，新計劃沒有標識為 IS_REOPTIMIZIBLE ，所以它將被這個 SQL 語句的所有後續執行所使用。之後，優化器關閉了統計資訊反饋的監視。

 

2.2 、效能反饋

oracle  12 c 自動重優化的另一種形式為效能反饋，效能反饋主要是在自適應模式下開啟自動並行度選擇功能，從而改善重複執行的 SQL 的效率 。

當自動並行度被啟用，在一個 SQL 語句的首次執行過程中，優化器會決定語句是否應該在並行模式下執行；如果是，應該使用什麼並行度。在初次執行結束時，優化器選擇的並行度，和根據語句初次執行期間的實際效能統計資訊（例如 CPU 時間）計算出來的並行度，被加以比較。 如果兩個值有顯著差別，那麼語句被標識為可重優化， 初次執行的效能統計資訊被作為反饋儲存起來，以幫助為後續的執行計算出一個更加合適的並行度。

Oracle 在 11gR2 引進了 PARALLEL_DEGREE_POLICY 初始化引數 時 當被設定為 AUTO 時，能使並行度 選擇 自動化。 該 引數預設值為 MANUAL 。

 

Oracle 12cR1 為該引數增加了 ADAPTIVE 設定 , 該值類似於 AUTO ， 但 包括了效能反饋。

 

在 PARALLEL_DEGREE_POLICY 調整為 ADAPTIVE 後，故意將一個並不需要那麼大並行的語句開啟到 128 並行度。

 

第二次執行時效能反饋機制已經失效， CBO 自動為該語句選擇並行度為 1 .

 

 

3 、 SQL 計劃指令

SQL 計劃指令是根據通過自動重優化學習到的資訊所建立出來的。在 oracle 12C 的版本開始， oracle 推出了 sql 計劃指令（ SQL Plan directives 簡稱 SPD ）功能，儲存為了以後生成最優執行計劃的一些指令和附加資訊到字典表中，達到持久化的目的。 SQL 計劃指令針對 SQL 的一些額外的資訊 , 優化器可用來生成一個更優的執行計劃。

關於 SQL 計劃指令，我在文章中《 ORACLE 12C 優化器的新利器 - SQL Plan Directives （ SQL 執行計劃指令）介紹》已做詳細的介紹。

http://note.youdao.com/noteshare?id=c30735781a4f69041bcd3c62537cee98

 

4 、自適應查詢優化有關初始化引數

有幾個新的初始化引數可以管理 Oracle 12c 資料庫的優化器及其新特性。下面是關於這些新引數的詳細介紹。

OPTIMIZER_ADAPTIVE_FEATURES

對於新的自適應查詢優化功能的使用，包括自適應連線， SQL 計劃指令的建立和使用，是受 OPTIMIZER_ADAPTIVE_FEATURES 引數控制的。這個引數的預設值和 OPTIMIZER_FEATURES_ENABLE(OFE) 相關。如果該引數被設定為 12.1.0.1 或者更高，那麼 OPTIMIZER_ADAPTIVE_FEATURES 被設為 TRUE, 所有的自適應查詢優化功能將會啟用。如果 OFE 被設為比 12.1.0.1 更低，那麼 OPTIMIZER_ADAPTIVE_FEATURES 將會被設為 FALSE, 所有的自適應查詢優化功能都不會啟用。

OPTIMIZER_ADAPTIVE_REPORTING_ONLY

在開啟自適應查詢優化後，執行計劃的自適應或者中途變動會讓人擔心是否比較危險。為了更好地理解多少 SQL 語句會受到新的自適應計劃的影響，你可以將自適應計劃開啟為只報告模式，方法是將 OPTIMIZER_ADAPTIVE_REPORTING_ONLY 設定為 TRUE( 預設值是 FALSE) 。在這個模式下，啟用自適應連線方法所需的資訊被收集，但是不會有任何修改計劃的行動。這意味著預設的計劃總是會被使用，但計劃在非報告模式下會如何調整的資訊也會被收集。

OPTIMIZER_DYNAMIC_SAMPLING

OPTIMIZER_DYNAMIC_SAMPLING 引數從 9i 就開始有了，在 12c 它有了新的級別 11, 這個級別控制動態統計資訊的生成。當設定為級別 11 時，優化器會自動確定哪些語句會受益於動態統計資訊，即使所有的物件已經有了統計資訊。

 

5 、自適應查詢優化的各種坑

還是那句話， 任何新功能都是一把雙刃劍 ，給我們帶來便利的同時會挖下不少坑來慢慢填。

 

結論

優化器是 Oracle 資料庫最吸引人的部件之一，這是因為它的複雜性。它的目的是為每個 SQL 語句確定最高效的執行計劃。在 oracle  12c 的優化器自適應查詢優化的新特性中我們更可以看到 oracle 在優化器的質量追求上巨大努力。越來越智慧的優化器能減少更多的 SQL 效能問題，但是正因為優化器是個很複雜的元件，在初期總會遭遇各種隱患。因此在升級更新換代之前更需要我們瞭解深入，防範於未然。

 

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