2.1 Introduction 介绍
With theintroduction of the CBO a number of changes were made to calculate theselectivity of a predicate, which in turn affected how the query wasoptimized. The selectivity was based on the number of distinct values fora given column or a predefined percentage of rows depending on the relationaloperator that was used. This worked well for data that was evenly distributedbut had limitations in applications where data was skewed.
Note:68992.1"PredicateSelectivity".
With 9i a newfeature "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.This method 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 was especially noticeable as the default statistical gathering methodologychanged to gather histograms automatically. This meant that selectivity thatpreviously used only a formula based on the number of distinct values was nowgenerated based on histograms, which gave the optimizer better informationabout the distribution data that was skewed.
The impact ofthis was that end users were reliant on the first execution of a query usingbinds that would generate an execution plan that was representative of thegeneral query usage.
Note:387394.1Queryusing Bind Variables is suddenly slow
Note:430208.1BindPeeking By Example
In Oracle 11gAdaptive Cursor Sharing has been introduced to get around some of these issues.This feature monitors the execution statistics for candidates queries and makesit possible for the same query to generate and use different executionplans for different set of binds values.
--在Oracle 11g中引入了Adaptive CursorSharing 特性，该特性监控查询语句执行的统计信息，并尽可能的根据相同的SQL语句，不同的绑定变量值，使用不同的执行计划。
2.2Advantages of Adaptive Cursor Sharing(ACS的优势)
Adaptive cursorsharing is a solution to give us the shareability of binds, with the planadaptability of literals. With adaptive cursor sharing the database can adaptexecution plans based on the selectivity of values used in binds.
This is aworkaround to issues where different sets of bind values for a given query mayhave different selectivity, leading to a situation where there may besuboptimal plans for different bind sets.
Previously,workarounds employed would either use literal values instead of binds, whichcould lead to excessive hard parsing, or apply a fixed plan that would be acompromise of performance between different bind selectivities.
2.3 Disadvantages of Adaptive Cursor Sharing（ACS的劣势）
There is someextra overhead associated with Adaptive Cursor Sharing in the form of :-
（1）More Hard Parses (CPU) - Extra HardParses will be required when a cursor becomes "Bind Aware" as weattempt to generate the better matched execution plans for the bindselectivity.
（2） More Child Cursors (SGA) - It isrecommended that some consideration be taken to increase the size of theshared_pool on upgrade from 10g to 11g, given the extra cursors that may berequired to accommodate this feature.
（3）More Work to Match the Cursor (CPU)- More Child Cursors and the requirement to match a query to the best executionplan for its predicate selectivity.
2.4 Extended Cursor Sharing ( BindSensitivity)
When a query isexecuted with bind peeking and binds using either one of the followingrelational operators = < > <= >= !=, or a user defined bindoperator e.g. contains(e.job,:job,1)>0, and a change in the bindvariable value may lead to a different plan, the cursor will be marked as bindsensitive.
--当我们在SQL中使用<= 等操作时，在改变绑定变量就会生成一个不同的执行计划（Cursor），并将这个cursor标记为bindsensitive，Bind-Sensitive Cursor是根据绑定变量值得到的最优执行计划的一个cursor。这个就是ECS。
The"LIKE" operator is supported from 11.2.0.2 onwards.
Apart fromchecking for a valid operator there are also a number of subsequent bindsensitivity checks that need to be performed before it can be marked as bindsensitive, if it fails any of these the cursor will not be marked as bindsensitive and adaptive cursor sharing would not occur.
If any of the following checks fail ECS willbe disabled :
--在以下情况会禁用ECS：
（1）Extended cursor sharing is disabled
（2）The query has no binds
（3）Parallel query is used
（4）Certain parameters like ("bindpeeking"=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 a given sqlstatement are greater than 14.
这里绑定变量个数不能超过14个，因为ACS 在绑定变量超过14个的时候会失效。
When using SQLPlan Baselines, and there is more than one plan enabled, ACS will stillbe enabled to use those plans.
When all thecriteria are met the cursor is marked as bind sensitive and a "sharingcontext" containing information about execution statistics of the cursoris created and stored against the cursor.
Cursors that aremarked as bind-sensitive can been identified by the column IS_BIND_SENSITIVE=Yin V$SQL or V$SQLAREA.
2.5 Adaptive Cursor Sharing (Bind Aware)
If there issignificant variation in the row source cardinality for executions of the samesql statement in consecutive executions a cursor will be marked as bind aware.
For more information about this please see:-
Note:836256.1AdaptiveCursor Sharing in 11G
2.6 Monitoring
V$SQL can beused to see if a cursor is_bind_sensitive, is_bind_aware, or is_shareable.
The bind contextinformation can be viewed via V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS andV$SQL_CS_HISTOGRAM
V$SQL_CS_SELECTIVITYexposes the valid selectivity ranges for a child cursor in extendedcursorsharing mode. A valid range consists of a low and high value for each predicatecontaining binds. Each predicate's selectivity (with the current bind value)mustfall between the corresponding low and high values in order for thechild cursor to be shared.
V$SQL_CS_STATISTICScontains the raw execution statistics used by the monitoring component ofadaptive cursor sharing. A sample of the executions is monitored.
This viewexposes which executions were sampled, and what the statistics were for thoseexecutions. The statistics are cumulative for each distinct set of bind values.
V$SQL_CS_HISTOGRAMsummarizes the monitoring information stored by adaptive cursor sharing.This information is used to decide whether to enable extended cursor sharingfor a query. It is stored in a histogram, whose bucket's contents areexposed by this view.
2.7 Issues with Excessive Child Cursors
There is also apossibility that Adaptive Cursor Sharing may compound problems in whichexcessivenumbers of child cursors are generated. This may lead toperformance degradation as largenumbers 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 andthis 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 arestill excessive child cursors, then Oracle Support should be contacted toassist with diagnosing the issue.
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;
关于Oracle 10g和11g中这些参数值的变化，MOS上有篇文档说明：
PARAMETERS TO CHANGE 11.2.0.1 TO 10.2.0.4[ID 1274553.1]
These are theparameters that are changed when setting optimizer_features_enable=10.2.0.4 in11.2.0.1 database. These values represent OFE=10.2.0.4 and -- is the 11.2.0.1value
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群⑧：
窥视卡
雷达卡
发表于 2016-7-22 07:43:19
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