Re: Explain Plan irregularity

Bill Sonia wrote:
>
> While tuning a pl/sql procedure, I've noticed that my execution plan
> will change after I hard code the dates in a between condition for a
> insert into select statement. The between is meant to use passed in
> variables to run for different periods. In fact, if I hard code the
> dates the query will finish in 30 secs while passed in date variables
> will run in about 15 minutes.
>
> i.e.WHERE inv_date between '01-FEB-2002' and '01-MAR-2002';
> if.WHERE inv_date between p_start_date and p_end_date;
>
> Does anyone know why this is? Is there anything I can do about it?
>
> Thanks
>
> --
> Posted via dBforums
> http://dbforums.com

The literals are giving the optimizer more information than would be the case with bind variables. This is the time-honoured battle between parse costs versus the possibility of better plans.

If they query in question is not frequently run (and if it takes 15mins, then you would hope it isn't run too often), then you may as well just wear the parse costs and stay with the literals.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."