RE: Bad exectution plans due to bind variable peeking
- From: "Khemmanivanh, Somckit" <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>
- To: <Brandon.Allen@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Jun 2006 11:37:19 -0700
Does Baan have any advice? For example, in certain SAP versions they
explicitly recommend not enabling bind variable peeking.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Allen, Brandon
Sent: Friday, June 23, 2006 11:15 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Bad exectution plans due to bind variable peeking
Hello,
I'm supporting an ERP app (SSA BaanIV) that uses bind variables
extensively, which is a good thing for the most part, but occasionally
I'm having queries that are suffering from plan instability due to bind
variable peeking. A user runs a query with a certain set of bind
variable values and Oracle chooses the optimal execution plan for those
bind variables - then someone runs the exact same query with wildly
different bind variables, but gets stuck with the same execution plan
that was optimized for the first execution's bind variables. There are
3 possible solutions/workarounds that I'm aware of:
1) Don't use bind variables for these queries - unfortunately I can't do
this - it's a canned application. We do have access to modify queries,
but can't stop it from using bind variables.
2) Set _optim_peek_user_binds=false - I don't really want to do this due
to the fact that it would most likely do more damage than good since
these problems are the rare exception - 99%+ queries are running very
well.
3) Use stored outlines to force a more consistent plan that works
decently, although probably not optimally, for both sets of bind
variables - this is what I've done up to now.
I'm just curious if anyone else has any ideas other than the 3 above?
Thanks,
Brandon
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.
Other related posts:
