I would do a 10046 trace if you can. That will show you what it is
spending its time on. You could tkprof it also with waits=yes.
Jeremy wrote:
> 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.
>
> 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 Fri Jan 12 2007 - 15:30:03 CST
