21st Century PL/SQL

April 06, 2005

Optimisation of array processing

From Oracle 10g onwards, for the following for loop style array processing is automatically done.

---

for i in (select empno from emp)
loop

  ;

end loop;

the records are fetched 100 at a time automatically.

However, many people have written their code like:

cursor c is
select empno from emp;

for i in c loop
     ;

end loop;

This code does not take advantage of array processing.

In the newer version, even such code should be made to take advantage of array processing

Thanks

Posted by viswanathan venkat ramanan at 03:47 AM | Add your thoughts (1)

October 06, 2005

New pragma to disavow knowledge of code having been run

After PL/SQL code is run it remains loosely associated with the session that ran it as evidenced by records in the V$OPEN_CURSOR view. This view also includes any cursors or DML the code performed. Sometimes it would be handy to avoid this association when one knows that particular code will never be executed by the session again. Perhaps syntax something like this:

CREATE OR REPLACE PROCEDURE bob AS

  PRAGMA disavow_knowledge;
...

One example of where this would be handy is with code that writes audit records. I don\\\'t want valuable application cursors to be pushed out of the session allocated space just because some auditing code ran. Conversely if I set the OPEN_CURSORS parameter really high I would not want the space to be cluttered with the auditing code.

Posted by Darryl Hurley at 05:33 PM | Add your thoughts (1)