Ideally, the value of cursor_sharing is set to exact. But sometimes, there are applications that don't use bind variables. In that case, the value of cursor_sharing can be set to force in which case Oracle will substiute constants with bind variables and provide the substituted value for the bind variable.
alter session set cursor_sharing = force;
select /* cursor_sharing = force */ * from dual where 0 = 1;
select /* cursor_sharing = force */ * from dual where 0 = 2;
select /* cursor_sharing = force */ * from dual where 0 = 3;
alter session set cursor_sharing = exact;
select /* cursor_sharing = exact */ * from dual where 0 = 4;
select /* cursor_sharing = exact */ * from dual where 0 = 5;
select /* cursor_sharing = exact */ * from dual where 0 = 6;
select
substr(sql_text, 1, 59) sql_text,
executions,
parse_calls,
sharable_mem,
persistent_mem,
runtime_mem
from
v$sql
where
sql_text like 'select /* cursor_sharing =%'
order by
sql_text;
--
-- SQL_TEXT EXECUTIONS PARSE_CALLS SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
-- ----------------------------------------------------------- ---------- ----------- ------------ -------------- -----------
-- select /* cursor_sharing = exact */ * from dual where 0 = 4 1 1 19420 16248 14840
-- select /* cursor_sharing = exact */ * from dual where 0 = 5 1 1 19420 16248 14840
-- select /* cursor_sharing = exact */ * from dual where 0 = 6 1 1 19420 16248 14840
-- select /* cursor_sharing = force */ * from dual where :"SYS 3 3 23598 32552 30904