Re: cursor sharing in 11g
Date: Sun, 3 Jun 2012 07:54:14 +0530
Message-Id: <4A0435DF-E84D-429C-9E3D-C398F9E47FC6_at_gmail.com>
In some our databases, we have seen different plans for same SQL which were not so optimal. A number of child cursors where seen and version_count sometimes go near 1000 for one sql. I am in 11.2.0.2. This has once caused severe library cache contention and concurrency wait events. As per Oracle support we have to disable ACS to reduce the number of child cursors and SQL version count. The reasons being rolling invalidation count and bind equivalent mismatch.
Cursor sharing is set to exact. We are testing our application to arrive at a best value of cursor sharing along with ACS enabled.
Cardinality feedback has once made a SQL which takes 5 seconds to execute in first run to complete in 21 minutes on second run. We have got a patch to solve this issue.
Regards,
Sreejith
-- Sent from my iPhone
On 01-Jun-2012, at 9:26 PM, Dominic Brooks <dombrooks_at_hotmail.com> wrote:
(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-l
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Jun 02 2012 - 21:24:14 CDT