Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Slow query when using parameters
pmartin_at_mitsa.ch wrote:
> Hello
>
> I've got a performance problem with a query.
> The query is in a procedure in a package (although this seems to make
> no difference).
>
> The parameters for the query are passed as arguments to the procedure.
>
> With the parameters passed as arguments the query takes 1 minute 40
> seconds to run. However if I 'hardcode' the paramter values the query
> only takes 11 seconds to run.
>
> I've tried putting the parameters passed to the stored procedure
> into local variables and then use the local variables in the query but
> this doesn't change anything.
>
> Can anybody explain this difference ?
>
> Many thanks
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
When you 'hardcode' parameter value Oracle know that value at parse time, therefore the cost-based optimizer can use histograms for optimization your query. It can't be done with bind variables.