Header

Saturday, December 16, 2017

Bind-Aware Cursors

Bind-Aware CursorsA bind-aware cursor is a bind-sensitive cursor that is now eligible to use different plans for different bind values. That means when a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its cardinality estimate. When a statement with a bind-sensitive cursor executes, the optimizer uses an internal algorithm to determine whether to mark the cursor bind-aware or keep it as bind-sensitive. This decision depends on whether the cursor produces significantly different data access patterns - plans for different bind values, resulting in a performance cost that differs from expectations.

If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:• Generates a new plan based on the bind value.• Marks the original cursor generated for the statement as not sharable (V$SQL.IS_SHAREABLE is N). That means original cursor is no longer usable and is eligible to age out of the library cache.

When the same query repeatedly executes with different bind values, the database examines the bind values and considers whether the current bind value results in a significantly different data volume, or whether an existing plan is sufficient. The database does not create a new plan for each new value.

During the initial hard parses, the optimizer is essentially mapping out the relationship between bind values and the appropriate execution plan. After this initial period, the database eventually reaches a steady state. Executing with a new bind value results in picking the best child cursor in the cache, without requiring a hard parse. Thus, the number of parses does not scale with the number of different bind values.

This example continues the example in "Bind-Sensitive Cursor". The following code again issues query on emp table with the bind variable set to 60:

During the first two executions, the database was monitoring the behavior of the queries, and determined that the different bind values caused the queries to differ significantly in cardinality. Based on this difference, the database adapts its behavior so that the same plan is not always shared for this query. Thus, the optimizer generates a new plan based on the current bind value, which is 60 :

The preceding output shows that the database created an additional child cursor (CHILD# of 1). Cursor 0 is now marked as not shareable. Cursor 1 shows a number of buffers gets lower than cursor 0, and is marked both bind-sensitive and bind-aware. A bind-aware cursor may use different plans for different bind values, depending on the selectivity of the predicates containing the bind variable.Lets execute below query again with dept_id 10 :-

The database discarded the original cursor (CHILD# of 0) when the cursor switched to bind-aware mode. This is a one-time overhead. The database marked cursor 0 as not shareable (SHAREABLE is N), which means that this cursor is unusable and will be among the first to age out of the cursor cache.