Re: How to cope with nasty side effects of bind variable peeking
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <Brandon.Allen@xxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Aug 2006 00:55:58 +0200
Hi Allen,
> What if Oracle could evaluate the incoming bind variable values for a
> query and compare them to the bind values used at parse time for all
> previously cached versions of the same query and if they don't match,
> then the CBO peeks at the new bind variables and comes up with the best
> plan.
My experience is: don't use bind variables in statements where different values
of the bind variables produce different plans.
What is the point of saving some milliseconds in parsing while potentially
loosing hours performing bad plans?
> 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.
If bind variables are unavoidable (performance reasons), a pragmatic solution
is in my opinion that the application check the range an generates different
statements for a small and a big range.
Regards
Jaromir D.B. Nemec
----- Original Message -----
From: Allen, Brandon
To: oracle-l
Sent: Thursday, August 24, 2006 10:11 PM
Subject: RE: How to cope with nasty side effects of bind variable peeking
Other related posts:
