oracle11g新特點——Adaptive Cursor Sharing (ACS)，oracle11gadaptive

oracle11g新特點——Adaptive Cursor Sharing (ACS)，oracle11gadaptive

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也會產生一個新的執行計畫，因此，共用遊標的數目會保持最小。

 

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。