Monday Feb 11, 2013

How do adaptive cursor sharing and SQL Plan Management interact?

We've received a lot of questions about how adaptive cursor sharing (ACS) and SQL plan management (SPM) interact.  We discussed this briefly in one of the original SPM posts, but in this post, we'll explain the concepts of how the two features interact, and show an example. 

The simplest way to reason about the interaction is to remember that they are responsible for two different tasks.  ACS controls whether or not a child cursor is shared on a particular execution.  For each execution of the query, ACS considers the current bind values and decides if an existing child cursor can be shared or if the optimizer should be given the chance to find a better plan for the current bind values.  SPM controls which plans the optimizer may choose.  If a child cursor is bind-aware, the decision to share or not is made irrespective of whether the query is controlled by SPM.  But once the query and its current bind values are sent to the optimizer for optimization, SPM constrains the optimizer's choice of plans, without regard to whether this query is being optimized due to ACS.

[Read More]

Monday Feb 13, 2012

How do I force a query to use bind-aware cursor sharing?

Way back in our original post on adaptive cursor sharing, a commenter asked if there was a way to skip the monitoring for a query and go straight to bind-aware cursor sharing.  We mentioned then that we would add a hint for this purpose, but we never addressed the new hint here on the blog.  Starting in, the BIND_AWARE hint can be used to force bind-aware cursor sharing from the first execution.[Read More]

Wednesday Jan 25, 2012

My cursor wasn’t shared because of STB_OBJECT_MISMATCH. What does that mean?

I got a question recently about cursor sharing and why a cursor wasn’t shared due to an STB_OBJECT_MISMATCH.I thought the question and subsequent investigation might make an interesting blog post. Let me begin by outlining the question in detail.[Read More]

Friday Mar 12, 2010

Explain adaptive cursor sharing behavior with cursor_sharing = similar and force.

Here's another question that was submitted during the OpenWorld Optimizer Roundtable. It's a common question that we've discussed a little bit in a couple other posts, but we wanted to summarize everything in one place. First, let's quickly review what the different values for the cursor_sharing parameter mean. We discussed this behavior in some detail in an earlier post about cursor_sharing. Below is a summary of the behavior of the different values in different cases (copied from the earlier post):

EXACT (No literal replacement) Worst possible case - each stmt issued has its own parent cursor Best possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt
FORCE Best possible case as only one parent and child cursor for each distinct stmt Potentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan
SIMILAR without histogram present Best possible case as only one parent and child cursor for each distinct stmt Potentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan
SIMILAR with histogram present Not quite as much space used as with EXACT but close. Instead of each stmt having its own parent cursor they will have their own child cursor (which uses less space) Best possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt

Adaptive cursor sharing (ACS) is another feature we've blogged about before, which allows the optimizer to generate a set of plans that are optimal for different sets of bind values. A common question is how the two interact, and whether users should consider changing the value of cursor_sharing when upgrading to 11g to take advantage of ACS. The simplest way to think about the interaction between the two features for a given query is to first consider whether literal replacement will take place for a query. Consider a query containing a literal:

select * from employees where job = 'Clerk'

As we see from the table above, the treatment of this query by literal replacement will depend on the value of the cursor_sharing parameter and whether there is a histogram on the job column. Here are the interesting cases:

  1. Cursor_sharing = exact. No literal replacement will take place, and the optimizer will see the query as is.
  2. Cursor_sharing = force. Whether there is a histogram or not, literal replacement will take place, and the optimizer will optimize the query as if it were: select * from employees where job = :b Bind peeking will take place, so that the value "Clerk" is used to generate cardinality estimates for the query. Subsequent executions of this query differing only in the literal value will share the same plan.
  3. Cursor_sharing = similar. There are two different cases for this:
    1. There is a histogram on the job column. In this case, literal replacement will not take place. The presence of a histogram indicates that the column is skewed, and the optimal plan may depend on the literal value. Hence, the optimizer sees the query as: select * from employees where job = 'Clerk' and subsequent executions with a different literal will not necessarily use the same plan.
    2. There is no histogram on the job column. This indicates that the column is not skewed, and the optimizer is likely to choose the same plan no matter the literal, so literal replacement takes place.
Now that we know when literal replacement will take place, and what the query looks like to the optimizer, we can consider adaptive cursor sharing. If literal replacement takes place, and the query that the optimizer optimizes contains a bind, then adaptive cursor sharing can take place. To adaptive cursor sharing, a bind variable is a bind variable, whether it comes from the user query or is inserted by literal replacement. On the other hand, if the query contains only literals (no binds), adaptive cursor sharing will not take place. In our example above, adaptive cursor sharing can be considered for cases 2 and 3.2. For case 3.2, it is likely that the optimizer will choose the same plan for different values of the literal. In case 2, if there is a histogram, then the optimizer may choose different plans depending on how popular the literal value is.

This example shows that if you use histograms, and want the optimizer to choose an optimal plan for different literal values using ACS, then you should set cursor_sharing to force. If it is set to similar, then literal replacement will not take place, and a child cursor will be created for each value of the literal. Setting cursor_sharing to similar effectively disables ACS for these kinds of queries. By setting cursor_sharing to force and letting adaptive cursor sharing kick in, the optimizer can choose optimal plans for different values, but if the same plan is appropriate for several values, they will share a single child cursor. Historically, cursor_sharing=similar has been recommended as a middle ground between no literal replacement (which causes a lot of cursors to be generated) and forced literal replacement (which causes a potentially sub-optimal plan to be shared for all literals). We now recommend using adaptive cursor sharing along with cursor_sharing=force instead.

