RE: cursor sharing in 11g
From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Fri, 1 Jun 2012 15:56:46 +0000
Message-ID: <DUB115-W6084BE28F3E51365E343DAA1080_at_phx.gbl>
(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 p articularly when the documentation and the optimizer blog suggest that CF should not kick in when DS is being used. Hope this helps. Cheers,Dominic
Date: Fri, 1 Jun 2012 15:56:46 +0000
Message-ID: <DUB115-W6084BE28F3E51365E343DAA1080_at_phx.gbl>
(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 p articularly when the documentation and the optimizer blog suggest that CF should not kick in when DS is being used. Hope this helps. Cheers,Dominic
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 01 2012 - 10:56:46 CDT