oracle11g中SQL最佳化新特性之Adaptive Cursor Sharing (ACS)

1.   ACS簡介

Oracle Database 11g提供了Adaptive Cursor Sharing (ACS)功能，以克服以往不該共享的遊標被共享的可能性。ACS使用兩個新指標：sensitivity and bindawareness來實施該特點。

2.   ACS機制

2.1.  Adaptive Cursor Sharing Metadata：Oracle 11g也提供了三個新檢視和動態檢視V$SQL的兩個新列來允許DBA來確定最佳化器是否已經確定一個SQL語句為一個ACS的候選，並且，也可以透過它們來觀察最佳化器分類SQL語句用以共享的執行計劃所使用的業務規則：

檢視	描述
V$SQL	Two new columns are added: IS_BIND_SENSITIVE indicates if a SQL statement is bind-sensitive. If this column contains a value of (Y)es, it means that the optimizer peeked at the values of the statement’s bind variables so that it can calculate each predicate’s selectivity. Likewise, IS_BIND_AWARE indicates if the optimizer has also decided that the statement’s cursor is bind-aware after additional execution of the statement.
V$SQL_CS_HISTOGRAM	Distributes the frequency (within a three-bucket histogram) at which Oracle 11g used to decide if a SQL statement was bind-sensitive, including how many times a particular child cursor has been executed.
V$SQL_CS_SELECTIVITY	Contains information about the relative selectivity of a SQL statement’s predicates, including the predicates themselves, and their high and low value ranges. These values are also termed the cursor’s selectivity cube.
V$SQL_CS_STATISTICS	Lists the statistics of whether and/or how often an Adaptive Cursor has been shared. The PEEKED column will display a value of Y(es) if the bind set had been used to build the Adaptive Cursor.

表-1 ACS 檢視

2.2.  Bind Sensitivity：當帶有繫結變數的SQL語句首次被解析時，在最佳化器窺探了繫結變數的值，並確定了語句謂詞的相關選擇率後，把該遊標標記為 bind-sensitive（繫結敏感的）。期間也保留了這些敏感測量值，以便今後帶有同樣變數、不同值的同樣語句再次執行時進行比較，看一個已經存在的執行計劃是否能被新繫結變數值的語句利用。

2.3.  Bind Awareness：一旦一個SQL語句的遊標被標為 bind-sensitive，最佳化器也可以確定遊標是bind-aware。透過檢查隨後執行的同樣SQL語句繫結變數的值和所有匹配計劃已被捕獲的繫結變數的值，最佳化器完成這個步驟。如果最佳化器確定該語句能利用已存在的計劃，那麼，只需要更新遊標執行柱狀圖來反應語句的執行即可。另外，如果繫結變數值足夠不同，最佳化器也許決定建立一個新的子游標和執行計劃。一旦這些發生，Oracle11g也把子游標的相關選擇率儲存到ACS後設資料中。在遊標隨後的執行過程中，最佳化器比較存在的統計選擇性資料和遊標最近執行的統計資料，如果觀察到大多數執行使用近似同樣的選擇性範圍，那麼，遊標將會被標記為bind-aware。

當查詢以一套超出一個已存在的bind-aware的遊標繫結變數的選擇率範圍界限的不同的繫結變數值被執行時會發生什麼呢？在該語句的硬解析期間，最佳化器也許僅僅決定擴充套件那個遊標的選擇率範圍來包含新的變數值，透過產生一個合併兩套繫結變數值的新游來做到這點。可見在必要的時候，也僅僅是增加了新遊標。Oracle11g中，ACS特點預設被開啟，且完全獨立於CURSOR_SHARING引數。

2.4.  對SQLPlan Management (SPM)影響：最後，如果你瞭解古Oracle11g的SPM特點，你可能想知道ACS和SPM是否會相互影響，下面做一些簡短的總結：

如果初始化引數OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES已被設定為TRUE來開啟自動計劃捕獲，那麼，帶繫結變數的一個SQL語句將被標作對應的能用和可接受的執行計劃。

如果同樣語句的第二個執行計劃被建立——對ACS特點來說這並不新鮮——那麼，計劃僅僅被增加到語句的計劃歷史裡，但並能被立刻使用，因為SPM要求新執行計劃首先被驗證為一個較好的計劃。

不幸的是，這意味著一個好的計劃也許被忽視掉，哪怕是其遊標的選擇性範圍可能會導致一個較好的效能。繞過該問題的一個很好的辦法是保持自動計劃捕獲為預設設定False，接著把所有library cahce裡的子游標都捕獲到SMB裡去。這將會迫使ACS產生的遊標的所有計劃都被標為SQL PLAN BASELINES。

3.   限制

Oracle11gR2為止，ACS特性存在以下限制(當以下場景出現時，會導致ACS不會把遊標標記為bind sensitive)：

?  擴充套件遊標共享被關閉；

?  查詢中沒繫結變數；

?  某些引數被設定（例如：繫結變數窺探被置為flase）；

?  語句正使用並行查詢時；

?  語句使用了hints；

?  Outlines正被使用；

?  查詢為遞迴查詢；

?  繫結變數數超過14；

4.   關閉及開啟

?  ACS預設情況下是開啟的，為了關閉ACS，我們需要修改以下三個引數：

alter system set"_optimizer_extended_cursor_sharing_rel"=none;

 altersystem set "_optimizer_extended_cursor_sharing"=none;

 altersystem set "_optimizer_adaptive_cursor_sharing"=false;

?  為了開啟ACS，我們需要修改以下四個引數：

_optim_peek_user_binds=true（一定要開啟繫結變數窺視）_optimizer_adaptive_cursor_sharing=TRUE（以下三個引數預設開啟ACS）_optimizer_extended_cursor_sharing=UDO

_optimizer_extended_cursor_sharing_rel=SIMPLE

5.   注意：

?  相關hint：Oracle11g中有個新的 hint，當使用此hint時，即使把ACS特性關掉，ACS特性在語句級依然會生效，該hint的語法為：/*+ BIND_AWARE*/；

?  關於Outlines：在Outlines存在的場景下，無論在系統級啟用還是語句級透過hint啟用，ACS都會失效；

6.   結論：

Oracle Database 11g的新特點ACS功能為帶繫結變數的SQL語句執行計劃高效共享提供了一個簡單的方法。由於只有當繫結變數值選擇率必要時，ACS也會產生一個新的執行計劃，因此，共享遊標的數目會保持最小。