Adaptive cursor Sharing for new 11g features (Adaptive cursor sharing)

Adaptive cursor sharing has multiple execution plans for a single statement that contains bound variables, meaning that the execution plan adapts to the execution plan that best fits that value based on the specific value of the bound variable.

Hide parameter _optimizer_adaptive_cursor_sharing=true turn this feature on or off.

First: Whether the cursor can be used by ACS, first the cursor must be a bound variable-sensitive cursor, which means that the optimal execution plan depends on the specific value of the bound variable. The database monitors binding variable-sensitive cursors to see if different execution plans benefit different values of bound variables.

Cursors are marked as bound variable-sensitive cursors when the following two conditions are met:

1. The optimizer does a selective evaluation by binding variable spy.

2. Histogram information exists on the column of the bound variable.

。。。

For the specific value of each new cursor passed in, the database records the statistics at the time the statement was executed, and when the statement is executed, the database compares the statistics for this execution with the statistics previously executed, and if the difference is large, the database marks the cursor as a bind-aware cursor.

When a cursor is marked as a bind-aware cursor, as long as the value of the bound variable falls within the histogram of the binding variable value and selection rate previously collected, the optimizer reuses an execution plan that already exists and is optimal for that binding variable, if the value of the bound variable does not fall within the histogram above, Is hard-resolved, the benefit of adaptive cursor sharing is that it reduces the number of hard-to-parse times and can find a suitable execution plan for different bound variable values.

Cursor Merging:

When the optimizer creates a new execution plan for a bind-aware cursor, if the execution plan is the same as an already existing cursor, in which case the optimizer merges the cursor to save memory space, the database expands the selectivity range to include the selectivity of the new bound variable value.

Cursor Sharing related performance views:

• V$SQLCheck to see if a cursor is a cursor of bind-sensitive and Bind-aware.

• V$SQL_CS_HISTOGRAM 有绑定变量，选择性，执行次数的直方图。

• V$SQL_CS_SELECTIVITY 包括绑定变量对选择性范围。

• V$SQL_CS_STATISTICSSummarizes includes statistics about whether a cursor is marked as Bind_aware by the optimizer.

sql> var v_own varchar2 (100); sql> exec:v_own= ' sys '; begin:v_own= ' sys '; End;ora-06550:line 1, column 13:pls-00103:encountered the symbol "=" when expecting one of the following:   

　　

Adaptive cursor Sharing for new 11g features (Adaptive cursor sharing)