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

"Jeremy" <jeremy0505@gmail.com> schreef in bericht
news:MPG.201023bfcb10271b98a3c3@news.individual.ne t...
> 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
> ================================================== ==========

In article <45a619fd$0$335$e4fe514c@news.xs4all.nl>, What's in a
namespace says...
> >
> > 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!
>
>

As I understand it bind variables increase efficiency - therefore I
guess the use of a constant in sqlplus shoud, if anything, have a
detrimental impact on performance?

Anyway, done. Same result (i.e. sub 1-second).

DECLARE p_candidate_id NUMBER:=311662;
BEGIN
dbms_output.put_line(TO_CHAR(SYSDATE,'hh24:mi:ss') );
FOR h IN (select ....
LOOP
NULL;
END LOOP;
dbms_output.put_line(TO_CHAR(SYSDATE,'hh24:mi:ss') );
END;

So why should this perform so poorly in the stored procedure versus when
run as a SQL statement or as an anonymous pl/sql block from within a
client tool?

Not actually seeking the answer here but at least an indication of where
I should be looking.

--
jeremy

"Jeremy" <jeremy0505@gmail.com> schreef in bericht
news:MPG.201030a8b6838e7198a3c4@news.individual.ne t...
> In article <45a619fd$0$335$e4fe514c@news.xs4all.nl>, What's in a
> namespace says...
>> >
>> > 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!
>>
>>
>
> As I understand it bind variables increase efficiency - therefore I
> guess the use of a constant in sqlplus shoud, if anything, have a
> detrimental impact on performance?
>
> Anyway, done. Same result (i.e. sub 1-second).
>
> DECLARE p_candidate_id NUMBER:=311662;
> BEGIN
> dbms_output.put_line(TO_CHAR(SYSDATE,'hh24:mi:ss') );
> FOR h IN (select ....
> LOOP
> NULL;
> END LOOP;
> dbms_output.put_line(TO_CHAR(SYSDATE,'hh24:mi:ss') );
> END;
>
> So why should this perform so poorly in the stored procedure versus when
> run as a SQL statement or as an anonymous pl/sql block from within a
> client tool?
>
> Not actually seeking the answer here but at least an indication of where
> I should be looking.
>
> --
> jeremy
>

Sorry, Jeremy, I read your post the wrong way around. But still I think you
should use a bind variable in SQLPLUS (select ... from ... where column_x=
:A) in stead of a contant to compare the performance. But I have no further
indication for clues...

Shakespeare

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

Interesting article:
http://www.oracle.com/technology/tec...atures_Doc.pdf

"3.2. BULK BINDING IN NATIVE DYNAMIC SQL 3.2.1. DEFINING
Consider a program to populate elements of a PL/SQL collection from a
SELECT query thus...

declare
type employee_ids_t is table of employees.employee_id%type index by
binary_integer;
employee_ids employee_ids_t;
n integer:=0;
begin
for j in ( select employee_id from employees where salary < 3000 )
loop
n := n+1;
employee_ids(n) := j.employee_id;
end loop;
end;

Each explicit row by row assignment of the collection element to the
cursor component causes a context switch between the PL/SQL engine and
the SQL engine resulting in performance overhead. The following
formulation (one of a family of constructs generically referred to as
bulk binding and available pre-Oracle9i)...

begin
select employee_id
bulk collect into employee_ids
from employees where salary < 3000;
end;

...substantially improves performance by minimizing the number of
context switches required to execute the block. (The above fragments
work pre-Oracle 9i.)"

The above is possibly one explanation - cost of context switching.

Predicted cardinality (expected number of rows that will be returned)
is another possibility - a 10053 trace would tell if this is the case.
When bind variables are used, the predicted cardinality will be 5% of
the rows. The predicted cardinality rules change if constants
(literals) are used, and change again if histograms are present.
Changes in the predicted cardinality can change the execution plan,
possibly to a less efficient one. This is a second possible
explanation.

When bind variables are used, Oracle 9i is permitted to peek at the
bind variables on the initial hard parse (it seems that there may be an
opportunity for Oracle to peek at the bind variables on every execution
if CURSOR_SHARING is set to SIMILAR, but there are warnings about using
this setting in the "Cost-Based Oracle Fundamentals" book). So, if
atypical values were used for the bind variable values during the
initial hard parse, future executions of the SQL statement with typical
values could suffer from an inefficient execution plan. If the two SQL
statements (one executed in PL/SQL and the other in SQLPlus) differ by
even a single space, they are not considered the same SQL statement,
and a hard parse will be required. This is a third possible
explanation.

If CURSOR_SHARING is set to FORCE, constants (literals) will be
automatically replaced with bind variables. If the two SQL statements
(one executed in PL/SQL and the other in SQLPlus) differ by even a
single space, they are not considered the same SQL statement, and a
hard parse will be required. Thus, the two SQL statements may have
different execution plans. This is a fourth possible explanation.

There must be more possible explanations.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

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 |

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.

- Bobby

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