oracle11g SQL optimization (SQL TUNING) new feature adaptive Cursor Sharing (ACS)

1. Introduction to ACS

Oracle Database 11g provides the adaptive cursor Sharing (ACS) feature to overcome the possibility that cursors that were not previously shared should be shared. ACS uses two new indicators: sensitivity and bindawareness to implement this feature.

2. ACS mechanism

2.1. Adaptive Cursor sharing metadata:oracle 11g also provides three new views and two new columns for dynamic view V$sql to allow DBAs to determine if the optimizer has identified an SQL statement as a candidate for ACS, and that You can also use them to observe the business rules used by the optimizer to categorize the execution plan that the SQL statement uses to share:

View	Describe
V$sql	New columns is 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 variabl Es so, it can calculate each predicate ' s selectivity. Likewise, Is_bind_aware indicates if the optimizer have also decided that the statement's cursor is bind-aware after Additi Onal 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 is bind -sensitive, including how many times a particular child the 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 is also termed the cursor ' s selectivity cube.
V$sql_cs_statistics	Lists the statistics of whether and/or how often a Adaptive Cursor has been shared. The peeked column would display a value of Y (es) If the bind set had been used to build the Adaptive Cursor.

Table -1 ACS View

2.2. Bind sensitivity: When an SQL statement with a bound variable is first parsed, it is marked as bind-sensitive (binding-sensitive) after the optimizer has spied on the value of the bound variable and determined the relative selection rate of the statement predicate. These sensitive measurements are also retained during the subsequent execution of the same statement with the same variable, with different values, to see if an existing execution plan can be exploited by a statement that has the value of the newly bound variable.

2.3. Bind awareness: Once a cursor for an SQL statement is marked as bind-sensitive, the optimizer can also determine that the cursor is bind-aware. The optimizer completes this step by examining the values of the same SQL statement bound variables that are subsequently executed, and the values of all bound variables that match the plan that have been captured. If the optimizer determines that the statement can take advantage of the existing plan, it is only necessary to update the cursor execution histogram to reflect the execution of the statement. In addition, if the binding variable values are sufficiently different, the optimizer may decide to create a new child cursor and execution plan. Once these occur, oracle11g also stores the associated selection rate of the cursor in the ACS metadata. During the subsequent execution of the cursor, the optimizer compares the existing statistical selectivity data and the cursor's most recently executed statistics, and if most executions are observed to use an approximate same selectivity range, then the cursor will be marked as Bind-aware.

What happens when a query is executed with a set of different binding variable values that exceed the selection range bounds of a cursor-bound variable of an existing Bind-aware? During the hard parsing of the statement, the optimizer may simply decide to extend the selection range of that cursor to include the new variable value, by generating a new tour that combines the values of two sets of bound variables. It can be seen that only new cursors are added when necessary. In oracle11g, the ACS feature is turned on by default and is completely independent of the cursor_sharing parameter.

2.4. Impact on SQLPlan Management (SPM): Finally, if you understand the SPM features of the ancient oracle11g, you may want to know if the ACS and SPM will affect each other, and here are some short summaries:

If the initialization parameter Optimizer_capture_sql_plan_baselines has been set to true to turn on automatic scheduled capture, then an SQL statement with a bound variable will be labeled as the corresponding, acceptable and executable plan.

If the second execution plan of the same statement is created-it is not new to the ACS feature-then the plan is only added to the statement's plan history, but can be used immediately, because the SPM requires that the new execution plan be validated first as a better plan.

Unfortunately, this means that a good plan may be overlooked, even if the selectivity of its cursor may lead to a better performance. A good way to get around this problem is to keep the automatic plan capture as the default setting false, and then capture all the child cursors in the library CAHCE to SMB. This will force all plans for the cursors produced by ACS to be marked as SQL Plan BASELINES.

3. Restrictions

ORACLE11GR2, the following limitations exist for the ACS attribute (which causes ACS not to mark the cursor as bind sensitive when the following scenario occurs):

Ø extended cursor sharing is closed;

Ø No variables are bound in the query;

Ø some parameters are set (for example: binding variable snooping is placed as flase);

Ø when the statement is using parallel query;

Ø The statement uses the hints;

Øoutlines is being used;

Ø query for recursive query;

Ø the number of bound variables exceeds 14;

4. Close and Open

Øacs is turned on by default, in order to turn off ACS, we need to modify the following three parameters:

Alter system set "_optimizer_extended_cursor_sharing_rel" =none;

Altersystem set "_optimizer_extended_cursor_sharing" =none;

Altersystem set "_optimizer_adaptive_cursor_sharing" =FALSE;

Ø in order to turn on ACS, we need to modify the following four parameters:

_optim_peek_user_binds=true (be sure to turn on bind variable peering) _optimizer_adaptive_cursor_sharing=true (the following three parameters turn on ACS by default) _optimizer_ Extended_cursor_sharing=udo

_optimizer_extended_cursor_sharing_rel=simple

5. Note:

Ø There is a new hint in the relevant hint:oracle11g, when using this hint, even if the ACS feature is turned off, the ACS feature will still be in effect at the statement level, the hint syntax is:/*+ bind_aware*/;

Ø about outlines: In the scene of outlines existence, the ACS will fail whether the system level is enabled or the statement level is enabled through hint;

6. Conclusion:

New features of Oracle Database 11g The ACS feature provides an easy way to efficiently share SQL statement execution plans with bound variables. Because ACS produces a new execution plan only if the binding variable value selection rate is necessary, the number of shared cursors is kept to a minimum.

oracle11g SQL optimization (SQL TUNING) new feature adaptive Cursor Sharing (ACS)