In this Document
| Purpose |
| Scope |
| Details |
| Introduction |
| Advantages and Disadvantages of Adaptive Cursor Sharing |
| Advantages of Adaptive Cursor Sharing |
| Disadvantages of Adaptive Cursor Sharing |
| Extended Cursor Sharing (Bind Sensitivity) |
| Adaptive Cursor Sharing (Bind Aware) |
| Monitoring |
| Known Issues with Excessive Child Cursors |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.1.0.6 and laterInformation in this document applies to any platform.
PURPOSE
The purpose of this document is to provide an overview to the Adaptive Cursor Sharing (ACS) feature that was introduced in 11g
SCOPE
The document contains a basic overview and background as well as issues that customers should be aware of.
DETAILS
Introduction
With the introduction of the Cost Based Optimizer (CBO) a number of changes were made to calculate the selectivity of a predicate, which in turn affected how the query was optimized. The selectivity was based on the number of distinct values for a given column or a predefined percentage of rows depending on the relational operator that was used. For more details on predicate selectivity see:
This worked well for data that was evenly distributed but had limitations in applications where data was skewed.
With 9i, a new feature "Bind Peeking" was introduced to try to get around the issues associated with the selectivity of a bind variable. During hard parsing of a query using bind variables, the binds are "peeked at" and selectivity is generated based on the bind and the underlying column statistics. This method is successful but if the bind selectivity of the initial execution of a statement varied from the selectivity of subsequent executions with different sets of binds this could sometimes lead to plans being generated that were not representative of the general query usage and which performed poorly.
In Oracle 10g, this was especially noticeable since the default statistic gathering methodology changed to gather histograms automatically. This meant that selectivity previously generated based on the number of distinct values was now generated based on histograms. This gave the optimizer better information about the distribution of skewed data, the impact of which being that the first execution of a query using binds that were representative of the general query usage was particularly important. For more information on this see:
Document 430208.1 Bind Peeking By Example
In Oracle 11g, Adaptive Cursor Sharing was introduced to provide a solution to these issues. Adaptive Cursor Sharing monitors the execution statistics for candidates queries and makes it possible for the same query to generate and use different execution plans for different set of binds values. See:
11g Release 2 (11.2)
Part Number E16638-07
11.1.3.1 Adaptive Cursor Sharing
http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#PFGRF95174
https://blogs.oracle.com/optimizer/entry/why_are_there_more_cursors_in_11g_for_my_query_containing_bind_variables_1
https://blogs.oracle.com/optimizer/entry/update_on_adaptive_cursor_sharing
https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force
Advantages and Disadvantages of Adaptive Cursor Sharing
Advantages of Adaptive Cursor Sharing
Adaptive cursor sharing is a solution to provide the shareability of binds, with the plan adaptability of literals.
With adaptive cursor sharing the database can adapt execution plans based on the selectivity of values used in bind variables. This resolves the situation where the plan used for one set of bind variables is inappropriate for another set.
Previously, workarounds employed would either use literal values instead of binds, which could lead to excessive hard parsing, or apply a fixed plan that could cause performance issues.
Disadvantages of Adaptive Cursor Sharing
There may be some extra overhead associated with Adaptive Cursor Sharing:
- 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 bind selectivity.
- More Child Cursors (SGA) - It is recommended that some consideration be taken to increase the size of the shared_pool on upgrade from 10g to 11g, given the extra cursors that may be required to accommodate this feature.
- More Work to Match the Cursor (CPU) - More Child Cursors and the requirement to match a query to the best execution plan for its predicate selectivity.
- If you encounter an issue with query performance, in some cases ACS may make the issue more difficult to identify and repeat if a new plan is generated and accepted that then makes the problem "disappear". The other side to this is that the query now performs better without user intervention.
Extended Cursor Sharing (Bind Sensitivity)
The first step towards ACS is Extended Cursor Sharing - where a query is recognised as "Bind Sensitive". This means that the best plan for a query has potential to be different according to the bind variables supplied to it. In effect this just means the cursor is being monitored to see if it should be made bind aware.
A cursor is marked as Bind Sensitive if:
-
query is executed with bind peeking
-
binds using any of the following relational operators = < > <= >= != or a user defined bind operator e.g. contains(e.job,:job,1)>0,
From 11.2.0.2 the "LIKE" operator is also supported.
-
A histogram exists on the column containing the bind value.
In other words, a cursor is marked Bind Sensitive if a change in the bind variable value may lead to a different plan.
Apart from checking for a valid operator there are also a number of subsequent bind sensitivity checks that need to be performed before it can be marked as bind sensitive. If any of these checks fail, the cursor will not be marked as bind sensitive and adaptive cursor sharing will not occur and Extended Cursor Sharing will be disabled :-
- Extended cursor sharing has been disabled
- The query has no binds
- Parallel query is used
- Certain parameters like ("_optim_peek_user_binds"=false) are set
- You are using a /*+ NO_BIND_AWARE */ hint
- Outlines are being used
- Query is recursive
- The number of binds in a given sql statement is greater than 14.
SQL Plan Baselines are designed to work with Adaptive Cursor Sharing. Any valid enabled SQL Plan Baselines Plans can be used by Adaptive Cursor Sharing. This means if you have more than one enabled baseline for a given SQL statement ACS can use one or more of those baselines at the same time.
When all the criteria are met the cursor is marked as bind sensitive and a "sharing context" containing information about execution statistics of the cursor is created and stored against the cursor.
Cursors that are marked as bind-sensitive can been identified by the column IS_BIND_SENSITIVE having a value "Y" in V$SQL or V$SQLAREA.
Adaptive Cursor Sharing (Bind Aware)
If there is significant variation in the row source cardinality for executions of the same sql statement in consecutive executions a cursor will be marked as bind aware.
For more information about this please see :-
Document 1115994.1 Introduction to Adaptive Cursor Sharing Concepts and Multimedia Demo [Video]
https://blogs.oracle.com/optimizer/entry/why_are_there_more_cursors_in_11g_for_my_query_containing_bind_variables_1
Monitoring
V$SQL can be used to see whether a cursor is_bind_sensitive, is_bind_aware, or is_shareable.
Bind context information can be viewed via V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS and V$SQL_CS_HISTOGRAM
V$SQL_CS_SELECTIVITY displays the valid selectivity ranges for a child cursor in extended cursor sharing mode.
A valid range consists of a low and high value for each predicate containing binds. Each predicate's selectivity (with the current bind value) must fall between the corresponding low and high values in order for the child cursor to be shared.
V$SQL_CS_STATISTICS contains the raw execution statistics used by the monitoring component of adaptive cursor sharing.
A sample of the executions is monitored. This view shows which executions 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 summarizes the monitoring information stored by adaptive cursor sharing.
This information is used to decide whether to enable extended cursor sharing for a query. It is stored in a histogram, whose bucket's contents are exposed by this view.
Known Issues with Excessive Child Cursors
There is a possibility that Adaptive Cursor Sharing may compound problems in which excessive numbers of child cursors are generated. This may lead to performance degradation as large numbers of child cursor can put space pressure on the shared pool and may also lead to an increase in mutex X waits for that cursor.
Checklist to avoid known issues
1. Ensure that cursor_sharing is not set to SIMILAR. In 11g this setting is not recommended and is deprecated in future releases.
2. If there are high version counts check v$sql_shared_cursor and search My Oracle Support for notes that may allude to the cause of the excessive cursors.
If there are still excessive child cursors, then Oracle Support should be contacted to assist with diagnosing the issue.
REFERENCES
NOTE:1518681.1 - FAQ: Adaptive Cursor Sharing (ACS) Frequently Asked QuestionsNOTE:13456573.8 - Bug 13456573 - Many child cursors / ORA-4031 with large allocation in KGLH0 using extended cursor sharing
NOTE:6644714.8 - Bug 6644714 - High number of child cursors with adaptive cursor sharing
NOTE:430208.1 - Bind Peeking By Example
NOTE:68992.1 - Predicate Selectivity
NOTE:1169017.1 - ANNOUNCEMENT: Deprecating the cursor_sharing = 'SIMILAR' setting
NOTE:387394.1 - Query using Bind Variables is suddenly slow
NOTE:1115994.1 - Introduction to Adaptive Cursor Sharing Concepts and Multimedia Demo [Video]
NOTE:836256.1 - Adaptive Cursor Sharing: Worked Example
810
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
