Ref cursor fetch loop performance problem in 8i
Date: 12 Jul 2002 02:59:22 -0700
Message-ID: <b653bde2.0207120159.29d9dc63_at_posting.google.com>
Hi
[Quoted] 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.
Pls ignore the syntactical errors in the block above as i'vnt given
any declartion block etc.
kumar Received on Fri Jul 12 2002 - 11:59:22 CEST