Demonstrating the LRU behavior of the PL/SQL cursor cache

Consider this trivial program, Open_Close_5_Different. Had it not been for the discussion above, you might think that you would see no trace of its cursors in v$open_cursor when it exits. Compile and run it, and observe it from another session as observer above. You'll see...

BEGIN Open_Close_5_Different; END;
SELECT '001' from dual

SELECT '002' from dual
SELECT '003' from dual
SELECT '004' from dual
SELECT '005' from dual

...which we now understand as reflecting the fact that the cursors were in fact only soft-closed.

The source for the program, Open_Close_5_Different was in fact generated by running the program Generate_Open_Many_Cursors thus...

begin Generate_Open_Many_Cursors (

  p_number=>5, p_close=>true, p_same_sql=>false ); end;

...(more on p_close and p_same_sql presently. Now find out the limit on the number of cursors your session can open...

select value from v$parameter
  where name='open_cursors'

...and build a program to excede that limit. (If you're the DBA, set the limit low to make this experiment less verbose.) For example, if your open_cursors is 300, then build Open_Close_305_Different by invoking Generate_Open_Many_Cursors with p_number=>305. Check it's what you expect thus...

select text from user_source
  where name = Upper('Open_Close_305_Different') order by line

...reconnect, and execute it. Observe open_cursor_sql_text from another session. You should see that the PL/SQL cursor cache is full, that the last few cache slots correspond to the cursors from the last few open statements and that the slots corresponding to the cursors from the first few open statements have been re-used LRU style.

Now rebuild your program using p_close=>false which will generate for example Open_305_Different. Execute it. You'll get ORA-01000: maximum open cursors exceeded. Observe open_cursor_sql_text again. You should see that the PL/SQL cursor cache is full again, but that the slots show cursors corresponding the first consequetive open statements until the limit is hit.

These two tests Open_Close_305_Different and Open_305_Different clearly show the effect of the PL/SQL close statement as a soft-close.

You should always close a PL/SQL static SQL cursor immediately after you have finished fetching from it.

There is never any reason for not following this guideline.

Note: If you program badly and omit the close statement corresponding to a given open then the PL/SQL runtime system will generally rescue you by doing the close for you as soon as the identifier for the cursor goes out of scope. (The close will be either soft of hard depending on the nature of the cursor. More on this later.) However, there are certain circumstances where this does not happen (reflecting a trade-off in the design of the PL/SQL runtime system between robustness against badly written code and speed of execution). Oracle Corporation does not document these subtle rules (they might change with successive versions).

If you insist on knowing what's not good for you to know(!), at Oracle9i, a cursor for which you omit the close statement will generally be closed when the block in which it was declared goes out of scope provided that this block is either named (a function or procedure) or is the top-level anonymous block invoked by the client. This rescue is deferred for a nested anonymous block until the enclosing named block or top-level anonymous block is exited.