Re: ref cursor slow performance

kumar wrote:

> 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
>

Why use a ref cursor, seems you dont use any binds but hardcoded values,   no indication of open cur1 for str using bind1, bind2.... Try the same thing with good old fashion cursor cur1 is .... and se if that is faster. Check the execution path. If I am not mistaken here (corrections welcome) plsql uses default either rule or all_rows, depending on your optimizer settings and the statistics. Try explain with all_rows hint - it might be very different from first_rows or rule. Do a trace level 8 or 12, check the execution plan in the trace file. And the waits while you are there. Dont use tkprof ... explain, that calls explain plan and does *not* show the execution plan from the trace   file.
Check your date format too. 'dd-mon-yyyy' (4 y'es)

/Svend Received on Sun Jul 14 2002 - 20:54:54 CEST