Cursor Sharing - Oracle

 

Deprecated the Cursor_Sharing = 'SIMILAR' Setting (Doc ID 1169017.1)

Oracle recommends that customers discontinue the user and setting of cursor_sharing = SIMILAR due to the many problematic situations customers have experienced using it. 

The ability to set this is removed in version 12c of the Oracle Database (the settings of EXACT and FORCE remain available).

The parameter CURSOR_SHARING can take 2 values :

 – EXACT

 – FORCE

1) Let's discuss ---->      CURSOR_SHARING=EXACT

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE

---------------------------------- ----------- --------------

cursor_sharing                       string      EXACT

Exact :- Only allows statements with identical text to share the same cursor.

SQL> alter system flush shared_pool;

System altered.

Parent    Parent   Parent

   |           |           |

Child      Child     Child

— Issue identical statements with different values of literals

conn sam/sam

select count(*) from test where id1=1;

select count(*) from test where id1=2;

select count(*) from test where id1=3;

SQL> show user

USER is "SAM"

SQL> select count(*) from test where id1=1;

  COUNT(*)

----------

         1

SQL> select count(*) from test where id1=2;

  COUNT(*)

----------

      1000

SQL> select count(*) from test where id1=3;

  COUNT(*)

----------

      2000

— Check that the 3 parent cursors have been created

— Note that there is one record for each statement in v$sqlarea as one parent cursor is created for each sql statement since  each of these statements differ in their text.

•     Each statement has different SQL_ID/HASH_VALUE
•    There is one child per parent cursor (version_count=1)
•     Execution plans for id = 2,3 is same (full table scan) (same PLAN_HASH_VALUE)
•     Execution plan for id = 1 is different (indexed access

SQL> col sql_text for a30 word_wrapped

SQL>

SQL> SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE

FROM V$SQLAREA

WHERE LOWER(SQL_TEXT) LIKE 'select count(*) from test%'

AND LOWER(SQL_TEXT) NOT LIKE '%HASH%';  

SQL_TEXT                                SQL_ID        VERSION_COUNT HASH_VALUE PLAN_HASH_VALUE

--------------------------------------- ------------- ------------- ---------- ---------------

select count(*) from test where id1=3   1n09m564gh0q3             1 2297955011      4192825871

select count(*) from test where id1=2   20nhaap8uxf7s             1 1370405112      3507950989

select count(*) from test where id1=1   bavqx2mw26wg0             1 4163072480      3507950989

— Note that 3 child cursors have been created for the 3 statements

SQL> col child_number for 99

SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE, PLAN_HASH_VALUE

FROM V$SQL

WHERE LOWER(SQL_TEXT) LIKE 'select count(*) from test%'

AND LOWER(SQL_TEXT) NOT LIKE '%HASH%';SQL>   2    3    4

SQL_TEXT                                      SQL_ID            CHILD# HASH_VALUE PLAN_HASH_VALUE

--------------------------------------------- ------------- ---------- ---------- ---------------

select count(*) from test where id1=3         1n09m564gh0q3          0 2297955011      4192825871

select count(*) from test where id1=2         20nhaap8uxf7s          0 1370405112      3507950989

select count(*) from test where id1=1         bavqx2mw26wg0          0 4163072480      3507950989

— We can see that in all 6 cursors have been created :
– 3 parent cursors and
– 3 child cursors

Each of the cursor occupies memory. 

Parent cursors contain sql text whereas child cursor contains execution plan, execution statistics and execution environment. 

Note:- If we replace literal with a bind variable, all the 3 statements
will be identical and hence only parent cursor needs to be created. 

Multiple child cursors can be created for different values of the bind variables.

2) Let's discuss ----------->   CURSOR_SHARING=FORCE

Share the same plan if there is only difference in Literal values.

Which means if two or more SQL's are same except their literal values 

will share the same plan.

      Parent

         |

   +—+—-+ +—+—-+

   |           |

  Child       Child

SQL> alter system set cursor_sharing=force;

System altered.

SQL> show parameter cursor_sharing

NAME                TYPE      VALUE

------------------- --------- -------------

cursor_sharing      string    FORCE

– Flush the shared pool and issue query using the column with histogram on it so that optimizer is aware of the skew.

SYS> alter system flush shared_pool;

SQL> conn sam/sam

Connected.

SQL>

SQL>

SQL>

SQL> select count(*) from test where id2=1;

  COUNT(*)

----------

         1

SQL>

SQL>

SQL>

SQL> select count(*) from test where id2=2;

  COUNT(*)

----------

      1000

SQL>

SQL>

SQL> select count(*) from test where id2=3;

  COUNT(*)

----------

      2000

SQL>

SQL>

SQL>

SQL> col sql_text for a30 word_wrapped

SQL>

SQL>

SQL>

SQL> SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE

FROM V$SQLAREA

WHERE LOWER(SQL_TEXT) LIKE 'select count(*) from test%'

AND LOWER(SQL_TEXT) NOT LIKE '%HASH%'; 

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE PLAN_HASH_VALUE

------------------------------ ------------- ------------- ---------- ---------------

select count(*) from test      3tcujqmqnqs8t             1 3981140249      2432738936

where id2=:"SYS_B_0"

Note:- Only one child cursors has been created (version_count=1)

SQL> col child_number for 99

         SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,

                        PLAN_HASH_VALUE

         FROM V$SQL

WHERE LOWER(SQL_TEXT) LIKE 'select count(*) from test%'

AND LOWER(SQL_TEXT) NOT LIKE '%HASH%';SQL>   2    3    4    5

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE PLAN_HASH_VALUE

------------------------------ ------------- ---------- ---------- ---------------

select count(*) from test      3tcujqmqnqs8t          0 3981140249      2432738936

where id2=:"SYS_B_0"

Note that 1 child cursor has been created and child has a distinct execution plan (PLAN_HASH_VALUE)

Share the same plan if there is only difference in Literal values.

Which means if two or more SQL's are same except their literal values will share the same plan.