private SQL area is used to store bind variable values and other info. The private SQL areas for each execution of a statement are not shared and may contain different values and data.

Cursor is a name or handle to a specific private SQL area. 

As shown in the following graphic, you can think of a cursor as a pointer on the client side and as a state on the server side. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.

What is cursor sharing?

Multiple private SQL areas in the same or different sessions can reference a single shared SQL area, this 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.

Let’s understand this where cusrsor_sharing =EXACT and literal used in WHERE clause

select count(1), max (SAL) from EMP where deptno =20;

select select count(1), max (SAL) from EMP where deptno =10;

select  count(1), max (SAL) from EMP where deptno =10;è extra space between select and count

select count(1), max (SAL) From EMP where deptno =10; è “F” capital in From

count(1), max (SAL) from EMP where deptno =30;

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

set line 150

COL SQL_TEXT FORMAT a60

SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE

FROM   V$SQLAREA

WHERE  SQL_TEXT LIKE ‘%EMP%’

AND    SQL_TEXT NOT LIKE ‘%SQL_TEXT%’ and SQL_TEXT NOT LIKE ‘%CACHE%’

order by plan_hash_value;

Now run same command but on different user

You can see the version_count changed to 2. This means that this parent cursor has been reused / shared (because exact same syntax) and instead a child cursor has been created.

You can also see from v$sql that statement with sql_id “1wvtzrgt5tvyn” has two child cursor. One from SCOTT user and another from SHALABH user.

set lines 150

COL SQL_TEXT FORMAT a60

COL CHILD# FORMAT 99999

COL EXEC FORMAT 9999

COL SCHEMA FORMAT a10

SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT,

CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL

WHERE SQL_TEXT LIKE ‘%EMP%’ AND SQL_TEXT NOT LIKE ‘%SQL_TEXT%’ and SQL_TEXT NOT LIKE ‘%CACHE%’ ORDER BY SQL_ID;

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. 

select child_number, bind_mismatch, optimizer_mode_mismatch, literal_mismatch, auth_check_mismatch,TRANSLATION_MISMATCH,BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id = ‘1wvtzrgt5tvyn’;

In this example, the TRANSLATION_MISMATCH column shows that the two statements (select  count(1), max (dob) from T1 where dept_id = 10) referenced different objects, resulting in a TRANSLATION_MISMATCH value of Y for the last statement. Because sharing was not possible, each statement had a separate child cursor, as indicated by CHILD_NUMBER of 0 and 1.

Now see what happens when cusrsor_sharing =EXACT and bind variable used in WHERE clause

Here three statements were executed using different value of deptno using bind variables. While using literal we saw that different parent cursor were opened but, in this case, only one cursor will be opened.

Version_count =1 tells us that even child cursors was reused. EXEC tells there were 3 executions of this sql.

Cursor_sharong=FORCE and Literals

ALTER SESSION SET CURSOR_SHARING=FORCE;

select * from Shalabh.t1 where emp_id < 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

select * from Shalabh.t1 where emp_id < 50;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

select * from Shalabh.t1 where emp_id < 9000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

Explain plan shows that shows that all three statements used the same plan. The optimizer chose the plan, an index range scan, because it peeked at the first value (10) bound to the system bind variable, and picked this plan as the best for all values. In fact, this plan is not the best plan for all values. When the value is 9000, a full table scan is more efficient.

set line 150

COL SQL_TEXT FORMAT a60

SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE

FROM   V$SQLAREA

WHERE  SQL_TEXT LIKE ‘%Shalabh.t1%’

AND    SQL_TEXT NOT LIKE ‘%SQL_TEXT%’ and SQL_TEXT NOT LIKE ‘%CACHE%’

order by plan_hash_value;

set lines 150

COL SQL_TEXT FORMAT a60

COL CHILD# FORMAT 99999

COL EXEC FORMAT 9999

COL SCHEMA FORMAT a10

SELECT SQL_ID, PARSING_SCHEMA_NAME AS SCHEMA, SQL_TEXT,

CHILD_NUMBER AS CHILD#, EXECUTIONS AS EXEC FROM V$SQL

WHERE SQL_TEXT LIKE ‘%Shalabh.t1%’ AND SQL_TEXT NOT LIKE ‘%SQL_TEXT%’ and SQL_TEXT NOT LIKE ‘%CACHE%’ ORDER BY SQL_ID;