RE: cursor sharing in 11g
- From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
- To: <eglewis71@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 1 Jun 2012 15:56:46 +0000
(Rewriting/resending because my previous email went to the moderator due to
overquoting) Have you investigated whether it is actually Adaptive Cursor
Sharing that is causing the change in plans or Cardinality Feedback? ACS is
evident from V$SQL.IS_BIND_SENSITIVE and IS_BIND_AWARE.CF from
v$SQL_SHARED_CURSOR.USE_FEEDBACK_STATS and V$SQL_PLAN.OTHER_XML. In my
experience on 11.2.0.3, ACS does not feature heavily but ACS has specific
requirements and it's influence is going to vary system-to-systemHowever,
Cardinality Feedback is very much in effect and influences a large number of
queries both positively and negatively - in fact on balance, more of the latter
than the former.I have a couple of queries where cardinality feedback is turned
off via hints because it has caused particular problems and am still
considering just turning it off at the database parameter level. I am
unconvinced by execution plans where cardinality feedback is being applied as
well as dynamic sampling particularly when the documentation and the optimizer
blog suggest that CF should not kick in when DS is being used. Hope this helps.
Cheers,Dominic
--
//www.freelists.org/webpage/oracle-l
Other related posts: