Oracle Database 11g: Adaptive Cursor Sharing

Synopsis.
At the heart of any SQL statement optimizing engine is the capability to
determine the least expensive and most effective access path to the statement’s
target data. Bind variables help limit the number of SQL statement cursors that
need to be stored in the Library Cache to return similar result sets. This
article discusses a new Oracle Database 11gR1 feature – Adaptive Cursor
Sharing – and illustrates how to use it in practical situations to limit hard
parsing of SQL statements with bind variables while increasing the relative
execution efficiency of those statements.

As I’ve
enjoyed telling several hundred students over the past four years as an
instructor, I have a sure-fire way to prevent poor database performance: Simply
be sure that an application developer never runs any SQL statements against their
databases. While this usually elicits at least a few guffaws of laughter and
some wistful glances, I usually request an immediate return to reality so we
can discuss the best ways to ensure their developers build effective SQL
statements. And I assure them that no matter what standards they may put in
place, sooner or later a poorly constructed SQL statement will cause their
OLTP, DSS, or hybrid database to perform poorly.

“Peeking” at Bind Variables: It’s Not Cheating!

Fortunately,
the Oracle RDBMS has provided us with some excellent tools to identify SQL
statements that are candidates for improved performance as well as the ability
to suggest better methods for reducing the impact of poor execution. At the
heart of this approach is the way in which we can influence the cost-based
optimizer to parse a SQL statement only when a new execution plan is absolutely
necessary. Of course, when a statement is executed for the first time, it must
be hard-parsed so that the optimizer can determine the optimal access path to
the required data. Because parsing is a relatively expensive operation, however,
DBAs are also interested in limiting the number of unique cursors stored in the
Library Cache, especially for online transaction processing (OLTP) environments
where the same statement may be executed hundreds or thousands of times an hour
to provide a similar result set to a calling user session.

The
chief method to accomplish effective sharing of cursors is by setting the CURSOR_SHARING
initialization parameter appropriately depending on the expected application
workload. Setting this parameter to a value of SIMILAR tells the optimizer to allow
cursors to be shared when SQL statements are virtually identical except for
literals in their predicates, and the execution plan provides equal or superior
performance. Likewise, setting CURSOR_SHARING to a value of FORCE
tells the optimizer to share the cursor regardless
of the existence of a potentially better execution plan. When SQL statements
contain bind variables, there are excellent opportunities to provide this
possible performance benefit. However, how can the optimizer initially construct
an effective execution plan for a SQL statement without knowing what typical
values are going to be used to populate the resulting cursor?

Oracle
Database 9i made the first attempt to overcome the potential issues inherent in
erroneous plan selection with the introduction of bind variable peeking. As its name implies, whenever a SQL
statement that contains bind variable is first executed, Oracle sneaks a quick
look at the actual values of the bind variable arguments and uses those values
to construct an optimal plan for just that execution. The advantage of this
approach is obvious: Instead of a possibly incorrect guess that might result in
a (horrendously!) non-optimal execution plan, the cost-based optimizer uses
real values to construct the plan.

Unfortunately,
however, the drawbacks of this approach for non-OLTP applications are also
self-evident. For example, what if the next run of a DSS query specifies a set
of bind variable values that need a completely different execution plan for more
efficient execution? In fact, this is not uncommon in a Data Warehousing
environment, as it’s actually quite desirable to have several potential execution
plans for a DSS query because one set of bind variable values may return a
result set that contains only a few hundred rows, while another set of values
may return several million rows. In this case, Oracle recommends leaving the CURSOR_SHARING
parameter at its default value of EXACT to force the generation of
a new and (potentially) more efficient execution plan.

Adaptive Cursor Sharing: More Flexible Binding

Oracle
Database 11g offers Adaptive Cursor Sharing
(ACS) to overcome the possibility that a cursor may be shared when it really shouldn’t
be shared. ACS uses two new metrics, bind
sensitivity
and bind awareness,
to implement this feature set.

