New Feature of oracle11g-Adaptive Cursor Sharing (ACS) and oracle11gadaptive

New Feature of oracle11g-Adaptive Cursor Sharing (ACS) and oracle11gadaptive

1. ACS Overview

Oracle Database 11g provides the Adaptive Cursor Sharing (ACS) function to overcome the possibility of Sharing a Cursor that should not be shared in the past. ACS uses two new metrics: 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 of the dynamic view V $ SQL to allow DBA to determine whether the optimizer has determined an SQL statement as an ACS candidate, you can also observe the business rules used by the optimizer to classify SQL statements for shared execution plans:

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 participant child cursor has been executed.
V $ SQL _cs_selecti.pdf	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 selectistmcube.
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.

Table-1 ACS View

2.2. bind Sensitivity: When an SQL statement with a bound variable is parsed for the first time, after the optimizer snoop on the value of the bound variable and determines the relevant choice rate of the statement predicate, mark the cursor as bind-sensitive (bind sensitive ). These sensitive measurements are also retained for future comparison when the same statements with the same variables and different values are re-executed, check whether an existing execution plan can be used by a statement that is bound with a new variable value.

2.3. Bind Awareness: Once the cursor of an SQL statement is labeled as bind-sensitive, the optimizer can also determine that the cursor is bind-aware. The optimizer completes this step by checking the value of the variable bound to the same SQL statement subsequently executed and the value of the variable bound to all matching plans that have been captured. If the optimizer determines that the statement can use an existing plan, it only needs to update the cursor execution bar chart to reflect the statement execution. In addition, if the bound variable values are different enough, the Optimizer may decide to create a new sub-cursor and execution plan. Once this happens, Oracle11g also stores the selection rate of the sub-cursor in the ACS metadata. During subsequent execution of the cursor, the optimizer compares the statistical selectivity data and the statistics of the cursor's recent execution. If it is observed that most executions use the same selectivity range, the cursor is marked as bind-aware.

What happens when a query is executed with a set of different bind Variable values that exceed the selection rate range of an existing bind-aware cursor binding variable? During the hard parsing of the statement, the Optimizer may only decide to extend the selection rate range of the cursor to include new variable values, this is done by generating a new game that combines two sets of variable values. It can be seen that when necessary, a new cursor is added. In Oracle11g, the ACS feature is enabled 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 whether ACS and SPM affect each other. The following is a brief summary:

If the initialization parameter OPTIMIZER_CAPTURE_ SQL _PLAN_BASELINES has been set to TRUE to enable automatic plan capture, an SQL statement with Bound variables will be labeled as a corresponding usable and acceptable execution plan.

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

Unfortunately, this means that a good plan may be ignored, even the selective range of its cursor may lead to a better performance. A good way to bypass this problem is to keep automatic plan capture as the default setting False, and then capture all sub-game tags in the library cahce to SMB. This will force all plans of the ACS cursor to be labeled as SQL PLAN BASELINES.

3. Restrictions

As of Oracle11gR2, the ACS feature has the following restrictions (when the following scenario appears, ACS will not mark the cursor as bind sensitive ):

The extended cursor sharing is disabled;

No variables are bound to the query;

Some parameters are set (for example, bind variables to flase );

Ø when the statement is using parallel query;

The hints statement is used;

Ø Outlines are in use;

The query is a recursive query;

The number of Bound variables exceeds 14;

4. Disable and enable

Ø ACS is enabled by default. To disable 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;

To enable ACS, we need to modify the following four parameters:

_ Optim_peek_user_binds = true (bind variables must be enabled) _ optimizer_adaptive_cursor_sharing = TRUE (ACS is enabled by default for the following three parameters) _ optimizer_extended_cursor_sharing = UDO

_ Optimizer_extended_cursor_sharing_rel = SIMPLE

5. Note:

Ø hint: there is a new hint in Oracle11g. When this hint is used, even if the ACS feature is disabled, the ACS feature will still take effect at the statement level. The syntax of this hint is: /* + BIND_AWARE */;

Ø about Outlines: In the scenario where Outlines exists, ACS will become invalid no matter whether it is enabled at the system level or at the statement level through hint;

6. Conclusion:

The ACS feature, a new feature of Oracle Database 11g, provides a simple way to efficiently share SQL statement execution plans with variable binding. Because ACS generates a new execution plan only when the variable value selection rate is required, the number of shared cursors is minimized.

 

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.