Re: optimization pb

franck_jeff_at_hotmail.com (FJ) wrote in message news:<c9bdc2f0.0303260755.1fcaa05f_at_posting.google.com>...
> Hi all,
> I do have a stored proc, with a simple select containing few joins.
> and in my conditions I have something like:
>
> where DCREATED >= dfrom
> and t.DCREATED < dto
>
> if dfrom and dto are taken in the SP parameters, the execution would
> take 8 sec
> and if I put the dates in hard in the proc like:
>
> where DCREATED >= '01-JAN-2000'
> and t.DCREATED < '20-MAR-2003'
>
> It takes 0.5 sec.
>
> Do any of you have an idea ? Thanks. Franck

This is not an unusual situation when comparing execution plans prepared by the optimizer when comparing known values verse unknown values.

Try running explain plan on the two separate queries intented to get the same result. And a query using constants vs the same query using bind variables is two different queries.

Your post is lacking in basic detail: CBO vs Rule, Oracle version, status of the statistics, existence of histograms, etc ... if you want the best possible responses you have to provide some background. But basically you are looking at a tuning 101 question/topic: constants provide more detail for the CBO to work with than bind variables (ignoring version 9 peeking).

HTH -- Mark D Powell -- Received on Thu Mar 27 2003 - 02:10:46 CET