Jeremy <jeremy0505@gmail.com> writes:

> 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.
>

Hello Jeremy,

IIRC Oracle uses different optimizer parameters in PL/SQL.
I think in PL/SQL Oracle uses ALL_ROWS whereas in SQL*Plus
FIRST_ROWS is used. Maybe you found a query the differs in execution plan
with these optimizer settings.
Have a look on optimizer hints to see how you could use the same settings
in SQL*Plus as in PL/SQL.
I saw some queries the performed better with FIRST_ROWS hint.

Hope that helps,
Lothar

--
Lothar Armbrüster | lothar.armbruester@t-online.de
Hauptstr. 26 |
65346 Eltville |