Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> How to cope with nasty side effects of bind variable peeking

How to cope with nasty side effects of bind variable peeking

From: Allen, Brandon <>
Date: Thu, 24 Aug 2006 11:51:58 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45059E1D8B@NT15.oneneck.corp>

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?  


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.

Received on Thu Aug 24 2006 - 13:51:58 CDT

Original text of this message