Re: Guidelines for avoid Bind Variable Peeking behavior

I often here people complaining about "bind variable issues" and "histogram" problems. I'd like to understand the issues better myself.

I am facing a lot of problems with bind variables because the access plan is
> changing and a critical performance problem occurs.(I have already posted a
> question "Histogram worthwhile" here at Oracle-l). I am suffering the "bind
> variable peeking" behavior.
>
> My customer is very disapointed about this "feature" of Oracle and he is
> asking me what to do to avoid this behavior.
>

Are you suggesting that bind variable peeking is causing plans to change?

My understanding of a potential problem of bind variable peeking is that the first time a query gets parsed, its bind variables are "peeked at" and a plan is created. The problem being, if this "first" query happened to have unlucky/unusual values then a sub-optimal plan would be created and used subsequently. This would be a problem until the plan ages out and the query re-parsed.

Is this not the case? Would we want the next query to be peeked at and have the dodgy plan binned wouldn't we? But that's not what happens though is it? We are stuck with it until its invalidated or aged out.

Aren't you seeing lots of plan invalidations for what ever reasons, rather than a bind variable peeking issue?

Regards
Tony

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