Cursor Sharing

A cursor is a name or handle to a specific private SQL area. 
The cursor contains session-specific state information such as bind variable values and result sets.
Oracle Database can share cursors, which are pointers to private SQL areas in the shared pool.

=> Private SQL Area
A private SQL area holds information about a parsed SQL statement and other session-specific information for processing.
When a server process executes SQL code, the process uses the private SQL area to store bind variable values, 
query execution state information, and query execution work areas. 
The private SQL areas for each execution of a statement are not shared and may contain different values and data.

You can think of a cursor as a pointer on the client side and as a state on the server side.



=> Private and Shared SQL Areas

A cursor in the private SQL area points to a shared SQL area in the library cache.
Unlike the private SQL area, which contains session state information, 
the shared SQL area contains the parse tree and execution plan for the statement.

For example, an execution of SELECT * FROM employees has a plan and parse tree stored in one shared SQL area. 
An execution of SELECT * FROM departments, which differs both syntactically and semantically,
has a plan and parse tree stored in a separate shared SQL area.

=> Multiple private SQL areas in the same or different sessions can reference a single shared SQL area, 
a phenomenon known as cursor sharing.
For example, an execution of SELECT * FROM employees in one session and an execution of the 
SELECT * FROM employees (accessing the same table) in a different session can use the same parse tree and plan.

=> A shared SQL area that is accessed by multiple statements is known as a shared cursor.



Textual Match Algorithms

Oracle Database automatically determines whether the SQL statement or PL/SQL block being issued is textually 
identical to another statement currently in the library cache,using the following steps:



For a textual match to occur, the text of the SQL statements or PL/SQL blocks must be character-for-character identical, 
including spaces, case, and comments.
For example, the following statements cannot use the same shared SQL area:

SELECT * FROM employees;
SELECT * FROM Employees;
SELECT *  FROM employees;

Parent and Child Cursors

Every parsed SQL statement has a parent cursor and one or more child cursors.
=> The parent cursor stores the text of the SQL statement.
       If the text of two statements is identical, then the statements share the same parent cursor.
       If the text is different, however, then the database creates a separate parent cursor.

=> A child cursor contains the execution plan, bind variables, metadata about objects referenced in the query, 
   optimizer environment, and other information. In contrast to the parent cursor, 
   the child cursor does not store the text of the SQL statement.

Parent Cursors and V$SQLAREA
The V$SQLAREA view contains one row for every parent cursor.

Child Cursors and V$SQL
Every parent cursor has one or more child cursors.
A child cursor contains the execution plan, bind variables, metadata about objects referenced in the query, 
optimizer environment, and other information. 
In contrast to the parent cursor, the child cursor does not store the text of the SQL statement.

If a statement is able to reuse a parent cursor, then the database checks whether the statement can reuse an existing child cursor. 

The database performs several checks, including the following:
•    For example, if two users issue the following SQL statement, and if each user has its own employees table, 
       then the following statement is not identical because the statement references different employees tables for each user:
       SELECT * FROM employees;
•    The database determines whether the optimizer mode is identical.

Cursor Mismatches and V$SQL_SHARED_CURSOR

If a parent cursor has multiple children, then the V$SQL_SHARED_CURSOR view provides information about why the cursor was not shared. 
For several types of incompatibility, the TRANSLATION_MISMATCH column indicates a mismatch with the value Y or N.

About Cursors and Parsing

If an application issues a statement, and if Oracle Database cannot reuse a cursor, 
then it must build a new executable version of the application code. 
This operation is known as a hard parse.
A soft parse is any parse that is not a hard parse, and occurs when the database can reuse existing code. 
Some soft parses are less resource-intensive than others. 
For example, if a parent cursor for the statement already exists, then Oracle Database can perform various optimizations, 
and then store the child cursor in the shared SQL area. 
If a parent cursor does not exist, however, then Oracle Database must also store the parent cursor in the shared SQL area, 
which creates additional memory overhead.
Effectively, a hard parse recompiles a statement before running it. 

A hard parse performs operations such as the following:
•    Checking the syntax of the SQL statement
•    Checking the semantics of the SQL statement
•    Checking the access rights of the user issuing the statement
•    Creating an execution plan
•    Accessing the library cache and data dictionary cache numerous times to check the data dictionary

Latching in database
An especially resource-intensive aspect of hard parsing is accessing the library cache and data dictionary cache numerous 
times to check the data dictionary. 
When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition
does not change during the check. 
Latch contention increases statement execution time and decreases concurrency.

About Literals and Bind Variables

Bind variables are essential to cursor sharing in Oracle database applications.
Literals and Cursors
When constructing SQL statements, some Oracle applications use literals instead of bind variables.
For example, the statement SELECT SUM(salary) FROM hr.employees WHERE employee_id < 101 uses the literal value 101 for the employee ID. 
By default, when similar statements do not use bind variables, Oracle Database cannot take advantage of cursor sharing. 
Thus, Oracle Database sees a statement that is identical except for the value 102, or any other random value, 
as a completely new statement, requiring a hard parse.

Major problems that result from using literal values include the following:
•    If every statement is hard parsed, then cursors are not shared, and so the database must consume more memory to create the cursors.
•    Oracle Database must latch the shared pool and library cache when hard parsing. As the number of hard parses increases, 
     so does the number of processes waiting to latch the shared pool. This situation decreases concurrency and increases contention.

Bind Variables and Cursors
You can develop Oracle applications to use bind variables instead of literals.
A bind variable is a placeholder in a query. 
For example,the statement SELECT SUM(salary) FROM hr.employees WHERE employee_id < :emp_id uses the bind variable:emp_id for the employee ID
Major benefits that result from using bind variables include the following:
•    When identical statements use bind variables, Oracle Database can take advantage of cursor sharing, 
      and share the plan and other information when different values are bound to the same statement.
•    Oracle Database avoids the overhead of latching the shared pool and library cache required for hard parsing.

Bind Variable Peeking
In bind variable peeking (also known as bind peeking), the optimizer looks at the value in a bind variable 
when the database performs a hard parse of a statement.
The optimizer does not look at the bind variable values before every parse. 
Rather, the optimizer peeks only when the optimizer is first invoked, which is during the hard parse.
When a query uses literals, the optimizer can use the literal values to find the best plan. 
However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. 
This task can be extremely difficult. 
By peeking at bind values during the initial hard parse, the optimizer can determine the cardinality of a 
WHERE clause condition as if literals had been used, thereby improving the plan.
Because the optimizer only peeks at the bind value during the hard parse, the plan may not be optimal for all possible bind values. 

CURSOR_SHARING and Bind Variable Substitution

This topic explains how setting CURSOR_SHARING to different values affects how Oracle Database uses bind variables.
CURSOR_SHARING Initialization Parameter
The CURSOR_SHARING initialization parameter controls how the database processes statements with bind variables.

In Oracle Database 12c, the parameter supports the following values:
•    EXACT
This is the default value. 
The database enables only textually identical statements to share a cursor. 
The database does not attempt to replace literal values with system-generated bind variables. 
In this case, the optimizer generates a plan for each statement based on the literal value.
•    FORCE
The database replaces all literals with system-generated bind variables.
For statements that are identical after the bind variables replace the literals, the optimizer uses the same plan.
Note:
The SIMILAR value for CURSOR_SHARING is deprecated.
You can set CURSOR_SHARING at the system or session level, or use the CURSOR_SHARING_EXACT hint at the statement level.

Parsing Behavior When CURSOR_SHARING = FORCE
When SQL statements use literals rather than bind variables, setting the CURSOR_SHARING initialization parameter to FORCE enables 
the database to replace literals with system-generated bind variables. 
Using this technique, the database can sometimes reduce the number of parent cursors in the shared SQL area.
Note:
If a statement uses an ORDER BY clause, then the database does not perform literal replacement in the clause because it is not semantically 
correct to consider the constant column number as a literal. The column number in the ORDER BY clause affects the query plan and execution, 
so the database cannot share two cursors having different column numbers.

When CURSOR_SHARING is set to FORCE, the database performs the following steps during the parse:
1.    Copies all literals in the statement to the PGA, and replaces them with system-generated bind variables
2.    Searches for an identical statement (same SQL hash value) in the shared pool
If an identical statement is not found, then the database performs a hard parse. Otherwise, the database proceeds to the next step.
3.    Performs a soft parse of the statement

Adaptive Cursor Sharing

The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans.

Purpose of Adaptive Cursor Sharing

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. 
If a column in a WHERE clause has skewed data, however, then a histogram may exist on this column.
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.

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. 
Thus, the optimizer automatically detects when different execution of a statement would benefit from different execution plans.

Note:
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.

Bind-Sensitive Cursors

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.
The database has examined the bind value when computing cardinality, and considers the query “sensitive” to plan changes 
based on different bind values. 
The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine 
whether a different plan is beneficial.
The optimizer uses the following criteria to decide whether a cursor is bind-sensitive:
•    The optimizer has peeked at the bind values to generate cardinality estimates.
•    The bind is used in an equality or a range predicate.
For each execution of the query with a new bind value, the database records the execution statistics for the 
new value and compares them to the execution statistics for the previous value. 
If execution statistics vary greatly, then the database marks the cursor bind-aware.

The database marked this cursor bind-sensitive because the optimizer used the histogram on the department_id column to compute
the selectivity of the predicate WHERE department_id = :dept_id. 
Because the presence of the histogram indicates that the column is skewed, different values of the bind variable may require different plans

Bind-Aware Cursors

A bind-aware cursor is a bind-sensitive cursor that is 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 cardinality estimate. 
Thus, “bind-aware” means essentially “best plan for the current bind value.”
When a statement with a bind-sensitive cursor executes, the optimizer uses an internal algorithm to determine whether 
to mark the cursor bind-aware. 
The decision depends on whether the cursor produces significantly different data access patterns 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). 
     The 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 adds new bind values to 
the “signature” of the SQL statement (which includes the optimizer environment, NLS settings, and so on), and categorizes the 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 need to create a new plan for each new value.

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 library cache. 
The database increases the selectivity range for the cursor to include the selectivity of the new bind value.
When a query uses a new bind variable, the optimizer tries to find a cursor that it thinks is a good fit based on similarity 
in the selectivity of the bind value. 
If the database cannot find such a cursor, then it creates a new one. 
If the plan for the new cursor is the same as one of the existing cursors, then the database merges the two cursors to save 
space in the library cache. 
The merge results in the database marking one cursor as not sharable. 
If the library cache is under space pressure, then the database ages out the non-sharable cursor first.

Adaptive Cursor Sharing Views

Specifically, use the following 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.

Life Cycle of Shared Cursors

The database allocates a new shared SQL area when the optimizer parses a new SQL statement that is not DDL. 
The database can remove a shared SQL area from the shared pool even if this area corresponds to an open cursor 
that has been unused for a long time. 
If the open cursor is later used to run its statement, then the database reparses the statement and allocates a new shared SQL area. 
The database does not remove cursors whose statements are executing, or whose rows have not been completely fetched.
Shared SQL areas can become invalid because of changes to dependent schema objects or to optimizer statistics. 

Oracle Database uses two techniques to manage the cursor life cycle: invalidation and rolling invalidation.

=> Cursor Marked Invalid

When a shared SQL area is marked invalid, the database can remove it from the shared pool, 
along with valid cursors that have been unused for some time.
In some situations, the database must execute a statement that is associated with an invalid shared SQL area in the shared pool. 
In this case, the database performs a hard parse of the statement before execution.

The database immediately marks dependent shared SQL areas invalid when the following conditions are met:
•  DBMS_STATS gathers statistics for a table, table cluster, or index when the NO_INVALIDATE parameter is FALSE.
•  A SQL statement references a schema object, which is later modified by a DDL statement that uses immediate cursor invalidation (default).

You can manually specify immediate invalidation on statements such as ALTER TABLE ... IMMEDIATE VALIDATION and 
ALTER INDEX ... IMMEDIATE VALIDATION, or set the CURSOR_INVALIDATION initialization parameter to IMMEDIATE at the session or system level.

Note:
A DDL statement using the DEFERRED VALIDATION clause overrides the IMMEDIATE setting of the CURSOR_INVALIDATION initialization parameter.
When the preceding conditions are met, the database reparses the affected statements at next execution.
When the database invalidates a cursor, the V$SQL.INVALIDATIONS value increases (for example, from 0 to 1), 
and V$SQL.OBJECT_STATUS shows INVALID_UNAUTH.

=>  Cursors Marked Rolling Invalid
When cursors are marked rolling invalid (V$SQL.IS_ROLLING_INVALID is Y), the database gradually performs hard parses over an extended time.
Note:
When V$SQL.IS_ROLLING_REFRESH_INVALID is Y, the underlying object has changed, but recompilation of the cursor is not required. 
The database updates metadata in the cursor.

Purpose of Rolling Invalidation

Because a sharp increase in hard parses can significantly degrade performance, 
rolling invalidation—also called deferred invalidation—is useful for workloads that simultaneously invalidate many cursors. 
The database assigns each invalid cursor a randomly generated time period. 
SQL areas invalidated at the same time typically have different time periods.
A hard parse occurs only if a query accessing the cursor executes after the time period has expired.
In this way, the database diffuses the overhead of hard parsing over time.

When Rolling Invalidation Occurs
If the DEFERRED INVALIDATION attribute applies to an object, either as a result of DDL or an initialization parameter setting, 
then statements that access the object may be subject to deferred invalidation. 
The database marks shared SQL areas as rolling invalid in either of the following circumstances:
•    DBMS_STATS gathers statistics when the NO_INVALIDATE parameter is set to DBMS_STATS.AUTO_INVALIDATE. This is the default setting.
•    when following statements is issued with DEFERRED INVALIDATION in circumstances that do not prevent the use of deferred invalidation:
ALTER TABLE on partitioned tables
ALTER TABLE ... PARALLEL
ALTER INDEX ... UNUSABLE
ALTER INDEX ... REBUILD
CREATE INDEX
DROP INDEX
TRUNCATE TABLE on partitioned tables
A subset of DDL statements require immediate cursor invalidation for DML (INSERT, UPDATE, DELETE, or MERGE) but not SELECT statements.
Many factors relating to the specific DDL statements and affected cursors determine whether Oracle Database uses deferred invalidation.