Sunday, December 10, 2017

Adaptive Cursor Sharing

What Is Adaptive Cursor Sharing

The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. With this feature database does not always use the same plan for each execution or bind variable value. Adaptive cursor sharing comes into play when SQL statement has bind variables in where clause and there is/are histogram(s) on column(s) referenced in bind variable(s).

With bind peeking, the optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor. The optimizer determines the cardinality of any WHERE clause condition as if literals had been used instead of bind variables. When the optimizer peeks at the value of the user-defined bind variable and chooses a plan, this plan may not be good for all values. Adaptive cursor haring comes here into picture to address this issue.

In adaptive cursor sharing, the database monitors data accessed over time for different bind values, ensuring the optimal choice of cursor for a specific bind value. For example, the optimizer might choose one plan for bind value 101 and a different plan for bind value 102. Cursor sharing is "adaptive" so that the optimizer does not always choose the same plan for each execution or bind variable value. Thus, the optimizer automatically detects when different execution of a statement would benefit from different execution plans.

Adaptive cursor sharing is independent of the CURSOR_SHARING initialization parameter. Adaptive cursor sharing is equally applicable to statements that contain user-defined and system-generated bind variables. Adaptive cursor sharing does not apply to statements that contain only literals.

How Adaptive Cursor Sharing Works

Adaptive cursor sharing monitors statements that use bind variables to determine whether a new plan is more efficient. Assume that an application executes the following statement five times, binding different values every time and histogram exists on at least one of the columns in the predicate:
              SELECT * FROM emp WHERE sal = :sal AND emp_id = :emp_id
 The database processes this statement as follows:

1.       The application issues the statement for the first time, which causes a hard parse. During the parse, the database performs the following tasks:
·       Peeks at the bind variables to generate the initial plan.
·       Marks the cursor as bind-sensitive.
·       Stores metadata about the predicate, including the cardinality of the bound values.
·       Creates an execution plan based on the peeked values.

2.       The database executes the cursor, storing the bind values and execution statistics in the cursor.

3.       The application issues the statement a second time, using different bind variable values, causing the database to perform a soft parse, and find the matching cursor in the library cache.

4.       The database executes the cursor.

5.       The database performs the following post-execution tasks:
·       The database compares the execution statistics for the second execution with the first-execution statistics.
·       The database observes the pattern of statistics over all previous executions, and then decides whether to mark the cursor as a bind-aware cursor. In this example, assume that the database decides the cursor is bind-aware.

6.       The application issues the statement a third time, using different bind variable values, which causes a soft parse. Because the cursor is bind-aware, the database does the following:
·       Determines whether the cardinality of the new values falls within the same range as the stored cardinality. In this example, suppose the cardinality is similar: 4 rows instead of 2 rows.
·       Reuses the execution plan in the existing child cursor.

7.       The database executes the cursor.

8.       The application issues the statement a fourth time, using different bind variables, causing a soft parse. Because the cursor is bind-aware, the database does the following:
·       Determines whether the cardinality of the new values falls within the same range as the stored cardinality. In this example, the cardinality is vastly different: 50 rows (in a table with 55 rows) instead of 2 rows.
·       Does not find a matching child cursor.

9.       The database performs a hard parse. As a result, the database does the following:
·       Creates a new child cursor with a second execution plan (in this example, a full table scan)
·       Stores metadata about the predicate, including the cardinality of the bound values, in the cursor

10.   The database executes the new cursor.

11.   The database stores the new bind values and execution statistics in the new child cursor.

12.   The application issues the statement a fifth time, using different bind variables, which causes a soft parse. Because the cursor is bind-aware, the database does the following:
·       Determines whether the cardinality of the new values falls within the same range as the stored cardinality. In this example, the cardinality is 15.
·       Does not find a matching child cursor.

13.   The database performs a hard parse. As a result, the database does the following:
   ·       Creates a new child cursor with a third execution plan (in this example, index access)
·       Determines that this index access execution plan is the same as the index access execution plan used for the first execution of the statement
·       Merges the two child cursors containing index access plans, which involves storing the combined cardinality statistics into one child cursor, and deleting the other one

14.   The database executes the cursor using the index access execution plan.

Adaptive Cursor Sharing Views

V$SQL shows whether a cursor is bind-sensitive or bind-aware.

V$SQL_CS_HISTOGRAM shows the distribution of the execution count across a three-bucket execution history histogram.

V$SQL_CS_SELECTIVITY shows the selectivity ranges stored for every predicate containing a bind variable if the selectivity was used to check cursor sharing. It contains the text of the predicates, and the low and high values for the selectivity ranges.

V$SQL_CS_STATISTICS summarizes the information that the optimizer uses to determine whether to mark a cursor bind-aware. For a sample of executions, the database tracks the rows processed, buffer gets, and CPU time. The PEEKED column shows YES when the bind set was used to build the cursor; otherwise, the value is NO.

Related Links :-

No comments:

Post a Comment