How CURSOR_SHARING works in ORACLE?CURSOR_SHARING determines what kind of SQL statements can share the same cursors. The possible values are EXACT which is the default value and SIMILAR and FORCE.
Values:· EXACT Only allows statements with identical text to share the same cursor.
· FORCE : Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
· SIMILAR(deprecated) : Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
CURSOR_SHARING=EXACT(Default)
Share the plan only if text of SQL matches exactly with the text of SQL in Shared Pool.Example: Let’s take an example with the below Table:
SQL> Create Table MyTest(C1 NUMBER);
Table created.
SQL> Insert Into MyTest Values(101);
1 row created.
SQL> Insert Into MyTest Values(102);
1 row created.
SQL> COMMIT;
Commit complete.SQL> SELECT * From MyTest Where C1=101;
C1
----------
101
SQL> SELECT * From MyTest Where C1=102;
C1
----------
102
SQL> SELECT Sql_Text From V$SQL
Where Sql_Text like 'SELECT * From MyTest%'
Order by Sql_Text;
SQL_TEXT
--------------------------------------------------------------------------------
SELECT * From MyTest Where C1=101
SELECT * From MyTest Where C1=102
As you can see ORACLE has generated two different Plans in V$SQL. Every time there is a change in literal value ORACLE will generate different Plan.
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. Let’s check this with the same table: First we need to Flush the Shared Pool and set the value to Force.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> ALTER SESSION SET CURSOR_SHARING=FORCE;
Session altered.
SQL> SELECT Sql_Text From V$SQL
Where Sql_Text like 'SELECT * From MyTest%'
Order by Sql_Text;
no rows selected
SQL> SELECT * From MyTest Where C1=101;
C1
----------
101
SQL> SELECT * From MyTest Where C1=102;
C1
----------
102
SQL> SELECT Sql_Text From V$SQL
Where Sql_Text like 'SELECT * From MyTest%'
Order by Sql_Text;
SQL_TEXT
--------------------------------------------------------------------------------
SELECT * From MyTest Where C1=:"SYS_B_0"
As you can see ORACLE has generated only one Plan in V$SQL for both the statements. So, ORACLE will generate the Plan for the statement fired for the first time and then after if only changes in the literal values, same Plan will be used. But be careful when you use this.
This not only helps in savings DB server engine time for generating the plan but also helps in reducing the number of plans shared pool can hold.
CURSOR_SHARING=SIMILAR(deprecated)
SIMILAR causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.With Value FORCE, where C1=102 may be a good candidate for index scan while “where C1=20” should use a full table scan because 90% of the rows in the table has t1=20 (Assumption).
To avoid 2 statements using the same plan when the same plan is not good for one of them, we have CURSOR_SHARING=SIMILAR
Let’s see this with below example:
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> Drop Table MyTest;
Table dropped.
SQL> CREATE Table MyTest(C1 NUMBER, C2 NUMBER);
Table created.
SQL> BEGIN
2 For i in 1 .. 100 Loop
3 Insert Into MyTest Values(1,i);
4 End Loop;
5 COMMIT;
6 END;
PL/SQL procedure successfully completed.
SQL> SELECT Count(*) From MyTest;
COUNT(*)
----------
100
SQL> Update MyTest Set C1=2 Where Rownum<2;
1 row updated.
SQL> COMMIT;
Commit complete.
In this case C1 has updated with value “2” in first row and “1” in rest 99 rows.
SQL> CREATE INDEX IND_C1 ON MyTest(C1);
Index created.
SQL> ALTER SESSION SET CURSOR_SHARING=SIMILAR;
Session altered.
SQL> SELECT * From MyTest Where C1=2;
1 row selected.
SQL> SELECT * From MyTest Where C1=2;
99 rows selected.
SQL> SELECT Sql_Text From V$SQL
2 Where Sql_Text like 'SELECT * From MyTest%'
3 Order by Sql_Text;
SQL_TEXT
--------------------------------------------------------------------------------
SELECT * From MyTest Where C1=:"SYS_B_0"
SELECT * From MyTest Where C1=:"SYS_B_0"
This tells us that even though the 2 statements were similar, Oracle opted for a different plan. Now even if you put t1=20 (0 rows), Oracle will create another plan.
SQL> SELECT * From MyTest Where C1=20;
no rows selected
SQL> SELECT Sql_Text From V$SQL
2 Where Sql_Text like 'SELECT * From MyTest%'
3 Order by Sql_Text;
SQL_TEXT
--------------------------------------------------------------------------------
SELECT * From MyTest Where C1=:"SYS_B_0"
SELECT * From MyTest Where C1=:"SYS_B_0"
SELECT * From MyTest Where C1=:"SYS_B_0"
This is because the first time when the SQL ran, oracle engine found the literal value as “unsafe” because using the same literal value can cause bad plans for another similar SQL’s. So along with the PLAN, optimizer stored the literal value also. This will ensure the reusability of the plan only in case the same literal is provided. In case of any change, optimizer will generate a new plan.
But this doesn’t mean that SIMILAR and EXACT are same.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> SELECT * From MyTest Where C1=2 And C1=20;
no rows selected
SQL> SELECT * From MyTest Where C1=2 And C1=25;
no rows selected
SQL> SELECT Sql_Text From V$SQL
2 Where Sql_Text like 'SELECT * From MyTest%'
3 Order by Sql_Text;
SQL_TEXT
--------------------------------------------------------------------------------
SELECT * From MyTest Where C1=:"SYS_B_0" And C1=:"SYS_B_1"
Optimizer used single plan for both.
Conclusions:
1. Use CURSOR_SHARING=similar only when you have library cache misses and/or most of the SQL statements differ only in literal values2. CURSOR_SHARING=force/similar significantly reduces the number of plans in shared pool
Note:
1. Oracle does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries. Also, star transformation is not supported with CURSOR_SHARING set to either SIMILAR or FORCE
2. Setting CURSOR_SHARING to SIMILAR or FORCE causes an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals (in a SELECT statement). However, the actual length of the data returned does not change.
reference: https://tipsfororacle.blogspot.com/2017/02/cursorsharingexactforcesimilar-in-oracle_17.html