Question

Performance issue with Cursor For Loop

  • 28 July 2021
  • 5 replies
  • 180 views
N
Rank
Userlevel 4
Badge +8

Hi,
 

There is a performance issue when accessing a cursor with a for loop within a procedure.

If we execute the query directly in the database, it will run within seconds. But if we access it via a FOR loop, it will hang at the FOR statement for around 30 minutes in the first iteration. Example is given below.  This query will fetch around 35500 records from database.

Eg:

Procedure XXXX

     Cursor report_query_ IS

     Select YYYY From ZZZZ;

Begin

     For row_ in report_query_ LOOP (execution hangs at this statement)

          --do something;

     END LOOP;

END XXXX;

Any idea how to investigate this? Why there is a difference when running the query directly and via a for loop?

Best Regards,
Nisansala

This topic has been closed for comments

5 replies

Nilushi Silva
Rank
Userlevel 5
Badge +8

Hi @Nisansala Liyanage ,

I hope cursor does not fetch any other parameters? Could you add a screenshot of the code. Could i also know 30 min instance was executed through a DB test window or through client?

Nilushi-

Shardha Weeratunga
Rank
Userlevel 5
Badge +6

Hi @Nisansala Liyanage,

Please try limiting the number of records fetched into the cursor forcefully, and check for performance improvement.

This will help to narrow down the issue.

/Shardha

ShaR
S
Rank
Userlevel 6
Badge +12

Hi Nisansala,

 

Check the table ZZZZ has indexes.

You can use a execution plan window and run the query : this gives you some idea on what happens.

 Select YYYY From ZZZZ; 

You can try for an index add to see if the execution plan cost changes. 

 

Regards,

Sahan

M
Rank
Userlevel 3
Badge +10

Does the query have a function call in it?  If so remove the function call by either commenting it out or rewriting the query not to use function calls.  Function calls are not accounted for in the explain plan cost.

There is no explicit close on the cursor so could there be multiple instances of this procedure being called at the same time?

How busy is the table behind the query?  If the table or some other resource is busy this can delay execution in some circumstances.

How many CPU threads are allocated to this database instance?

How do you know it is hanging on the for loop call?  Are you using the debugger?  If so you have two issue a second step to get the debugger to fetch the first row.  If not using a debugger how do you know that is where it is hanging?

I have to agree with Nilushi, we need to see the code.  The generic demo does not help when it comes to performance.

P
Rank
Userlevel 3
Badge +4

Hi Nisansala

There might be a performance drop due to the oracle "Bind Variable Peeking".
I think the SQL used in your cursor have placeholders in the SQL statement (under where condition) that must be replaced with a valid value when executing the query.
Please check the execution plan of it and improve the query accordingly .

You can used below code block to obtain the execution plan.

DECLARE
 
V1   varchar2(20):= 'X';
 
BEGIN
 
FOR r IN (SELECT YYYY
    FROM ZZZZ
    WHERE  condition = V1 ) 
LOOP   
   NULL;
END LOOP;
 
FOR p IN (SELECT * from table(dbms_xplan.display_cursor)) LOOP
dbms_output.put_line(p.plan_table_output);
END LOOP;

END;

Thanks & Regards,
Sameera.

Terms & ConditionsCookie settings