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
>

Bill, version and platform information would be nice, column type of inv_date (can be date or varchar2). If inv_date is a date column, you do an implicit data conversion from varchar2 to date in the hardcoded version, and maybe also in the bind version? Any partitions or histograms in use? Stick to fully qualified binds, ie. to_date('01-FEB-2002','DD-MON-RRRR') as the execution plan can be calculated on parse of statement and in the execute phase.
Only 9i has the ability to 'sneak-peak' bind variables, hence ajusting the execution path to the bind values. Great for histogram and partition elimination....
My guess is that you have very unequal data distribution and/or use partitioning.
/svend Received on Sat Mar 16 2002 - 06:09:07 CST