Re: Slow Query in PL/SQL Block

brijeshmathew_at_gmail.com schrieb:
> 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..

I guess, its because you tell Oracle to do 67 times the same work , that he can do only 1 time and yield identical results ...

Best regards

Maxim Received on Sun Aug 14 2005 - 09:06:34 CDT