PL/SQL Block Query Slow

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:24:30 CDT