Adaptive cursor sharing in Oracle
Adaptive cursor sharing in introduced in 11g version of Oracle. It help in overcome the default bind variable behavior to make more execution plan for the query for specific bind variable value. In default only one execution plan use for bind query which is created first. Default behavior cause performance issue.So oracle introduced Adaptive cursor sharing new feature in 11g.
Note:
1. From 11g it is enabled by default.
2. It work with bind variable, may application use bind variable or cursor_sharing parameter must be set to FORCE value.
Check Status of Adaptive cursor sharing
col parameter for a35
col "Session value" for a10
col "Instance value" for a10
SELECT
x.ksppinm "Parameter", Y.ksppstvl "Session Value",Z.ksppstvl "Instance Value" FROM x$ksppi X, x$ksppcv Y, x$ksppsv Z
WHERE x.indx = Y.indx AND x.indx = z.indx
AND x.ksppinm like '_optimizer_adaptive_cursor_sharing%'
order by x.ksppinm;
Parameter Session Va Instance V ----------------------------------- ---------- ---------- _optimizer_adaptive_cursor_sharing TRUE TRUE
Disable the Adaptive cursor sharing in Oracle
alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;
--Disable bind variable or other parameter
alter system set "cursor_sharing"=exact scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both;
alter system set "_optimizer_extended_cursor_sharing"=none scope= both;
Enable the Adaptive cursor sharing
alter system set "_optimizer_adaptive_cursor_sharing"=TRUE scope= both;
--Enable bind variable
alter system set "cursor_sharing"=FORCE scope=both;
alter system set "_optimizer_extended_cursor_sharing_rel"=SIMPLE scope=both;
alter system set "_optimizer_extended_cursor_sharing"=UDO scope= both;