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

Jeremy <jeremy0505_at_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_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |