Database -- Methods for preventing Bind Peeking and ACS (Adaptive Cursor Sharing)

To reason that makes me write this post is a question that asked by one of our customers. They have both Microsoft and Oracle Databases, but they work with Microsoft Sql Server more intensively.
Sql Server has such an capability that it s not caching the ad-hoc queries (mostly executed by users) in its Library Cache. This seems like an opportunity to control the library cache usage, by preventing ad-hoc sql queries overloading the shared memory. Its another benefit is that, it can prevent bind variable peeking, as mentioned by the customer.  So they have wanted to configure Oracle to treat in the same way...

Bind variable peeking is not a new thing. It is basically about, peeking a bind variable and then constructing the Execution plan for a specific query according to that particular bind variable value. This brings the opportunity for preventing the hard parses and memory consumption.. On the other hand; this also brings out an issue if the underlying table has not a uniform data distribution.

Lets suppose a table X has a column Col1 and it has values like A,B,C ....   We have also an index on Col1.. (table and index analyzed)

The row counts are like below;
C1      Count
------------
A           1
B           1000000000
C          245

Now suppose we just started our database , and one of our users executes an sql statement like ;
select col1, col2 from X where Col1=:b1;    --> and the user supplies the bind variable 's value as 'A'

Normally, Oracle makes an hard parse in this situation. It does symantic checks, syntax checks and etc.. In that phase, Oracle builds an Execution Plan, too.. Execution plan is a very important aspect for CBO. Oracle uses Explain Plan to retrieve the data..

By looking to the row count(histograms and etc.),  Oracle will decide to use the index on Col1,that is, it generates the execution plan stating the usage of that index and retrieves the data very quicky..

Note that Oracle saves this information in its shared memory for upcoming sql statements.. One of the thing that makes Oracle to cache the cursor is, to eliminate the unnecessary I/O's and Cpu consumption for regenerating the Execution plan. (in case-- the same sql will be executed again)
This is a good thing but lets suppose the following scenario;

Just after the sql statement (select col1, col2 from X where Col1=:b1; ) is executed, another session executes the same sql statement with a different bind value  (select col1, col2 from X where Col1=:b1;  <--B).. This time the sql is executed with the conditon Col1='B') ..
Just because the structure of the sql seems  the same from the Oracle perspective, Oracle will use the same execution plan for that sql, too. This seems normal as it is really the same sql.. But, this time it is executed with Col1='B'... Bind peeking arise from this weakness.. Because of the bind peeking, Oracle will use that cached execution plan and use that index to retrieve 1000000000 B record...  On the other hand, it should use a Full table Scan, as it s so expensive to read such a big number of rows (big percentage of rows, as well)  from an index.. So this is wrong..

There are some ways to prevent bind variable peeking though;

we can avoid using Cursor_sharing parameter set as FORCE.. Cursor sharing parameter, when set to FORCE, will convert the conditions using literals to conditions using bind variables..
As, this will increase the chance for bind variable peeking, it s good  to set this parameter to SIMILAR or EXACT..(according to the situation)
On the other hand, this is not enough and it s not the solution for the problem, as we will always have queries with binds..

Another approach is to use different sql statements according to the data;
if (COLX > 20 )
then
  open l_cursor for
  select * from t alias1 where ...
else
   open l_cursor for
   select * from t alias2 where ...
end if;

Thus, different sql statement will be executed for differents values . There will be no bind peeking.. On the other hand, it requires an intervention to the code. That s why, may be considered as an expensive way.

Using stored outlines, not gathering statistics and disabling the bind peeking with the _optim_peek_user_binds underscore parameter can be considered as solutions for bind peeking.
Reference cursor can be another way, as they will trigger a parse on each and every open.

Analyzing the objects can also be used to change the cached Execution plans.. Automatic dependency tracking makes Oracle flush the Execution plans from the shared pool.

All of the methods explained above bring us indirect ways to handle the bind variable peeking problem..

So lets look at what Oracle developed in 11g to handle with it;

In 11g, there is new feature called Adaptive Cursor Sharing(ACS). It is enabled by default and can not be disabled. It enables a single statement that contains bind variables to use multiple execution plans. ACS supplies two terms with its methodology. Bind sensitive and Bind aware..
A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.

So what happens with ACS in 11g is as follows;

When a query is first executed, Oracle generates its plan and put it into cache.. If Oracle decides that the execution plan may need to change according to the bind variable values, it marks the cursor as bind sensitive. When the query executed again, Oracle executes the related statement with the same execution plan, but as it knows that query is a bind sensitive query, it compares the runtime statistics of the query with the statistics gathered in its previous run. If Oracle finds a remarkable difference between these two statistics, it will mark the query as Bind Aware.. By marking the query Bind aware, Oracle guarantees a hard parse, thus generating a new execution plan on the next run of that query..

This is how Oracle deals with bind variable peeking in the new releases..

In addition, the "BIND_AWARE" hint can also be used to make a cursor skip the bind sensitive phase and directly come to the Bind aware phase.. But this approach is not considered as a good recommendation..

In conclusion, ACS brings us a good and transperent way to deal with bind peeking in Oracle databases. By the help of ACS, we dont have to think for ways to deal with the performance problems caused by bind peeking.. In addition to that, in my opinion; as long as Oracle can manage it with its LRU implementation and features like ACS; caching ad-hoc queries are not a problem, but a choice..