Re: Why woulld a query run with different performance inside/outside of a stored procedure?

"Jeremy" <jeremy0505_at_gmail.com> schreef in bericht news:MPG.201023bfcb10271b98a3c3_at_news.individual.net...
> Puzzled. Usually if I find a slow running query in a stored procedure, I
> lift it out and run it in sqlplus or TOAD etc and tune it from there.
>
> Today I am looking at a query which, when declared in a procedure like
> this:
>
> for h in (select ......)
> loop
> ..
> end loop;
>
> takes 3-4 seconds to parse and execute - i.e. if I stick a trace line
> before the "for" and after the "loop" line, the first row becomes
> available after 3-4 seconds.
>
> Lifting the SQL out and running in sqlplus, it takes < 0.5 seconds and
> has a pretty good explain plan. The query has one condition based on a
> parameter in the plsql procedure which, when run in sqlplus, is replaced
> by a constant - other than that the SQL is identical.

You should replace this by a binding variable! See what happens then!

Shakespeare

>
> Not sure where to start in trying to find the cause of this discrepancy,
> would appreciate any pointers.
>
> cheers
>
> --
> jeremy
>
> ============================================================
> ENVIRONMENT:
> Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
> ============================================================
Received on Thu Jan 11 2007 - 05:05:34 CST