How 11g Tries To Improve the Use of Bind Variables

I'm sure that all of us who have just about any Oracle experience have encountered the enigmatic SQL query that ran 2 seconds yesterday but 102 seconds today. What can cause this unpredictable behavior?

The answer is that many factors can cause this degradation, but one of the most common causes is execution plan invalidation. When the optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor, it is called "bind peeking". This usually has a positive effect on performance because it 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.

Bind peeking becomes a problem when a bind variable is used with a table column that has skewed data. For example, we have a table of 20000 rows that contains a "part_num" column. Part number "A110" occurs 3 times in our table (3 rows) but part number "B235" occurs only 8000 times (800 rows). An index is created on the part_num column and when statistics are gathered, the skew in the data distribution is detected and a histogram is generated. When a cursor that contains a select query using bind variables for a conditional statement is first invoked, the optimizer can peek at the value of the user defined bind variable and determine the execution plan which is then stored in the shared pool. If the bind variable for part_num is set to "A110" at first cursor invocation, the optimizer peeks at the bind variable and sets the plan access accordingly. However, if the value of the bind variable is set to the value "B235" and the cursor is parsed again, the old plan will still be in place and will be used. Thus, an access plan not suited for optimal performance for the value of the bind variable is used, thereby degrading performance due to the difference in buffer gets that are required. Before 11g, one of the ways to take care of this was to flush the shared pool and cause the query to be reparsed with the new bind variable values.

In 11g, Oracle has taken a step to address the above problem by introducing Adaptive Cursor Sharing. ACS allows for more cursors for the same query containing bind variables. In other words, the optimizer will allow multiple execution plans to be used for a single statement that uses bind variables. This ensures that the best execution plan will be used depending on the bind value. So, taking the example above, 11g would allow two plans to be generated for the difference in selectivity of the part_num column bind variable values.