Dynamic Cursors vs Standard Cursors - speed issue 1

[Spikmeister]

I have two procedures that do identical things, one uses a Standard cursor that it loops through:

CURSOR Cursor_SP IS		
  SELECT ID, CCT_ID
  FROM SP_2
  WHERE Inserted_By = 'I';

BEGIN

  FOR List_SP IN Cursor_SP LOOP

    (Lots of data crunching with other tables)

  END LOOP;

END;

The other uses a dynamic one which it fetches into variables and then loops through:

sql_Statement VARCHAR2 (2000);
strid NUMBER;
strcctid VARCHAR2 (255);

BEGIN
  sql_Statement := 'SELECT ID, CCT_ID FROM SP_2 WHERE Inserted_By = ''I'' ';

  OPEN Sp_Cursor FOR sql_Statement;
    LOOP
		
      FETCH Spares_Cursor 
        INTO strid,
        strcctid;
      EXIT WHEN Spares_Cursor%NOTFOUND;

      (Same data cruching as other procedure)

    END LOOP;
  CLOSE Sp_Cursor;
END;

Is there any reason why the procedure with the Standard cursor takes half the time to run through that the one with the dynamic cursor does...???

 

[sem]

Sure, because dynamic cursor should be parsed during runtime.

Regards, Dima

 

[Spikmeister]

Please elaborate, I thought it was parsed during runtime...???

 

[sem]

That's true for dynamic cursors; standard ones are parsed during compile time.

Regards, Dima

 

[Spikmeister]

Ah, now it makes sense... Thank you...

 

[sem]

I'd suggest you to read about DBMS_SQL package to understand it more clear. Then just assume, that somebody kindly replaced a set of commonly used calls to this package to provide shorter notation.

Regards, Dima