Question: I understand that Oracle11g
now has an improved method for cursor_sharing, so that Oracle will
compare execution plans, and only use a changed plan when the histogram
indicates a difference that makes a difference. What are the internal
details of this 11g adaptive cursor sharing?
Answer: Oracle has improved cursor_sharing
several times over the years. Remember, 11g adaptive cursor sharing is only
deployed in rare cases where a skewed column distribution (as noted by the
histogram), indicates that a different execution plan would be faster. For
example, a query with a popular bind variable value would be best served with a
full table scan while an unpopular bind variable value would benefit from an
index access plan.
Also see my bind_aware
Disabling adaptive cursor sharing
Remember, the need for adaptive cursor sharing can be a
rare occurrence in many systems. In many OLTP databases there exists one,
and only one optimal SQL execution plan for a query, regardless of the value of
a bind variable.
For these types of databases, the overhead of adaptive
cursor sharing may be bypassed by disabling the adaptive cursor sharing
According to MOSC Bug 11657468.8, adaptive cursor sharing can be disabled
by setting hidden parameters:
Turn off adaptive cursor
alter system set "cursor_sharing"=exact scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none
alter system set "_optimizer_extended_cursor_sharing"=none scope= both;
alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;
In my experience
at BC, about 80% of shops have uniformly
distributed data. Large tables remain large, and the distribution of values
within a column remain unchanged.
Note: For testing purposes, you may
want to turn-off optimizer bind variable peeking and this can be done in
Effectively using adaptive
On the other hand, we have roughly 20% of databases that experience highly
volatile data loads, where tables are small on one day and huge the next, or
cases where the is a "difference that makes a difference". In these databases,
huge changes in the tables data (usually associated with high DML) changes the
distribution of data values, necessitating a re-analysis of column histograms.
Histograms are critical to the CBO decision to
choose an index vs. a full scan, and also for determining the optimal table join
and adaptive cursor sharing
mechanism (cursor_sharing=similar) had significant issues in Oracle9i.
Robert Freeman notes:
"Oracle8i introduced a feature called cursor sharing
which provided the ability of the optimizer to convert literals within SQL
statements into bind variables in certain situations. As a result, SQL
statements that are alike with the exception of literal values, can share a
This has the impact of reducing the overall time to
parse the SQL statement and, perhaps most importantly, reducing
fragmentation of the shared SQL area of the shared pool. Unfortunately, one
of the end results of cursor sharing and the use of bind variables is that
the optimizer has a difficult time determining the selectivity of the data
in the columns associated with the bind variable. This can lead to
sub-optimal execution plans. You use the parameter cursor_sharing=force
to enable cursor sharing.
Oracle9i now adds modifications to cursor sharing. If
you set cursor_sharing=similar, the optimizer will be able to analyze
the distribution of the data in the columns (using the analyzed statistics
of the table, columns, associated indexes and any histograms that you may
have generated, and determine if the parsed execution plan will be optimal.
If the plan does appear to be optimal then the parsed SQL statement will be
Note: The cursor_sharing=similar option has
been deprecated in Oracle 11g and will be removed in version 12 per MOSC
Dr. Tim Hall
notes that the 11g adaptive cursor sharing uses bind variable peeking in a
"DBAs are always encouraging developers to use bind
variables, but when bind variables are used against columns containing
skewed data they sometimes lead to less than optimum execution plans.
This is because the optimizer peaks at the bind
variable value during the hard parse of the statement, so the value of a
bind variable when the statement is first presented to the server can affect
every execution of the statement, regardless of the bind variable values.
Oracle 11g uses Adaptive Cursor Sharing to solve this problem by allowing
the server to compare the effectiveness of execution plans between
executions with different bind variable values.
If it notices suboptimal plans, it allows certain bind
variable values, or ranges of values, to use alternate execution plans for
the same statement. This functionality requires no additional
Dr. Hall also notes that the v$sql keeps this adaptive information:
"If we look at the
view entry for this query, we can
column is marked as 'Y', so Oracle is
aware this query may require differing execution plans depending on the bind
variable values, but currently the
marked as 'N', so Oracle as not acted on this yet."
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware
WHERE sql_text = 'SELECT MAX(id)
FROM acs_test_tab WHERE record_type = :l_record_type';
9bmm6cmwa8saf 0 Y N
Adaptive cursor sharing queries
Here are some sample queries for 11g adaptive cursor sharing:
See my relates notes and articles on Oracle cursor sharing
and bind variable peeking:
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts.