Other Oracle database topics - PL/SQL Block Query Slow

Author

PL/SQL Block Query Slow

brijeshmathew@gmail.com

2005-08-14, 7:24 am

Hi Everyone

I use Oracle 9i Release 9.0.1
I am facing a problem in one of my stored procedures. The execution is
too slow. I have taken an extract from the procedure into a PL/SQL
block

declare
cursor mcur is select no no from sales.m where fd between
to_date('01052005','
ddmmyyyy') and to_date('10082005','
ddmmyyyy')
;
lsl Number(15,5);
lt Number(10);
fromdate date;
todate date;
begin
fromDate := to_date('01052005','
ddmmyyyy') ;
toDate := to_date('10082005','
ddmmyyyy') ;
for lcur in mcur loop
Select /*+USE_HASH(s payranking) */ sum(sl) - sum(r) , sum(t) into
lsl, lt from sales.s,sales.payranking
where valuedate between fromdate and todate and no = lcur.no and
payranking.paymethod = s.paymethod and payranking.countas = 1 ;
dbms_output.put_line('no' || lcur.no || ',' || lsl || ',' || lt);
end loop;
end;
/

____________________
____________________
________________
--------------------------------------------------------

Instead of this , if i give

Select /*+USE_HASH(s payranking) */ no, sum(sl) - sum(r) , sum(t)
from sales.s,sales.payranking
where valuedate between to_date('01052005','
ddmmyyyy') and
to_date('10082005','
ddmmyyyy') and payranking.paymethod = s.paymethod
and payranking.countas = 1
and no in ( select no from sales.m where fd between
to_date('01052005','
ddmmyyyy') and to_date('10082005','
ddmmyyyy') )
group by no
/

Statistics
--------------------------------------------------------
0 recursive calls
2 db block gets
396399 consistent gets
0 physical reads
0 redo size
4310 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
67 rows processed

I am not able to figure out why the SQL execution in this case is
faster than the PL/SQL block.. My requirement is to use the PL/SQL
block.
Is there anything that is wrong with my DB configuration. ? Any
parameters that cause this slowdown. ?
Can anyone help me in this regard.

Thanks in advance

Brijesh Mathew

Mark D Powell

2005-08-15, 8:23 pm

Brijesh, the two queries are not actually the same query. One is using
bind variables (pl/sql variables) while the other is using constants.
It is very likely the CBO is developing different plans for the two
versions since it has the actual constant values in one case and
unknown values in the other.

Run an explain plan on both versions. You can use :var1, :v2 to
represent bind variables in explain plan.

Also the pl/sql version is processing each and every row in the cursor
while the straight query just returns the entire row set. This is a
very significant difference in the two statements.

Explain plans for each version of the query may allow someone to
provide useful advice.

HTH -- Mark D Powell --

Sponsored Links