An exciting new internal feature of Oracle 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.
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:
The transformation replaces the literal west with a host variable:
In Oracle, 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 Oracle we also see a new setting for cursor_sharing called “similar”.
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.
Oracle cursor sharing peeking is only useful for highly skewed column
distributions – Peeking is only useful when Oracle 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:http://www.rampant-books.com/book_2003_1_Oracle_sga.htm
Copyright © 1996 - 2012 by Burleson Enterprises. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.