Re: Ref cursor fetch loop performance problem in 8i

"kumar" <skum_ar_at_rediffmail.com> wrote in message news:b653bde2.0207120159.29d9dc63_at_posting.google.com...
> Hi
> i've a performance problem when using ref cursor. My code is as
> follows
> from
>
>
> str:=' select a.col1,b.col2,c.col3(, a list of columns..)
> from tab1 a,tab2 b ,tab3 c
> WHERE a.col6=3
> AND a.col3 <= TO_DATE(''02-SEP-2002'',''DD-MON-YY'') + 1
> AND b.col2=62
> AND c.col1=b.col2
> AND a.col4=b.col1
> AND EXISTS ( SELECT 1 FROM tab4 c where col1=b.col1)
> AND EXISTS ( SELECT 1 FROM tab5 d where col2=a.col5) ';
> open cur1 for str;
> loop
> fetch cur1 into typ1 ; /* typ1 is a record type variable */
> exit when cur1%notfound;
> tot := tot+1;
> end loop;
>
> The above block is a small part of my actual procedure. In this, the
> table a contains 300,000 records, table b contains more than 2 million
> records and table c contains only 200 records. All tables analyzed.
> The issue is when i execute the query alone without the following
> cursor loop it executes in 1 second. But the block after that where i
> use the cursor ,it takes nearly 3 minutes to complete the complete
> fetch loop.
> The main probs is this query returns around 7500 records. The same
> block if i add a rownum restriction with <10 or 100 in the query
> records its faster.
> I tried to use the oracle 8i bulk collect statements like BULK
> INTO(tho' the bulk collect directly does not support record type
> variables or the dynamic query) . but still it takes the same time for
> the loop to process.
> Can anyone suggest methods to tune the block . I think there is a
> problem in the cursor memory area,as each fetch by the cursor takes
> time,even tho' i do a simple count inside that.

How does

  SELECT COUNT(*) INTO TOT FROM (
your select statement';

Perform? Received on Fri Jul 12 2002 - 12:09:53 CEST