Oracle9i cursor sharing enhancements   Donald K. Burleson

An exciting new internal feature of Oracle9i allows the cost-based SQL optimizer to change execution plans even when optimizer plan stability is used.  This is called “peeking” and allows the cost-based SQL optimizer to change execution plans when the value of a bind variable would cause a significant change to the execution plan for the SQL.

Cursor sharing has a processing overhead at optimization time, and it should only be used if an application generates dynamic SQL or applications that must embed literal values.  PL/SQL applications should be written for the SQL to use bind variables, and these systems will not benefit from cursor sharing.

To illustrate with a simple example, consider a simple example where our cursor_sharing parameter is set to “force”. This will change all SQL literal values to host variables inside the library cache.

Now, let’s assume that we have an index on a region column of a customer table. The region column has four values, north, south, east and west.  The data values for the region column are highly skewed with 90% of the values in the south region. 

Hence, the cost-based SQL optimizer would be faster performing a full-table scan when south is specified, and an index range scan when east, west, or north is specified. When using cursor sharing, the cost-based SQL optimizer changes any literal values in the SQL to bind variables.  Hence, this statement would be changed as follows:

select
   customer_stuff
from
   customer
where
   region = ‘west’;

The transformation replaces the literal west with a host variable:

select
   customer_stuff
from
   customer
where
   region = ‘:var1’;

In Oracle9i, the cost-based SQL optimizer “peeks” at the values of user-defined bind variables on the first invocation of a cursor. This lets the optimizer determine the selectivity of the WHERE clause operator, and change the execution plan whenever the south value appears in the SQL.

This enhancement greatly improves the performance of cursor sharing when a bind variable is used against a highly skewed column.  In Oracle9i we also see a new setting for cursor_sharing called “similar”.

With cursor_sharing=similar, Oracle will switch in the bind variables if doing so makes no difference to the outcome, but will use literal values if using bind variables would make a significant difference to the outcome.

Remember, Oracle9i cursor sharing peeking is only useful for highly skewed column distributions – Peeking is only useful when Oracle9i detects that the value of a column literal will affect the execution plan.  For index columns without excessive skew, peeking is a wasted step.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.