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 11.1.0.6. 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 11.1.0.6, while with 11.1.0.7 the same test only generates 7 child cursors. Two code changes went into 11.1.0.7 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%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
       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%';

CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------ ---------- ----------- ----------------- -------------
       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%';

CHILD_NUMBER EXECUTIONS  IS_SHAREABLE
------------ ---------- -------------
       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.
Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

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.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today