Bind Sensitivity. Whenever a SQL statement
with bind variables is parsed for the first time, the optimizer will mark it as
bind-sensitive after it has peeked
at the values of the bind variables to determine the relative selectivity of
the predicates for the statement. But that’s where the similarity to bind
peeking ends, because it also retains these sensitivity measurements for
comparison during later executions of the same statement with different values for the same bind variables
to determine if an existing execution plan can provide service for that new
combination of values.

To
illustrate how these bind sensitivity features work, I’ve constructed a simple
query shown in Listing
1
against the SH.SALES table in the Oracle 11g SH
sample schema – a good choice, since it’s the largest table in the schema, and
is deeply partitioned across time ranges. That query uses four bind variables
to define a beginning and ending range for SH.SALES.TIME_ID and SH.SALES.CUST_ID.

Adaptive Cursor Sharing Metadata. Oracle
11g also provides three new views and two new columns in dynamic view V$SQL
to allow an Oracle DBA to determine if the optimizer has decided that a SQL statement
is a candidate for Adaptive Cursor Sharing, as well as peek into the business
rules the optimizer has used to classify a SQL statement’s execution plan for
sharing:

Table 1. Adaptive
Cursor Sharing Views

View

Description

V$SQL

Two
new columns are added:

  • IS_BIND_SENSITIVE indicates if a SQL statement is bind-sensitive. If this column contains
    a value of (Y)es, it means that the optimizer peeked at the values of the
    statement’s bind variables so that it can calculate each predicate’s
    selectivity.
  • Likewise,
    IS_BIND_AWARE indicates if the optimizer has also
    decided that the statement’s cursor is bind-aware
    after additional execution of the statement.

V$SQL_CS_HISTOGRAM

Distributes
the frequency (within a three-bucket histogram) at which Oracle 11g used to
decide if a SQL statement was bind-sensitive,
including how many times a particular child cursor has been executed.

V$SQL_CS_SELECTIVITY

Contains
information about the relative selectivity
of a SQL statement’s predicates,
including the predicates themselves, and their high and low value ranges.
These values are also termed the cursor’s selectivity
cube
.

V$SQL_CS_STATISTICS

Lists
the statistics of whether and/or how often an Adaptive Cursor has been
shared. The PEEKED
column will display a value of Y(es) if the bind set had been used to build the Adaptive
Cursor.

I’ve
also constructed sample SQL*Plus queries with formatted output against these
views as shown in Listing
2
; I’ll be using them in the rest of this article to illustrate
exactly how Adaptive Cursor Sharing works. In addition, in Listing
3
, I’ve shown the impact of the first execution of the statement
on this metadata. After this statement is hard-parsed for the first time, its
cursor is automatically marked as bind-sensitive,
but not yet bind-aware. Also, the
query’s bind variable values during its first execution have placed it in the
middle of the three histogram buckets for Adaptive Cursor Sharing.

Bind Awareness. Once a SQL statement’s
cursor has been marked as bind-sensitive,
the optimizer may also decide that the cursor is bind-aware. The optimizer accomplishes this by checking the values
supplied for the bind variables against those already captured for all matching
plans during subsequent executions of the same query. If the optimizer decides
it can utilize an existing execution plan, then just the cursor execution
histogram is updated to reflect the statement’s execution; on the other hand, if
the bind variable values are sufficiently different, the optimizer may decide
to create a completely new child cursor and execution plan. Once this happens,
Oracle 11g also stores the relative selectivity
of the child cursor in the Adaptive Cursor Sharing metadata.

I
find it helps to think of these selectivity ratings as the central point of an
“electron cloud” or sphere of influence. (The Oracle documentation uses the
term “selectivity cube” but that’s a little hard-edged for my brain.) During
subsequent execution(s) of a cursor, the optimizer compares the existing
selectivity statistics against the statistics for the cursor’s most recent
execution, and if it observes that most of the executions use approximately the
same selectivity sphere, the cursor will be marked as bind-aware.

