Friday, November 3, 2017

What is the difference between a bind-sensitive and bind-aware cursor?

The terms "bind-sensitive" and "bind-aware" are used when explaining Oracles Adaptive Cursor Sharing (ACS) feature.

Adaptive cursors sharing makes it possible for a single statement to use different execution plans, depending on the values of its bind variables.

Oracle explains the difference between bind-sensitive and bind-aware in their documentation like this:


"A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable."

and

"A bind-aware cursor is a bind-sensitive cursor that is eligible to use different plans for different bind values."


In other words, when a bind-sensitive cursor have been executed, the database saves the execution statistics for the new values, and compares them with the values used previously.

If (and only if) there is a significant change, Oracle marks the cursor as bind-aware.
During future executions of the cursor, the CBO will generate a plan based on the bind values and their cardinality estimates.

No comments:

Post a Comment