Re: cursor sharing in 11g

  • From: Sreejith S Nair <sreejithsna@xxxxxxxxx>
  • To: "dombrooks@xxxxxxxxxxx" <dombrooks@xxxxxxxxxxx>
  • Date: Sun, 3 Jun 2012 07:54:14 +0530

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@xxxxxxxxxxx> 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


Other related posts: