RE: adaptive cursor sharing and bind peeking

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <mwf@xxxxxxxx>, <Christian.Antognini@xxxxxxxxxxxx>, "'Carlos Sierra'" <carlos.sierra.usa@xxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Fri, 31 May 2013 14:06:44 -0400

Never mind. I should have read JL's post reference to:

This doesn't seem to agree with a blog post that Randolf Geist wrote some
time ago:
http://oracle-randolf.blogspot.co.uk/2011/01/adaptive-cursor-sharing.html

He does mention a bug, relating to ACS not working properly with PL/SQL, and
presumably that's fixed by now; even so, "checking before every EXECUTION"
doesn't seem to match his observations.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: Friday, May 31, 2013 1:37 PM
To: Christian.Antognini@xxxxxxxxxxxx; 'Carlos Sierra'
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: adaptive cursor sharing and bind peeking

In the context given, I *think* the open cursor operation is effectively
what Carlos includes as one of the possibilities of an execution in his
reference to "every execution" (as opposed to each fetch from an open
cursor).

Please straighten me out on this if I'm misunderstanding.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Christian Antognini
Sent: Friday, May 31, 2013 3:06 AM
To: Carlos Sierra
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: adaptive cursor sharing and bind peeking

Hi Carlos

> Once it is bind aware on every execution it looks at the values of 
> binds and compares to acs selectivity profile for this sql

When a cursor is open, its execution plan cannot be changed. As a result,
the peeking and everything else that goes with it in case of a bind-aware
cursor can only be done when a parse call is performed. This is also the
reason why static cursors in PL/SQL loops or Java applications using
client-side statement caching cannot take advantage of ACS.


HTH
Chris Antognini

Troubleshooting Oracle Performance, Apress 2008 http://top.antognini.ch 

--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


Other related posts: