Cursor Sharing in oracle

Cursor Sharing in oracle

 

Before oracle8.1.6, When application sends a SQL, oracle first check the shared pool to see if there is an exact statement in the shared pool. If the exact statement is not found, then a hard parse is necessary to generate the executable form of the statement.           Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared

CURSOR_SHARING=EXACT

Oracle does not use bind variable and every unique SQL statement, execution plan will be generated. It forced to hardparse every uniqe SQL statement and it leads to consume lot of CPU cycles. Oracle can not hardparse hundreds of SQL statement concurrently and it end up waiting for shared pool. To overcome this problem, oracle introduced FORCE paramter to use bind variable.

In oracle8.1.6, Oracle introduced new parameter FORCE. This is accomplished by transforming the statement into a new one by replacing the literals with system generated bind variables. The problem with bind variables is that the optimizer cannot predict precise selectivity.

Using cursor_sharing = force will parse the FIRST sql statement loaded into memory and will use this execution plan for the subsequent similar sql statements. This might be a problem if the FIRST statement's literals have good selectivity but those of the subsequent statements don't. In this case the statements with poor selectivity will also use the index whereas a full table scan would perform better. However, if the firstly run statement doesn't have good selectivity and a full table scan is used, the subequent similar statements will also use FTS.


Oracle generate only one plan and use for all the SQL code. This would turn the hard parse into soft parse. It would require fewer resource. FORCE option is good when the query table is not skewed and all the queries require same execution plan. But in real world, this is not a good option.

To overcome this problem, In Oracle9i we can use CURSOR_SHARING=SIMILAR setting which makes the right plan based on the statistics.

Optimizer replace the literal with bind variable and check if it requires different plan for the query based on the current statistics. If so then, optimizer mark the bind variable as unsafe and store the value in the signature of the query. Next time, user submit the query with the same bind variable value, then oracle resuse the plan. Optimizer would use the same execution plan based on the statistics if user submit the same query with different literal .

SIMILAR option resolve the issues which we had in EXACT. Exact does not use bind variable. But here it is using bind variable. In FORCE option, optimizer use the same execution plan for same query with different literal. But SIMILAR option, it use the different plan for the same query with different literal values based on the current statistics.

 

 

Adaptive Cursor Sharing

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.