ADAPTIVE CURSOR SHARING CONCEPTS FOR Oracle database 10G DBA's
The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the
cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.
BIND SENSITIVE CURSORS
A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.
The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:
1) The optimizer has peeked at the bind values to generate selectivity estimates.
2) A histogram exists on the column containing the bind value.
BIND AWARE CURSORS
A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate. When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values.
If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:
1) Generates a new plan based on the new bind value.
2) Marks the original cursor generated for the statement as not shareable (V$SQL.IS_SHAREABLE is N). This cursor is no longer usable and will be among the first to be aged out of the shared SQL area.
CURSOR MERGING
If the optimizer creates a plan for a bind-aware cursor, and if this plan is the same as an existing cursor, then the optimizer can perform cursor merging. In this case, the database merges cursors to save space in the shared SQL area. The database increases the selectivity range for the cursor to include the selectivity of the new bind. Suppose you execute a query with a bind value that does not fall within the selectivity ranges of the existing cursors. The database performs a hard parse and generates a new plan and new cursor. If this new plan is the same plan used by an existing cursor, then the database merges these two cursors and deletes one of the old cursors.
The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the
cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.
BIND SENSITIVE CURSORS
A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.
The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:
1) The optimizer has peeked at the bind values to generate selectivity estimates.
2) A histogram exists on the column containing the bind value.
BIND AWARE CURSORS
A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate. When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. The decision depends on whether the cursor produces significantly different data access patterns for different bind values.
If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:
1) Generates a new plan based on the new bind value.
2) Marks the original cursor generated for the statement as not shareable (V$SQL.IS_SHAREABLE is N). This cursor is no longer usable and will be among the first to be aged out of the shared SQL area.
CURSOR MERGING
If the optimizer creates a plan for a bind-aware cursor, and if this plan is the same as an existing cursor, then the optimizer can perform cursor merging. In this case, the database merges cursors to save space in the shared SQL area. The database increases the selectivity range for the cursor to include the selectivity of the new bind. Suppose you execute a query with a bind value that does not fall within the selectivity ranges of the existing cursors. The database performs a hard parse and generates a new plan and new cursor. If this new plan is the same plan used by an existing cursor, then the database merges these two cursors and deletes one of the old cursors.
No comments:
Post a Comment