New Features of Oracle 11g-adaptive cursor sharing: ACS

 

 

1. Adaptive cursor sharing

 

1.1 ACS Overview

Variable binding allows oracle db to share a single cursor for Multiple SQL statements to reduce the amount of shared memory used for analyzing SQL statements. However, cursor sharing and SQL optimization are two conflicting targets. Writing SQL statements in text provides more information for the optimization program, which will undoubtedly lead to better execution plans, but a large amount of hard analysis will lead to increased memory and CPU overhead.

 

Oracle9i Database launched a compromise solution for the first time: similar SQL statements with different text values can be shared. For statements that use bind variables, Oracle9i also introduces the BIND peek concept. With the BIND scan, the optimizer will view the BIND value when executing the statement for the first time. It then uses these values to determine an execution plan, and all other executions of the statement share the execution plan. To benefit from the BIND view, it is assumed that cursor sharing is used, and that different calls of the statement use the same execution plan. If different calls of a statement are greatly benefited from different execution plans, binding a scan will no longer be useful for generating an effective execution plan.

 

A plan does not always apply to all bound values. To solve this problem as much as possible, Oracle Database 11g introduces adaptive cursor sharing. This feature is a more complex strategy and does not blindly share cursors. 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 bound to variables. However, because the purpose of binding variables is to share the cursor in the memory, a compromise must be adopted for the number of child cursors to be generated.

 

Adaptive cursor sharing has the following functions:

• Adaptive cursor sharing allows you to intelligently share a cursor only for statements that use variable binding.

• Adaptive cursor sharing is used to coordinate conflicts between cursor sharing and optimization.

• Adaptive cursor sharing has the following advantages:

-Automatic Detection of the time at which different executions benefit from different execution plans

-Limit the number of child cursors to the minimum

-It is an automatic mechanism and cannot be disabled.

 

1.2 ACS Architecture

 

1.2.1 example

 

 

Use adaptive cursor sharing to perform the following steps:

 

