Re: How to cope with nasty side effects of bind variable peeking

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

--
http://www.freelists.org/webpage/oracle-l