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@xxxxxxxxx] 


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

Other related posts: