Re: ref cursor slow performance

The first problem I see is that the date and the format are funny. try  TO_DATE(''02-SEP-2002'',''DD-MON-YY'YY') the other might mean September 2nd, the year 20.
Jim
"kumar" <skum_ar_at_rediffmail.com> wrote in message news:b653bde2.0207122022.6cc8986d_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 .
> but still it takes the same time for the loop to process.
> 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.
> Pls ignore the syntactical errors in the block above as i'vnt given
> any declartion block etc.
> kumar
Received on Sat Jul 13 2002 - 08:07:20 CEST