一．自適應遊標共用（Adaptive Cursor Sharing） 說明
1.1 ACS概述
綁定變數使Oracle DB 可以為多條SQL 陳述式共用單個遊標，以減少分析SQL 陳述式所使用的共用記憶體量。然而，遊標共用和SQL 最佳化是兩個相互衝突的目標。用文字編寫SQL 陳述式為最佳化程式提供了更多的資訊，這無疑會導致更好的執行計畫，但大量的硬分析會導致記憶體和CPU 開銷增加。
Oracle9i Database首次嘗試推出了一個折衷的解決方案:允許共用使用不同文字值的相似SQL 陳述式。對於使用綁定變數的語句，Oracle9i 還引入了綁定掃視(Bind Peek)概念。使用綁定掃視，最佳化程式會在首次執行語句時查看綁定值。然後，它使用這些值來確定一個執行計畫，語句的所有其它執行均共用該執行計畫。為了從綁定掃視中受益，假定使用遊標共用，且假定語句的不同調用使用相同的執行計畫。如果語句的不同調用從不同的執行計畫中獲得很大收益，則綁定掃視對產生有效執行計畫就不再有用。
一個計劃並不總是適用於所有綁定值,為了儘可能解決此問題，Oracle Database 11g 引入了自適應遊標共用。此功能是一項更複雜的策略，它並不會盲目地共用遊標，如果與分析時間和記憶體使用量量開銷相比，使用多個執行計畫所帶來的收益更重要，則會為使用綁定變數的每條SQL 陳述式產生多個執行計畫。然而，由於使用綁定變數的目的是共用記憶體中的遊標，因此對於需要產生的子遊標數目必須採取一種折衷的方法。
Adaptive Cursor Sharing 作用如下：
• 通過自適應遊標共用，可以僅針對使用綁定變數的語句智能地共用遊標。
• 自適應遊標共用用於協調遊標共用和最佳化之間的矛盾。
• 自適應遊標共用具有如下優點：
– 自動檢測不同執行受益於不同執行計畫的時間
– 將產生的子遊標數限制到最小
– 是自動機制，無法關閉
1.2 ACS體繫結構
1.2.1樣本
上圖使用自適應遊標共用執行下列步驟：
1. 遊標照常隨硬分析啟動。如果發生綁定掃視(BindPeek)，且使用長條圖計算包含綁定變數的謂詞選擇性，則該遊標將被標記為對綁定敏感的遊標。此外，還會儲存一些有關包含綁定變數的謂詞的資訊，包括謂詞選擇性。在上圖中，所儲存的謂詞選擇性是一個以(0.15,0.0025) 為中心的立方體。由於進行了初始硬分析，將使用已掃視的綁定確定初始執行計畫。執行遊標後，綁定值和遊標的執行統計資訊儲存在該遊標中。
當使用新的一組綁定值執行下一語句時，系統會執行常規軟分析，並尋找供執行使用的相匹配的遊標。執行結束時，會將執行統計資訊與當前儲存在遊標中的執行統計資訊進行比較。然後，系統觀察所有先前啟動並執行統計提示模式並確定是否將遊標標記為能識別綁定的遊標。
2. 下一次對此查詢進行軟分析時，如果遊標能夠識別綁定，則會使用能識別綁定的遊標匹配。假定具有新的一組綁定值的謂詞選擇性現在是(0.18,0.003)。由於選擇性用作能識別綁定的遊標匹配的一部分，並且該選擇性位於現有立方體中，因此該語句使用現有子遊標的執行計畫運行。
3. 下一次對此查詢進行軟分析時，假設具有一組新綁定值的謂詞選擇性是(0.3,0.009)。由於該選擇性不在現有立方體中，所以找不到子遊標匹配項。因此，系統會執行硬分析，在本例中產生了一個具有第二個執行計畫的新子遊標。此外，新選擇性立方體將儲存為該新子遊標的一部分。執行該新子遊標後，系統會將綁定值和執行統計資訊儲存在該遊標中。
4. 下一次對此查詢進行軟分析時，假設具有一組新綁定值的謂詞選擇性是(.28,0.004)。由於該選擇性不在現有的某個立方體中，系統將執行硬分析。假設此時硬分析產生與第一個執行計畫相同的執行計畫。因為該計劃與第一個子遊標相同，所以將合并這兩個子遊標。也就是說，這兩個立方體將合并為一個較大的新立方體，並刪除其中一個子遊標。下次執行軟分析時，如果選擇性位於該新立方體中，子遊標將匹配。
1.2.2 說明
在Oracle 10g 和11g中對綁定變數的處理，已經有所不同， 在Oracle 10g中，綁定變數相對比較簡單，當使用綁定變數的SQL 第一次執行時，會進行硬解析，產生plan 和cursor。 在這個過程中，Oracle 會使用bind peeking，即將綁定變數的值帶入，從而選擇最優的一個plan。以後每次執行都使用這個plan。
在以後的執行時，如果因為其他原因導致cursor 不可重用，那麼就會產生一個child_cursor. 這個cursor 不可重用的原因可以查看：v$sql_shared_cursor視圖。
那麼這就有一個問題。如果列上有列上有嚴重的資料扭曲，某個欄位中99%是值1，1%是值0. 當我們用0 來進行peeking的時候，這時候會走索引，並且以後的所有plan 都是使用這個。 如果我們的綁定值變成了1. 這個時候，明顯走全表掃描比索引划算。
但是Oracle 10g 下還是會使用第一次的plan，即使這個plan 不是最優的。所以在Oracle 10g下，如果資料存在資料扭曲，那麼最好不要使用綁定變數。
在Oracle 11g 以後在綁定變數這塊有所以改變，會產生一個範圍值的執行計畫。然後每次傳變數進去就對比範圍，選擇最優的執行計畫。與這個功能相關的參數儲存在v$sql視圖中：is_bind_sensitive,is_bind_aware,is_shareable。這幾個欄位，在Oracle 10g的v$sql 視圖裡是沒有的。
我們這裡要說明的Adaptive Cursor Sharing特性，其允許一個使用綁定變數的SQL語句使用多個執行計畫。對於同一個SQL, 為了得到合適的查詢，oracle 會監控使用不同綁定變數的情況，已確保對不同綁定變數值的cursor（執行計畫）都是最優的。比如因為資料扭曲的原因對綁定變數值A 使用執行計畫A，對綁定變數值B 使用執行計畫B。雖然他們的SQL 是相同的，但執行計畫不同。
Adaptive Cursor Sharing 預設啟動的。不過要注意的是，該特性只有在綁定變數的參數個數不超過14個的情況才有效。
有關Oracle 10g和11g 綁定變數更多區別參考：
Oracle 10g 與 11g 綁定變數（Bind Variable） 區別 說明
http://www.cndba.cn/Dave/article/1390
1.3自適應遊標共用視圖
1.3.1 V$SQL 中已新增了兩個新列
（1）IS_BIND_SENSITIVE：指示遊標是否為對綁定敏感，值為YES | NO。符合以下情況的查詢稱為對綁定敏感的查詢：計算謂詞選擇性時最佳化程式為其掃視綁定變數值，並且綁定變數值的更改可能導致不同計劃。
（2）IS_BIND_AWARE：指示遊標是否為能標識綁定的遊標，值為YES | NO。遊標快取中已標記為使用能識別綁定的遊標共用的遊標稱為能標識綁定的遊標。
1.3.2 V$SQL_CS_HISTOGRAM
顯示跨三個儲存桶執行記錄長條圖的執行計數的分布情況。
1.3.3 V$SQL_CS_SELECTIVITY
顯示為包含綁定變數且在遊標共用檢查中使用了其選擇性的每個謂詞儲存在遊標中的選擇性立方體或範圍。它包含謂詞文本和選擇性範圍的下限值和上限值。
1.3.4 V$SQL_CS_STATISTICS
自適應遊標共用監視查詢的執行，並在一段時間內收集相關的資訊，使用此資訊可確定是否切換到對該查詢使用能識別綁定的遊標。該視圖匯總了所收集的資訊以讓您作出以下決定：對於執行樣本，它跟蹤已處理的行數、緩衝區擷取數和CPU 時間。如果使用綁定集來構建遊標，則PEEKED 列的值為YES，否則為NO。
二．MOS 說明
MOS上對ACS的說明：
Adaptive Cursor Sharing in 11G[ID 836256.1]
Adaptive Cursor SharingOverview [ID 740052.1]
2.1 Introduction 介紹
With the introduction of theCBO a number of changes were made to calculate the selectivity of a predicate, whichin turn affected how the query was optimized. The selectivity was basedon the number of distinct values for a given column or a predefined percentageof rows depending on the relational operator that was used. This worked wellfor data that was evenly distributed but had limitations in applications wheredata was skewed.
Note:68992.1 "PredicateSelectivity".
With 9i a new feature"Bind Peeking" was introduced to try to get around the issuesassociated with guessing the selectivity of the bind. This meant that duringhard parsing of a query using bind variables, we would peek at the binds andgenerate selectivity based on the bind and the underlying column statistics. Thismethod could sometimes lead to plans being generated that were notrepresentative of the general query usage if the bind selectivity of theinitial execution of a statement varied from the selectivity of subsequentexecutions with different sets of binds.
In Oracle 10g, this wasespecially noticeable as the default statistical gathering methodology changedto gather histograms automatically. This meant that selectivity that previouslyused only a formula based on the number of distinct values was now generatedbased on histograms, which gave the optimizer better information about thedistribution data that was skewed.
The impact of this was that endusers were reliant on the first execution of a query using binds that wouldgenerate an execution plan that was representative of the general query usage.
Note:387394.1 Query usingBind Variables is suddenly slow
Note:430208.1 Bind PeekingBy Example
In Oracle 11g Adaptive CursorSharing has been introduced to get around some of these issues. Thisfeature monitors the execution statistics for candidates queries and makes itpossible for the same query to generate and use different execution plansfor different set of binds values.
--在Oracle 11g中引入了AdaptiveCursor Sharing 特性，該特性監控查詢語句執行的統計資訊，並儘可能的根據相同的SQL語句，不同的綁定變數值，使用不同的執行計畫。
2.2 Advantages of Adaptive Cursor Sharing(ACS的優勢)
Adaptive cursor sharing is asolution to give us the shareability of binds, with the plan adaptability of literals.With adaptive cursor sharing the database can adapt execution plans based onthe selectivity of values used in binds.
This is a workaround to issueswhere different sets of bind values for a given query may have differentselectivity, leading to a situation where there may be suboptimal plans fordifferent bind sets.
Previously, workaroundsemployed would either use literal values instead of binds, which could lead toexcessive hard parsing, or apply a fixed plan that would be a compromise ofperformance between different bind selectivities.
2.3 Disadvantages of Adaptive Cursor Sharing（ACS的劣勢）
There is some extra overheadassociated with Adaptive Cursor Sharing in the form of :-
（1）More Hard Parses (CPU) -Extra Hard Parses will be required when a cursor becomes "Bind Aware"as we attempt to generate the better matched execution plans for the bindselectivity.
（2） More Child Cursors(SGA) - It is recommended that some consideration be taken to increase the sizeof the shared_pool on upgrade from 10g to 11g, given the extra cursors that maybe required to accommodate this feature.
（3）More Work to Match theCursor (CPU) - More Child Cursors and the requirement to match a query to thebest execution plan for its predicate selectivity.
2.4 Extended Cursor Sharing ( Bind Sensitivity)
When a query is executed withbind peeking and binds using either one of the following relational operators =< > <= >= !=, or a user defined bind operator e.g.contains(e.job,:job,1)>0, and a change in the bind variable value maylead to a different plan, the cursor will be marked as bind sensitive.
--當我們在SQL中使用<= 等操作時，在改變綁定變數就會產生一個不同的執行計畫（Cursor），並將這個cursor標記為bind sensitive，Bind-Sensitive Cursor是根據綁定變數值得到的最優執行計畫的一個cursor。這個就是ECS。
The "LIKE" operatoris supported from 11.2.0.2 onwards.
Apart from checking for a validoperator there are also a number of subsequent bind sensitivity checks thatneed to be performed before it can be marked as bind sensitive, if it fails anyof these the cursor will not be marked as bind sensitive and adaptive cursorsharing would not occur.
If any of the following checks fail ECS will bedisabled :
--在以下情況會禁用ECS：
（1）Extended cursor sharingis disabled
（2）The query has no binds
（3）Parallel query is used
（4）Certain parameters like("bind peeking"=false) are set
（5）You are using a /*+NO_BIND_AWARE */ hint
（6）Outlines are being used
（7）It is a recursive query
（8）The number of binds in agiven sql statement are greater than 14.
這裡綁定變數個數不能超過14個，因為ACS 在綁定變數超過14個的時候會失效。
When using SQL Plan Baselines,and there is more than one plan enabled, ACS will still be enabled to usethose plans.
When all the criteria are metthe cursor is marked as bind sensitive and a "sharing context"containing information about execution statistics of the cursor is created andstored against the cursor.
Cursors that are marked asbind-sensitive can been identified by the column IS_BIND_SENSITIVE=Y in V$SQL orV$SQLAREA.
2.5 Adaptive Cursor Sharing (Bind Aware)
If there is significantvariation in the row source cardinality for executions of the same sqlstatement in consecutive executions a cursor will be marked as bind aware.
For more information about this please see :-
Note:836256.1 AdaptiveCursor Sharing in 11G
2.6 Monitoring
V$SQL can be used to see if acursor is_bind_sensitive, is_bind_aware, or is_shareable.
The bind context informationcan be viewed via V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS andV$SQL_CS_HISTOGRAM
V$SQL_CS_SELECTIVITY exposesthe valid selectivity ranges for a child cursor in extended cursor sharingmode. A valid range consists of a low and high value for each predicatecontaining binds. Each predicate's selectivity (with the current bind value)must fall between the corresponding low and high values in order for thechild cursor to be shared.
V$SQL_CS_STATISTICS containsthe raw execution statistics used by the monitoring component of adaptivecursor sharing. A sample of the executions is monitored.
This view exposes whichexecutions were sampled, and what the statistics were for those executions.The statistics are cumulative for each distinct set of bind values.
V$SQL_CS_HISTOGRAM summarizesthe monitoring information stored by adaptive cursor sharing. Thisinformation is used to decide whether to enable extended cursor sharing for aquery. It is stored in a histogram, whose bucket's contents are exposedby this view.
2.7 Issues with Excessive Child Cursors
There is also a possibilitythat Adaptive Cursor Sharing may compound problems in whichexcessive numbers of child cursors are generated. This may lead toperformance degradation as large numbers of child cursor can put spacepressure on the shared pool, and may also lead to an increase in mutex X waitsfor that cursor.
Things to check.
1. Ensure that cursor_sharing is not set to SIMILAR. In 11g, this setting isnot recommended and this parameter will eventually be deprecated.
Note:1169017.1:ANNOUNCEMENT:Deprecating the cursor_sharing = 'SIMILAR' setting
2. If there are high version counts check v$sql_shared_cursor. And search MyOracle Support for notes that may allude to the cause of the excessive cursors.
Note:438755.1 :Formated V$SQL_SHARED_CURSOR Report by SQLID or Hash Value
If there are still excessivechild cursors, then Oracle Support should be contacted to assist withdiagnosing the issue.
三．ACS 啟用與關閉
與ACS 相關的3個參數是：
_optimizer_adaptive_cursor_sharing
_optimizer_extended_cursor_sharing
_optimizer_extended_cursor_sharing_rel
在Oracle 11gR2的參考手冊裡並沒有搜到這個參數的解釋，不過我們可以通過查看Oracle 的參數來瞭解這3個參數的作用。
這裡我們要用到一個視圖：all_parameters. 該視圖的源碼參考：
Oracle all_parameters 視圖
http://blog.csdn.net/tianlesoftware/article/details/6641281
SQL>select * from all_parameters where name like '%_optimizer_%_cursor_sharing%';
返回結果：
我們可以在db 運行時修改這3個參數，並且能即時生效。
關閉ACS的操作如下：
SQL> alter session set"_optimizer_extended_cursor_sharing_rel"=none;
SQL> alter session set"_optimizer_extended_cursor_sharing"=none;
SQL> alter session set"_optimizer_adaptive_cursor_sharing"=false;
關於ACS的問題，itpub上的 viadeazhu 有個更深入的研究，寫得非常詳細，網址如下：http://space.itpub.net/15415488/viewspace-621535
關於Oracle 10g和11g中這些參數值的變化，MOS上有篇文檔說明：
PARAMETERS TO CHANGE 11.2.0.1 TO 10.2.0.4 [ID1274553.1]
These are the parameters thatare changed when setting optimizer_features_enable=10.2.0.4 in 11.2.0.1database. These values represent OFE=10.2.0.4 and -- is the 11.2.0.1 value
alter session set "_optimizer_undo_cost_change"= '10.2.0.4'; -- 11.2.0.1
alter session set "_optimizer_null_aware_antijoin" = false; -- true
alter session set "_optimizer_extend_jppd_view_types" = false; --true
alter session set "_replace_virtual_columns" = false; -- true
alter session set "_first_k_rows_dynamic_proration" = false; --true
alter session set "_bloom_pruning_enabled" = false; -- true
alter session set "_optimizer_multi_level_push_pred" = false; --true
alter session set "_optimizer_group_by_placement" = false; -- true
alter session set "_optimizer_extended_cursor_sharing_rel" = none; --simple
alter session set "_optimizer_adaptive_cursor_sharing" = false; --true
alter session set "_optimizer_improve_selectivity" = false ; -- true
alter session set "_optimizer_enable_density_improvements" = false;-- true
alter session set "_optimizer_native_full_outer_join" = off; -- force
alter session set "_optimizer_enable_extended_stats" = false; -- true
alter session set "_nlj_batching_enabled" = 0; -- 1
alter session set "_optimizer_extended_stats_usage_control" = 255; --224
alter session set "_bloom_folding_enabled" = false; -- true
alter session set "_optimizer_coalesce_subqueries" = false; -- true
alter session set "_optimizer_fast_pred_transitivity" = false; -- true
alter session set "_optimizer_fast_access_pred_analysis" = false; --true
alter session set "_optimizer_unnest_disjunctive_subq" = false; --true
alter session set "_optimizer_unnest_corr_set_subq" = false; -- true
alter session set "_optimizer_distinct_agg_transform" = false; --true
alter session set "_aggregation_optimization_settings" = 32; -- 0
alter session set "_optimizer_connect_by_elim_dups" = false; -- true
alter session set "_optimizer_eliminate_filtering_join" = false; --true
alter session set "_connect_by_use_union_all" = old_plan_mode; --true
alter session set "_optimizer_join_factorization" = false; -- true
alter session set "_optimizer_use_cbqt_star_transformation" = false;-- true
alter session set "_optimizer_table_expansion" = false ; -- true
alter session set "_and_pruning_enabled" = false ; -- true
alter session set "_optimizer_distinct_placement" = false ; -- true
alter session set "_optimizer_use_feedback" = false ; -- true
alter session set "_optimizer_try_st_before_jppd" = false ; -- true
-------------------------------------------------------------------------------------------------------
著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!
QQ:492913789
Email:ahdba@qq.com
Blog: http://www.cndba.cn/dave
Weibo: http://weibo.com/tianlesoftware
Twitter: http://twitter.com/tianlesoftware
Facebook: http://www.facebook.com/tianlesoftware
Linkedin: http://cn.linkedin.com/in/tianlesoftware
