How to cope with nasty side effects of bind variable peeking
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 24 Aug 2006 11:51:58 -0700
Ever since moving to 9i and 10g databases in the last couple years, the
most common performance problem I see is queries that are optimized by
the CBO based on one set of bind variables, but subsequently executed
with a drastically different set of bind values, resulting in a terrible
mismatch between the query and the execution plan. For example, the
following query:
select * from sales_orders where order_number >= :v1 and order_number <=
:v2;
is executed once with v1:= 200000 and v2:=200000, so the index on
order_number is used in the explain plan as it should be and performance
is great for this execution. But, then the same exact query is executed
with v1:=0 and v2:=999999 and this query gets stuck with the same
execution plan - using an index when it should be using a full table
scan, which of course results in terrible performance.
I'm just curious if everyone else out there is seeing this problem as
frequently as I am and how you are working around it. What I've been
doing in most cases is using stored outlines to force a consistent
"general purpose" execution plan. However in extreme cases like the one
above, there really isn't a good middle-ground, so one of the executions
will have to suffer and I have to force the best plan for the most
frequent or business-critical execution.
Is anyone running their production instances with
"_optim_peek_user_binds"=false? If so, how is it going? Any related
bugs?
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:
