Oracle 11g new Features-adaptive cursor sharing (Adaptive Cursor sharing:acs) Description __oracle

one. Adaptive cursor Sharing (adaptive Cursor sharing) Description

1.1 ACS Overview

Binding variables Enable Oracle DB to share a single cursor for multiple SQL statements to reduce the amount of shared memory used for parsing SQL statements. However, cursor sharing and SQL optimization are two conflicting goals. Writing SQL statements in writing provides more information to the optimizer, which will undoubtedly lead to better execution plans, but a large amount of hard analysis can lead to increased memory and CPU overhead.

Oracle9i Database first tried to introduce a compromise solution that allows you to share similar SQL statements that use different literal values. For statements that use binding variables, the oracle9i also introduces the concept of binding scanning (bind Peek). With a binding pan, the optimizer looks at the bound value when the statement is first executed. It then uses these values to determine an execution plan, and all other executions of the statement share the execution plan. In order to benefit from a bound peek, it is assumed that cursor sharing is used and that the different invocations of the statement use the same execution plan. A bound peek is no longer useful for generating a valid execution plan if different invocations of the statement derive significant benefits from different execution plans.

A schedule does not always apply to all bound values, and in order to resolve this problem as much as possible, Oracle Database 11g introduces adaptive cursor sharing. This feature is a more complex strategy that does not blindly share cursors, and if the benefits of using multiple execution plans are more important than the analysis time and memory usage overhead, multiple execution plans are generated for each SQL statement that uses the binding variable. However, because the purpose of using bound variables is to share cursors in memory, there must be a compromise approach to the number of child cursors that need to be generated.

Adaptive Cursor sharing function as follows:

• With adaptive cursor sharing, you can intelligently share cursors only for statements that use bound variables.

• Adaptive cursor sharing is used to reconcile the contradictions between cursor sharing and optimization.

• Adaptive cursor Sharing has the following advantages:

– Automatically detect the time that different execution benefits from different execution plans

– Limit the number of child cursors generated to a minimum

– is an automatic mechanism and cannot be closed

1.2 ACS Architecture

1.2.1 Sample

The above illustration uses adaptive cursor sharing to perform the following steps:

1. Cursors are started as usual with hard analysis. If a binding peek (Bindpeek) occurs and the predicate selectivity containing the binding variable is computed using a histogram, the cursor is marked as a binding-sensitive cursor. In addition, some information about predicates containing bound variables, including predicate selectivity, is stored. In the above illustration, the stored predicate selectivity is a (0.15,0.0025)-centric cube. Because of the initial hard analysis, the initial execution plan is determined using a swept binding. After the cursor is executed, the binding value and the execution statistics of the cursor are stored in the cursor.

When the next statement is executed with a new set of bound values, the system performs a general soft analysis and looks for a matching cursor to be used for execution. At the end of execution, the execution statistics are compared against the execution statistics currently stored in the cursor. The system then observes all the previously running statistics patterns and determines whether the cursor is marked as a bound cursor.

2. The next time you make a soft analysis of this query, if the cursor is able to recognize the binding, it will match with a cursor that recognizes the binding. The predicate selectivity assumed to have a new set of bound values is now (0.18,0.003). Because the selectivity is used as part of the matching of the cursor that recognizes the binding, and the selectivity is in an existing cube, the statement runs with the execution plan of an existing child cursor.

3. The next time you make a soft analysis of this query, assume that the predicate selectivity with a new set of bound values is (0.3,0.009). The child cursor match cannot be found because the selectivity is not in the existing cube. Therefore, the system performs a hard analysis and in this case a new child cursor with a second execution plan is generated. In addition, the new optional cube is stored as part of the new child cursor. After the new child cursor is executed, the system stores the bound value and execution statistics in the cursor.

4. The next time you make a soft analysis of this query, assume that the predicate selectivity with a new set of bound values is (. 28,0.004). Because the selectivity is not in one of the existing cubes, the system performs a hard analysis. Suppose that the hard analysis generates the same execution plan as the first execution plan. Because the plan is the same as the first child cursor, the two child cursors will be merged. That is, the two cubes will be merged into a larger new cube and delete one of the child cursors. The next time you perform a soft analysis, the child cursor will match if the selectivity is in the new cube.

