Adaptive Cursor Sharing:

 

This document will explain step by step to analyze adaptive cursor sharing.

Let’s start with the basics. Let’s create a table as a copy of dba_objects.

 

SQL> create table t as select * from dba_objects;

Now, if we run a query like ‘select object_name from t where object_id=1027’, then it must go through the full process of hard parsing. Each time we put a literal as the object_id value, Oracle sees it as a new statement and does a hard parse. And as we know, a hard parse is an expensive operation, one that we must try to avoid as much as possible. Major problems that result from using literal values include the following:

• There is a security threat as applications that concatenates literals are soft targets for SQL Injection attacks. Not using literals, i.e. using Bind Variables eliminates that threat.

• For every statement that is hard parsed, cursors are not being shared, and more memory is being consumed.

• Hard Parsing implicitly increases latch contention, as Oracle must latch the shared pool and library cache while doing a hard parse.

Okay, let’s first run the following three queries, each one using a different object_id, i.e. a new literal for each statement:

select object_name from t where object_id=1027;

select object_name from t where object_id=1028;

select object_name from t where object_id=1029;

So far, so good. So, let’s check how Oracle internally views each of these statements. For that let’s run the following statement:

SQL> select sql_text, sql_id, version_count, hash_value from v$sqlarea where sql_text like ‘select object_name from t%’;

And we can see the query of V$SQLAREA shows that the three statements require three different parent cursors. As shown by VERSION_COUNT, each parent cursor requires its own child cursor.

So, to overcome the problem of hard parsing every time, we can use bind variables to share the cursor.

Let’s have a look at the following example:

So, what we have done is that we have run three different statements with three different object_id values, but used a bind variable which we defined with the object_id, before running the statement. The expectation here is that Oracle will share the cursor, and define the object_id value at run time. Let’s see how it actually worked:

The VERSION_COUNT value of 1 indicates that the database reused the same child cursor rather than creating three separate child cursors. Using a bind variable made this reuse possible.

However, there is a catch. When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. What Oracle does is, at the outset it peeks at the bind variable value for the initial hard parse, and uses that information to generate an execution plan for all subsequent values of the bind variable, thus leaving itself open to working with sub optimal plans, because the plan produced during the initial hard parse may or may not be the best plan for all values of the bind variable.

Let’s see whether supposedly similar statements can produce different execution plans or not.

Let’s first create an index on the object_id column of the table t.

create index i1 on t(object_id)

Then let’s run the following three statements and see the execution plan for each one of them.

In the third case where we are selecting a significantly larger number of rows from the table, the optimizer considers a full table scan more efficient than an index scan, thus validating what we were guessing-that execution plans do change for ‘similar-looking statements’ but with very different literals.

But before we move ahead let’s check the same three statements using bind variables. As a preparatory step before actually running our queries, let us flush the shared pool and buffer cache, just so we are sure about the results we get.

alter system flush shared_pool;

alter system flush buffer_cache;

It is clear that Oracle continues to use the plan developed during the initial hard parse, even though we know the INDEX RANGE SCAN is not the most optimal plan for object_id<19000, as we already saw a FULL TABLE SCAN occurring when we gave the optimizer the benefit of literals in the earlier example.

So, although there are some obvious benefits of using bind variables, it is definitely not the solution for all our SQL tuning problems. With bind peeking, the optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. The optimizer determines the cardinality of any WHERE clause condition as if literals had been used instead of bind variables. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, this plan may not be good for all values.

So herein comes the benefit of ADAPTIVE CURSOR SHARING. In adaptive cursor sharing, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. Let’s say the optimizer chooses a particular plan for bind value 1000, then it can very well choose a different plan for bind value 19000, which may be relatively the more optimal plan for that value of the bind variable.

Thus, cursor sharing is “adaptive” because the cursor adapts its behavior so that the optimizer does not always choose the same plan for each execution or bind variable value. So, the optimizer automatically detects when different execution of a statement would benefit from different execution plans.

Adaptive cursor sharing works only if you are using bind variables and have a histogram on at least one of the columns that is being used in the WHERE clause of the query.

Let’s create a new table ‘S’ as a copy of dba_objects and gather statistics on it as well as create a histogram on the object_id column.

The next step would be to run a query on this table using a bind variable.

With Adaptive Cursor Sharing, two additional columns are there in V$SQL. IS_BND_SENSITIVE and IS_BIND_AWARE. A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.

The database has examined the bind value when computing cardinality, and considers the query “sensitive” to plan changes based on different bind values. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

The optimizer uses the following criteria to decide whether a cursor is bind-sensitive:

• The optimizer has peeked at the bind values to generate cardinality estimates.

• The bind is used in an equality or a range predicate.

For each execution of the query with a new bind value, the database records the execution statistics for the new value and compares them to the execution statistics for the previous value. If execution statistics vary greatly, then the database marks the cursor bind-aware.

At this point, let us see what is the status of the columns IS_BIND_SENSITIVE and IS_BIND_AWARE for the query we ran. We notice that IS_BIND_SENSITIVE is flagged to ‘Y’ while IS_BIND_AWARE is flagged as ‘N’.

This means that Oracle is ‘sensitive’ to the fact that bind variable is being used and the optimal plan is predicated on the bind variable value, i.e. whatever value we pass in the bind variable subsequently will determine whether the plan is good, bad, or maybe somewhere in-between.

Let’s try one more time with a bind variable value not too different from what we used earlier:

The cursor continues to be bind sensitive. Now, let us do something different. Let us use a bind variable value which is significantly different, and let us see what we get:

1. So Oracle, now in its wisdom decides to make the cursor bind aware as well. Because the cursor is bind-aware, the database does the following:

Determines whether the cardinality of the new values falls within the same range as the stored cardinality. If it falls within that range it reuses the execution plan in the existing child cursor.

What happens if it doesn’t fall in the range of stored cardinality? In that case, Oracle does a hard parse and creates a new child cursor with a different execution plan, stores metadata about the predicate, including the cardinality of the bound values, in the cursor and executes the cursor.

So, the essential point here is, we now have two child cursors catering to two different sets of bind variable values.

Great! What next? Here it starts getting really interesting. Let’s say we now use a bind variable value which doesn’t match the stored cardinality of the existing child cursors, so the optimizer goes through the painful process of hard parsing and creates another child cursor which it then executes. Assume this execution plan used an index. Oracle then checks and sees that one of the existing child cursors had also used an index scan. Oracle now tries to optimize things by merging the two child cursors containing index access plans. This implicitly involves storing the combined cardinality statistics into one child cursor and deleting the other one. This is really efficient, because instead of two child cursors, we now have one child cursor which can be reused, and it sores the combined cardinality of more than one bind variable value.

So, in conclusion it can be said that Adaptive Cursor Sharing is a really cool feature in Oracle that allows us to use to use Bind Variables in an unfettered way, leveraging the reuse of child cursors without having to compromise with living with suboptimal execution plans.

********************************************************************************************************************************************