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.
If the optimizer decides it can utilize an existing execution plan, then just the cursor execution histogram is updated to reflect the statements 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 of the child cursor in the Adaptive Cursor Sharing metadata.
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.
Oracle Database 11g offers after it has peeked at the values of the bind variables to determine the relative selectivity of the predicates for the statement. 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 statements execution plan for sharing: Ive also constructed sample SQL*Plus queries with formatted output against these views as shown in Listing 2; Ill be using them in the rest of this article to illustrate exactly how Adaptive Cursor Sharing works.
At the heart of any SQL statement optimizing engine is the capability to determine the least expensive and most effective access path to the statements 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.
Ive 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 querys cursors is immediately evident as shown in Listing 5.
This obviously results in only limited additional child cursors being generated when theyre absolutely necessary. The good news is that its already activated in Oracle 11g, and even better, its 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!
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.