Update on Adaptive Cursor Sharing
By Allison on Apr 12, 2009
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 184.108.40.206, while with 220.127.116.11 the same test only generates 7 child cursors. Two code changes went into 18.104.22.168 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 YThe 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 YThis 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 YSo, 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.