I’ve
illustrated these concepts with two additional executions of the same query,
but with totally different bind variables (see Listing
4
). The resulting changes to the Adaptive Cursor Sharing metadata
for the query’s cursors is immediately evident as shown in Listing
5
. Note that Oracle 11g has created a new child cursor for the
statement with a plan hash value of 2855975716 and marked it as both
bind-sensitive and bind-aware; the selective metrics for these cursors is also
updated in the metadata as well.

And what
happens when the query is executed with a set of different bind variable values
that are just outside the bounds of an existing bind-aware cursor’s sphere of
influence? During the hard parse of the statement, the optimizer may simply decide
to expand the selectivity sphere of that cursor to now include the new bind
values. It does this by generating a new child cursor that incorporates both
sets of bind variable values, and then deleting the older, less-inclusive child
cursor. This obviously results in only limited additional child cursors being
generated when they’re absolutely necessary.

So
how do I activate this new feature? The good news is that it’s already
activated in Oracle 11g, and even better, it’s completely independent of
whatever the setting is for the CURSOR_SHARING initialization
parameter. This dramatically increases the chances that a statement that uses bind
variables in a hybrid OLTP / DSS environment – not at all uncommon today in
Real Application Cluster databases! – will function best depending on how it’s
intended to be used.

Impact On SQL Plan Management (SPM).
Finally, if you’ve read my SQL
Plan Management
on Oracle 11g’s new SQL
Plan Management
(SPM) feature set, you’re probably wondering if Adaptive
Cursor Sharing interferes with SPM’s ability to capture and retain SQL
execution plans in the SQL Management Base
(SMB). Here’s a brief summary of their interaction:

  • If initialization parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
    has been set to TRUE
    to activate automatic plan capture, then a SQL statement with bind variables
    will be marked as the corresponding enabled and accepted execution plan.
  • If a second execution plan for the same statement
    is built – not uncommon with Adaptive Cursor Sharing! – then the plan is simply
    added to the statement’s plan history, but it’s not immediately used because
    SPM first requires that the new plan is first verified as a better statement.

Unfortunately,
this means that a “good” plan might be ignored even when its child cursor’s
selectivity sphere would probably return better performance. An excellent
workaround to this problem is to leave automatic plan capture at its default
setting of FALSE and then simply capture all child cursors in the library
cache’s cursor cache into the SMB. This will force all plans for the child
cursors generated by Adaptive Cursor Sharing to be marked as SQL Plan
Baselines.

Conclusion

Oracle
Database 11g’s new Adaptive Cursor Sharing capabilities provide a much simpler
method for SQL statements that contain bind variables to share execution plans
effectively, but only when it makes sense based on the values supplied for the
bind variables. And since Adaptive Cursor Sharing will also generate a new
execution plan when the resulting selectivity of the bind variable values
warrant it, the relative number of shared cursors is kept to a minimum. The
bottom line: Oracle DBAs have to spend a lot less time demystifying
unexpectedly poor performance of a (hitherto) well-tuned SQL statement.

References and Additional Reading

While
I’m hopeful that I’ve given you a thorough grounding in the technical aspects
of the features I’ve discussed in this article, I’m also sure that there may be
better documentation available since it’s been published. I therefore strongly
suggest that you take a close look at the corresponding Oracle documentation on
these features to obtain crystal-clear understanding before attempting to
implement them in a production environment. Please note that I’ve drawn upon
the following Oracle Database 11gR1 documentation for the deeper technical
details of this article:

B28320-01 Oracle Database 11gR1 Reference Guide

B28274-01 Oracle Database 11gR1 Performance Tuning Guide

B28279-02 Oracle Database 11gR1 New Features Guide

»


See All Articles by Columnist
Jim Czuprynski

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles