RE: Bind variable peeking and Dynamic sampling

Yes, I've been on asktom too :-)

Here is the text of my ER. I was also told that it should be made public today.

Enhancement Request:
Please provide a method of instructing the CBO to always peek at the bind variables for specific
queries, and optimize the explain plan accordingly, thus treating them like
literals instead of bind variables. I'm sure there are a few different ways
this could be implemented, but what I have in mind is a hint, such as ALWAYS_PEEK, which could also be applied to any given query via a stored

outline so that you could implement it even in cases where you don't have
access to modify the

Business Needs:
We are running the BaanIV ERP application and have been having intermittent problems with certain queries ever since upgrading from Oracle 8 to 10g about a year ago. I've also seen the
same problem with other Baan systems running on 9i+ ever since bind variable
peeking was introduced. I understand it is a well-known problem, as documented
in Metalink note 387394.1. I know the recommendation is to modify the application so that it will either use literals instead of bind variables, or
have it differentiate the queries with different cardinalities by inserting
comments or otherwise modifying the query to have a different hash_value. But,
with COTS applications like Baan, SAP, etc., we (your customers) have very
little control over this. I have been working around the problems by flushing
the statements out of the shared pool when necessary with GRANT statements on a
table of the specific problem query, and by using stored outlines in some
cases. I'm considering turning off bind variable peeking (_optim_peek_user_binds=false) instance wide, but am afraid that may do more
harm than good. There are only a few queries where we have this problem frequently, but they are high impact so it would be very helpful if we could
just force the CBO to always peek at the bind variables for these queries.  

-----Original Message-----
From: Alberto Dell'Era [mailto:alberto.dellera_at_gmail.com]

You mean something like http://tinyurl.com/2fogq9 ? I've spotted many, many other people on asktom asking for the same thing.

Is it possible to have a look at the text of your ER ? - don't think it's "public".

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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