Oracle Database 11g: Adaptive Cursor Sharing

September 30, 2008

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:

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