Re: Slow Query in PL/SQL Block

<brijeshmathew_at_gmail.com> wrote in message news:1124011375.086820.30330_at_g49g2000cwa.googlegroups.com...
> 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
>

How much time do the two different tests take ?

Unless I have mis-read your code, you will be executing the query in the pl/sql loop about 100 times - and this might reasonably take about 100 times as long as the SQL query depoending on how the optimizer treats
the subquery.

To start investigating, your best bet is to enable SQL trace, run the tests, end the session, and then use tkprof on the trace file to find out what the execution plans are, and see if the plans justify the difference in performnace.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005