Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Slow Query in PL/SQL Block
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 Received on Sun Aug 14 2005 - 04:22:55 CDT