1.2.2 Description

The handling of binding variables in Oracle 10g and 11g has been different, and in Oracle 10g, binding variables are relatively simple, and when SQL that uses a binding variable executes for the first time, a hard resolution is made to generate plan and cursor. In this process, Oracle uses bind peeking to bring the value of the bound variable into, thus selecting the optimal plan. This plan is used for each subsequent execution.

In future executions, if the cursor is not reusable for other reasons, a child_cursor is generated. This cursor is not reusable for the reason that you can view: V$sql_shared_cursor view.

So there's a problem. If there is a serious data skew on the column, 99% of the value in a field is 1,1% value 0. When we use 0来 for peeking, this is the index, and all future plan uses this. If our binding value turns to 1. This time, obviously go full table scan peso for good value.

But Oracle 10g will still use the first plan, even if the plan is not optimal. So under Oracle 10g, if the data has data skew, it's best not to use bound variables.

After Oracle 11g in the binding variable this block has changed, will generate a range of values of the execution plan. Then each variable goes in the contrast range, select the best execution plan. The parameters associated with this feature are saved in the V$sql view: is_bind_sensitive,is_bind_aware,is_shareable. These few fields are not in the Oracle 10g V$sql view.

We are going to describe the adaptive Cursor sharing feature, which allows multiple execution plans to be used by an SQL statement that uses a binding variable. For the same SQL, in order to get the right query, Oracle monitors the use of different binding variables, ensuring that the cursor (execution plan) for the values of the different binding variables are optimal. For example, because the data is skewed to the binding variable value a uses execution plan A to use execution Plan B for the bound variable value B. Although their SQL is the same, the execution plan is different.

Adaptive Cursor Sharing is started by default. Note, however, that the attribute only works if the number of parameters in the bound variable is no more than 14.

More difference references for Oracle 10g and 11g binding variables:

Oracle 10g and 11g binding variable (bind Variable) Difference description

http://www.cndba.cn/Dave/article/1390

1.3 Adaptive cursors shared views

two new columns have been added to the 1.3.1 V$sql

(1) Is_bind_sensitive: Indicates whether the cursor is sensitive to bindings, and the value is yes | NO. Queries that meet the following conditions are referred to as binding-sensitive queries: The optimizer scans the values of the bound variables for them when the predicate selectivity is evaluated, and changes to the value of the binding variable can cause different plans.

(2) Is_bind_aware: Indicates whether the cursor is a cursor that can identify the binding, and the value is yes | NO. Cursors in the cursor cache that have been marked for sharing with a bound cursor are called cursors that can identify bindings.

1.3.2 V$sql_cs_histogram

Displays the distribution of execution counts for the execution history histogram across three buckets.

1.3.3 v$sql_cs_selectivity

A selective cube or range that is displayed as a binding variable and each predicate that uses its selectivity in the cursor share check is stored in the cursor. It contains the lower and upper values of the predicate text and selectivity range.

1.3.4 V$sql_cs_statistics

Adaptive cursor sharing monitors the execution of a query and collects relevant information over a period of time, using this information to determine whether to switch to a cursor that recognizes binding to the query. This view summarizes the information collected to allow you to decide on the following: For example execution, it tracks the number of rows processed, the number of buffer fetches, and the CPU time. If you use a binding set to build a cursor, the value of the peeked column is yes or No.

two. MOS Description

Description of ACS on MOS:

Adaptive Cursor sharing in 11g[id 836256.1]

Adaptive Cursor Sharingoverview [ID 740052.1]

2.1 Introduction Introduction

With the introduction of THECBO a number of changes were made to calculate the selectivity of a predicate, Whichin turn AF  fected how the query is optimized. The selectivity was basedon the number of distinct values for a given column or a predefined percentageof rows depending o n the relational operator that is used. This worked wellfor data is evenly distributed but had limitations in applications is wheredata.

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