1. The cursor is started as usual with the hard analysis. If bindpeek is bound and a histogram is used to calculate the predicate selectivity of the bound variable, the cursor is marked as a binding sensitive cursor. In addition, it stores information about the predicates that contain the bound variables, including the predicate selectivity. In, the predicate stored is a cube centered on (0.15, 0.0025. Due to initial hard analysis, the initial execution plan will be determined using the checked bindings. After the cursor is executed, the execution statistics of the bound value and cursor are stored in the cursor.

When you use a new set of bound values to execute the next statement, the system performs a regular soft analysis and finds the matched cursor for execution. When execution ends, the execution statistics are compared with the execution statistics currently stored in the cursor. Then, the system observes all previous statistical information modes and determines whether to mark the cursor as a bound cursor.

 

2. During the next soft analysis of this query, if the cursor can identify the binding, it will use the cursor matching that can identify the binding. Assuming that the predicate with a new set of bound values is selective now (0.18, 0.003 ). This statement runs using an existing sub-game subject execution plan because it is used as a part of an identifiable bound cursor match and is located in an existing cube.

 

3. For the next soft analysis of this query, it is assumed that the predicate with a set of new bound values is optional (0.3, 0.009 ). Because the selection is not in the existing cube, the Child cursor match cannot be found. Therefore, the system performs a hard analysis and generates a new sub-cursor with the second execution plan in this example. In addition, the new selective cube will be stored as part of the new sub-game. After the new sub-cursor is executed, the system stores the bound value and execution statistics in the cursor.

 

4. For the next soft analysis of this query, it is assumed that the predicate with a set of new bound values is optional (. 28, 0.004 ). Since this selectivity is not in an existing cube, 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 are merged. That is to say, the two cubes are merged into a larger new cube and one of the Child cursors is deleted. When the next soft analysis is performed, the Child cursor matches the child cursor if the new cube is selected.

 

 

1.2.2 description

The handling of variable binding in Oracle 10g and 11g is different. In Oracle 10g, variable binding is relatively simple. When the SQL statement that uses the variable binding is executed for the first time, it will perform hard parsing to generate the plan and cursor. In this process, Oracle will use bind peeking to bring in the value of the bound variable, so as to select the optimal plan. This plan will be used for each subsequent execution.

In future execution, if the cursor cannot be reused due to other reasons, a child_cursor will be generated. for the reason that the cursor cannot be reused, see V $ SQL _shared_cursor view.

 

So there is a problem. If a column has a severe data skew, 99% in a field is a value of and is a value of 0. when we use 0 for peeking, this will take the index, and all future plans will use this. If our binding value is changed to 1, it is more cost-effective to perform full table scan than to perform index scanning.

However, Oracle uses the first plan for 10 Gb, even if the plan is not optimal. Therefore, in Oracle 10 Gb, if data is skewed, it is best not to bind a variable.

 

After the Oracle 11g is bound to the variable, an execution plan with a range value will be generated. Then, compare the range and select the optimal execution plan each time the variables are passed in. Parameters related to this function are stored in the V $ SQL view: is_bind_sensitive, is_bind_aware, and is_retriable. These fields are not found in the V $ SQL view of Oracle 10 Gb.

 

The adaptive cursor sharing feature, which allows one SQL statement bound with a variable to use multiple execution plans. For the same SQL statement, Oracle monitors the use of different bound variables to obtain appropriate queries. It has ensured that the cursor (Execution Plan) of different bound variable values is optimal. For example, Execution Plan A is used for binding variable value a due to data skew, and execution plan B is used for binding variable value B. Although their SQL statements are the same, their execution plans are different.

 

Adaptive cursor sharing is enabled by default. However, it should be noted that this feature is valid only when the number of variable-bound parameters cannot exceed 14.

 

For more differences between Oracle 10g and 11g variable binding, refer:

Differences between Oracle 10g and 11g Bind Variable

Http://blog.csdn.net/tianlesoftware/article/details/6591222

 

1.3 adaptive cursor sharing View

 

1.3.1 two new columns have been added to V $ SQL.

(1) is_bind_sensitive: indicates whether the cursor is sensitive to binding. The value is yes | No. Queries that meet the following requirements are called binding-sensitive queries: When determining predicate selectivity, the optimizer scans the bound variable values for them, and changes to the bound variable values may lead to different plans.

(2) is_bind_aware: indicates whether the cursor can identify the bound cursor. The value is yes | No. A cursor that has been marked as a shared cursor that can be identified and bound in the cursor cache is called a cursor that can be identified and bound.

 

1.3.2 v $ SQL _cs_histogram

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

 

1.3.3 v $ SQL _cs_selecti.pdf

Displayed as a selective cube or range in the cursor where each predicate containing the bound variable and used for its selectivity in the cursor sharing check is stored. It contains the lower and upper limits of the predicate text and selective range.

 

1.3.4 v $ SQL _cs_statistics

Adaptive cursors share the execution of monitoring queries and collect relevant information within a period of time. You can use this information to determine whether to switch to the automatically bound cursor for this query. This view summarizes the collected information so that you can make the following decisions: for execution examples, it tracks the number of lines processed, the number of buffer obtained, and the CPU time. If you use a bind set to build a cursor, the value of the peeked column is yes; otherwise, the value is no.

 

 

Ii. MOS description

Description of ACS on MOS:

Adaptive cursor sharing in 11g [ID 836256.1]

Adaptive cursor shard Overview [ID 740052.1]

 

2.1 Introduction to 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 selectistmwas 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 "predicateselecti.pdf ".

 

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 wocould peek at the binds andgenerate selectivity based on the bind and the underlying column statistics. thismethod cocould sometimes lead to plans being generated that were notrepresentative of the general query usage if the BIND selectition of Theinitial execution of a statement varied from the selectivity of records 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 previuslyused 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.

-- Introduced the adaptivecursor sharing feature in Oracle 11g. This feature monitors statistics on statement execution and tries its best to bind variable values to the same SQL statement, use different execution plans.

2.2 advantages of adaptive cursor sharing (ACS advantages)

Adaptive cursor sharing is asolution to give us the ability 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.

 

Previusly, too wowould either use literal values instead of binds, which cowould lead toexcessive hard parsing, or apply a fixed plan that wocould be a compromise ofperformance between different bind selectivities.

2.3 disadvantages of adaptive cursor sharing (ACS disadvantage)

There is some extra overheadassociated with adaptive cursor sharing in the form :-

(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 bindselecti.pdf.

(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 sensiti.pdf)

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.

-- When we use operations such as <= in SQL, a different execution plan (cursor) is generated when the variable bound is changed, and the cursor is marked as bind sensitive, bind-sensitive cursor is a cursor of the optimal execution plan based on the value of the bound variable. This is 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 passed med 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 wocould not occur.

 

If any of the following checks fail ECs will bedisabled:

-- ECs is disabled in the following cases:

(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.

The number of Bound variables cannot exceed 14, because ACS will expire when the number of Bound variables exceeds 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_policable.

The BIND context informationcan be viewed via v $ SQL _cs_selecti.pdf, V $ SQL _cs_statistics andv $ SQL _cs_histogram

 

V $ SQL _cs_selecti1_exposesthe valid selecti1_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 shoshould be contacted to assist withdiagnosing the issue.

 

Iii. ACS enabling and disabling

 

The three parameters related to ACS are:

_ Optimizer_adaptive_cursor_sharing

_ Optimizer_extended_cursor_sharing

_ Optimizer_extended_cursor_sharing_rel

 

The parameter explanation is not found in the Oracle 11gr2 reference manual, but we can view the Oracle parameters to understand the role of the three parameters.

 

Here we will use a view: all_parameters. For the source code of this view, refer:

Oracle all_parameters View

Http://blog.csdn.net/tianlesoftware/article/details/6641281

 

SQL> select * From all_parameters where name like '% _ optimizer _ % _ cursor_sharing % ';

 

Returned results:

 

We can modify these three parameters when the database is running and the parameters take effect immediately.

 

To disable ACS, follow these steps:

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;

 

 

About the issue of ACS, viadeazhu on itpub has a more in-depth study, written in very detail, URL: http://space.itpub.net/15415488/viewspace-621535

 

 

About the changes of these parameter values in Oracle 10g and 11g, MOS has a document description:

Parameters to change 11.2.0.1 to 10.2.0.4 [id1_4553.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_selecti.pdf" = 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_assist_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

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://blog.csdn.net/tianlesoftware

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware