Cursor Sharing in Oracle

Private SQL area holds information about a parsed SQL statement and other session specific information for processing.

When a server process executes SQL or PL/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.

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.

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.

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

1. Automatically Convert Literals to Bind Variables in SQL Statements
2. Oracle 9i introduced the CURSOR_SHARING parameter, which determines how the database handles statements containing literal values.

The CURSOR_SHARING initialization parameter allows existing applications to improve SQL reuse. The allowable values are listed below.

• EXACT - (Default) Only statements with an exact text match will share the same cursor.
	
• FORCE - The database substitutes system generated bind variables for all literals, thereby increasing the chances of a text match.
	
• SIMILAR - Deprecated in Oracle 11.2. The database substitutes system generated bind variables for all literals, thereby increasing the chances of a text match. Oracle will force similar statements to share the SQL area without deteriorating execution plans.

In the following example we run two SQL statements containing literal values and display the contents of the V$SQLAREA view.

- Run two statements containing literals.

select * from dual where dummy = 'LITERAL1';
select * from dual where dummy = 'LITERAL2';

-- Check the contents of the shared pool.

column sql_text format a60
select sql_text,
       executions
from   v$sqlarea
where  instr(sql_text, 'select * from dual where dummy') > 0
and    instr(sql_text, 'sql_text') = 0
order by sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select * from dual where dummy = 'LITERAL1'                           1
select * from dual where dummy = 'LITERAL2'                           1
SQL>

We can see both statements are stored in the shared pool, each with a single execution.
Next we enable enable cursor sharing for the session, and repeat a similar test.

-- set cursor sharing.

alter session set cursor_sharing=force;

-- Run two statements containing literals.

select * from dual where dummy = 'LITERAL3';
select * from dual where dummy = 'LITERAL4';

-- Check the contents of the shared pool.

column sql_text format a60
select sql_text,
       executions
from   v$sqlarea
where  instr(sql_text, 'select * from dual where dummy') > 0
and    instr(sql_text, 'sql_text') = 0
order by sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
select * from dual where dummy = 'LITERAL1'                           1
select * from dual where dummy = 'LITERAL2'                           1
select * from dual where dummy= :"SYS_B_0"                           2

We can see the database has substituted a system generated bind variable called :"SYS_B_0" for the literal values. This now means the two statement we ran have an exact test match, so we only have a single statement added to the shared pool, which has two executions.

BIND VARIABLES

Application developers must also ensure that SQL statements are shared within the shared pool. To achieve this goal, use bind variables to represent the parts of the query that change from execution to execution. If this is not done, then the
SQL statement is likely to be parsed once and never re-used by other users. To ensure that SQL is shared, use bind variables and do not use string literals with SQL statements. 

When constructing SQL statements, some Oracle applications use literals instead of bind variables.

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.

For example:
Statement with string literals:

SELECT * 
FROM employees 
WHERE last_name LIKE 'KING';

Statement with bind variables:

SELECT * 
FROM employees 
WHERE last_name LIKE :1;

Creating Bind Variables

You create bind variables in SQL*Plus with the VARIABLE command.

 For example
VARIABLE ret_val NUMBER

Referencing Bind Variables

You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. 

For example
:ret_val := 1;

To change this bind variable in SQL*Plus, you must enter a PL/SQL block. 

For example
SQL> begin
  2  :ret_val:=4;
  3  end;
  4  /
PL/SQL procedure successfully completed.

This command assigns a value to the bind variable named ret_val.

Displaying Bind Variables

To display the value of a bind variable in SQL*Plus, you use the SQL*Plus PRINT command. 

For example
SQL> print ret_val
RET_VAL
----------
         4

Creating, Referencing, and Displaying Bind Variables

To declare a local bind variable named id with a datatype of NUMBER, enter

SQL> VARIABLE id NUMBER

Next, put a value of "1" into the bind variable you have just created:
SQL> BEGIN
  2  :id := 1;
  3  END;
  4  /

If you want to display a list of values for the bind variable named id, enter

SQL> print id

ID
 1