So far we have only discussed cursor sharing in the presence of histograms. There are other cases where the optimizer's choice of plan can depend on the specific literal that appears in the query, for instance when binds appear in range predicates or when a bind value falls outside of a column's range (according to the optimizer statistics). Binds appearing in these kinds of predicates are also considered by adaptive cursor sharing, whereas they are not considered by cursor_sharing=similar.

Tuesday May 26, 2009

Why do I have hundreds of child cursors when cursor_sharing is set to similar in 10g

Recently we received several questions regarding an unusual situation where a SQL Statement has hundreds of child cursors. This is in fact the expected behavior when

  1. CURSOR_SHARING is set to similar
  2. Bind peeking is in use
  3. And a histogram is present on the column used in the where clause predicate of query
You must now be wondering why this is the expected behavior. In order to explain, let's step back and begin by explaining what CURSOR_SHARING actually does.[Read More]

Sunday Apr 12, 2009

Update on Adaptive Cursor Sharing

In December 2007 we did a post on the new Oracle Database 11g feature Adaptive Cursor Sharing called Why are there more cursor in 11g for my queries containing binds. In this post we explained that in Oracle Database 11g it was possible to have multiple execution plans for a single statement that uses bind variables, thus ensuring that the best execution plan will be used depending on the bind value. However, it's been brought to our attention by Ric Van Dyke from Hotsos that you may get a lot more cursors than you were expecting especially if you are using So why is that? In Ric's case he had a simple query
Select /* ACS_1 */ count(c1) from t where c1 = :B1;
The column c1 has 12 distinct values but the data in that column is skewed. There are three possible execution plans for this query,

Full table scan
Index range scan
Fast full scan

When Ric ran through all 12 distinct values for B1 he found he got 11 child cursors in, while with the same test only generates 7 child cursors. Two code changes went into to reduce the number of child cursors, which is why there are only 7 but why do we see 7 when there are only 3 possible plans?

As we discussed in our original blog post the first time this statement is executed the optimizer peeked at the actual value of the bind variable b1 and uses that value to determine the initial execution plan. The next value passed in for B1 will use the same plan but behind the scenes, Oracle monitors the behavior of the queries, and determined that the different bind values caused the data volumes manipulated by the query to be significantly different. Based on this difference, Oracle "adapts" its behavior so that the same plan is not always shared for this query. Hence on the next execution, a new plan is generated based on the current bind value. So now the child cursor count is 2.

SQL> select child_number, executions, buffer_gets,
2    is_bind_sensitive, is_bind_aware
3  from v$sql
4  where sql_text like 'select /*ACS_1%';

------------ ---------- ----------- ----------------- -------------
       0          2         600      Y                 N
       1          1        1821      Y                 Y
The new child cursor is marked bind aware. As Ric ran through all 12 distinct values (running each value twice) Oracle continued to consider whether the current bind value was expected to result in a significantly different data volume than those bind values for which cursors had already been created. Each time Oracle saw a significant change, it triggered the statement to be hard parsed. In Ric's case this happened 5 more times. During those additional 5 hard parses the optimizer came up with two distinct plans. But because Oracle did 5 hard parses, 5 new child cursors were generated even though some of those cursor had the same execution plan.

SQL> select child_number, executions, buffer_gets,
2    is_bind_sensitive, is_bind_aware
3  from v$sql
4  where sql_text like 'select /*ACS_1%';

------------ ---------- ----------- ----------------- -------------
       0          2         600      Y                 N
       1          1        1821      Y                 Y
       2          1         107      Y                 Y
       3          1         218      Y                 Y
       4          1         430      Y                 Y
       5          1         821      Y                 Y
       6          1        1007      Y                 Y
This is the expected behavior. However, if we look at how many of these cursors are marked shareable we find only two of the 7 cursors are , which means the other 5 cursors will be aged out if cache space is required. So, they should not cause any contention.

SQL> select child_number, executions,
2         is_shareable
3  from v$sql
4  where sql_text like 'select /*ACS_1%';

------------ ---------- -------------
       0          2        N
       1          1        N
       2          1        N
       3          1        N
       4          1        N
       5          1        Y
       6          1        Y
So, as the system get busier and the non-shareable plans age out we will eventually have at most 3 cursors for this SQL statement (one for each execution plan), as you would expect.

You might wonder if this behavior scales with the number of bind values. In other words, if you run the query with 20 different bind values, would that result in 10 or more hard parses? This is not the case. During the initial hard parses, the optimizer is essentially mapping out the relationship between bind values and the appropriate execution plan. After this initial ramp up period, a steady state will eventually be reached, so that executing with a new bind value will result in picking the best child cursor in the cache, without requiring a hard parse. In fact, Ric's test table contained over 30 possible values for the bind (though he only used 12 in his test queries). Changing the test to use all of the possible values results in only one additional hard parse.

Monday Dec 03, 2007

Why are there more cursors in 11g for my query containing bind variables?

Oracle introduced a new feature, adaptive cursor sharing, in 11g, to improve the plans that are selected for queries containing bind variables.  This feature can result in more cursors for the same query containing bind variables.  We'll explain why in this article.  Before we get into the details, let's review a little history.

Oracle introduced the bind peeking feature in Oracle 9i. With bind peeking, the Optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. This allows the optimizer to determine the selectivity of any WHERE clause condition as if literals have been used instead of bind variables, thus improving the quality of the execution plan generated for statements using bind variables.[Read More]

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.


« July 2016