CURSOR_SHARING is an init.ora parameter which decides whether a SQL statement issued by a user is parsed freshly or will use an existing plan. This parameter has 3 values:
1. EXACT
This is the default value. This value share the plan only if text of SQL matches exactly with the text of SQL which is in shared pool. For example:
SQL> conn ilan/ilan
Connected.
SQL> select * from test where id=1;
ID
———-
1
SQL> select * from test where id=2;
ID
———-
2
SQL> select sql_text
from v$sql
where sql_text like ‘select * from test%’
order by sql_text; 2 3 4
SQL_TEXT
——————————————————————————–
select * from test where id=1
select * from test where id=2
As you can see from v$sql view, oracle need to generate 2 different plans as the SQL statements are not the same.
2. FORCE
When cursor_sharing set to force, this will force the same SQL statements to be reused provided the text is similar except the literal values. For example:
SQL> alter system set cursor_sharing=force;
System altered.
SQL> show parameter cursor
NAME TYPE VALUE
———————————— ———– ——————————
cursor_sharing string FORCE
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 20
SQL> alter system flush shared_pool;
System altered.
SQL> conn ilan/ilan
Connected.
SQL> select * from test where id=1;
ID
———-
1
SQL> select * from test where id=2;
ID
———-
2
SQL> select sql_text
from v$sql
where sql_text like ‘select * from test%’
order by sql_text; 2 3 4
SQL_TEXT
——————————————————————————–
select * from test where id=:”SYS_B_0″
As you can see, oracles still reparse the same SQL statement for the second execution. This is done by replacing the literal value with system generated bind variable (SYS_B_0). Even if we run the same statement again, oracle will use the same plan by just replacing the bind variable with the value specified in the ‘where clause’.
This option seems good but there is a drawback. Using the same plan for both the statement might not good for 1 of them. For example:
SQL> select count(*),id from test group by id;
COUNT(*) ID
———- ———-
9 1
1 2
‘test’ table consist of 10 rows with value ‘1’ the most. If the column ID is indexed and the below statement issued, oracle will use index which is a good execution plan:
SQL> select * from test where id=2;
Execution Plan
———————————————————-
Plan hash value: 578627003
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_ID | 1 | 13 | 1 (0)| 00:00:01 |
—————————————————————————
The same plan will be reused for the below statement as well when we set cursor_sharing =force:
SQL> select * from test where id=1;
Execution Plan
———————————————————-
Plan hash value: 578627003
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 9 | 117 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_ID | 9 | 117 | 1 (0)| 00:00:01 |
—————————————————————————
Index scan is not appropriate for this statement as 90% of data in table ‘test’ is with value ‘2’. So, setting cursor sharing to force is not always an appropriate solution when there a number of similar SQL statements.
3. SIMILAR
This option is introduced to overcome the force option issue. ‘SIMILAR’ option works the same way like force where it reuses the SQL statements with same text except the literal value. But, when histogram present on a particular column (like on ID column in the example earlier), another plan will be created. For example:
SQL> alter system set cursor_sharing=similar;
System altered.
SQL> show parameter cursor
NAME TYPE VALUE
———————————— ———– ——————————
cursor_sharing string SIMILAR
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 20
SQL> conn ilan/ilan
Connected.
SQL> select * from test where id=1;
ID
———-
1
SQL> select * from test where id=2;
ID
———-
2
SQL> select sql_text
from v$sql
where sql_text like ‘select * from test%’
order by sql_text; 2 3 4
SQL_TEXT
——————————————————————————–
select * from test where id=:”SYS_B_0″
select * from test where id=:”SYS_B_0″
As we can see above, though same SQL statement present, oracle will create another ‘child’ cursor to provide the best execution plan based on the histogram.
Conclusion
- Cursor sharing=similar is used when there is high number library cache misses provided most of the SQL statements are differ in literal values
- cursor sharing=force/similar will reduces the number of plans in shared pool
- if both the above requirements are not important in your database environment, it is always better to leave cursor sharing setting to default value (EXACT)