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;