Cursors and Memory

At this point in your PL/SQL programming career, you no doubt have had some exposure to cursors. You may have written hundreds of programs that declare, open, fetch from, and close cursors. Perhaps you've even programmed with cursors at a relatively low level using the DBMS_SQL built-in package. It's impossible to execute SQL or PL/SQL without cursors, and statements often invisibly open recursive or child cursors to perform additional processing. And because every cursor, whether implicit or explicit, requires an allocation of memory on the database server, tuning Oracle often involves reducing the number of cursors required by an application.

Oracle assigns cursors to anonymous PL/SQL blocks in much the same way that it assigns cursors to SQL statements. For example, on the first parse call from a session, Oracle opens an area in PGA memory (the "private SQL area") where it will put things like bind data and other information specific to the run.

It turns out that some of the server-side data structures associated with cursors reside in the UGA, and some in the CGA. For example, because SELECT statements identify rows that need to be available during multiple fetches, Oracle allocates a work area for the cursor from UGA memory; because DML statements complete in a single call, Oracle allocates their work area from CGA memory. Similarly, when executing PL/SQL, Oracle allocates UGA memory to store state information, and uses CGA for other processing.

When executing a SQL statement or a PL/SQL block, the server first looks in the library cache to see if it can find a reusable parsed representation of it. If it does find such a shared PL/SQL area, the runtime engine establishes an association between the private SQL area and the shared SQL area. If no reusable shared area exists, Oracle will "hard parse" the statement or the block. (As an aside, note that Oracle also prepares and caches a simple execution plan for anonymous PL/SQL blocks, which consists of calling the PL/SQL engine to interpret the bytecode.)

Oracle interprets the simplest of PL/SQL blocks—those that call no subprograms and include no embedded SQL statements—using only the memory allocated for its primary cursor. If your PL/SQL program includes SQL or PL/SQL calls, though, Oracle requires additional private SQL areas in the PGA. Called statements execute in these recursive cursors that PL/SQL manages on behalf of your application. Oracle frees the private SQL areas used by the child cursors only after the private SQL area for the parent cursor is freed.

This brings us to another important fact about cursors: there are two ways a cursor can be closed. A soft-closed cursor is one that you can no longer use in your application without reopening it. This is what you get when you close a cursor using a statement such as this one:

CLOSE cursorname;

or even when an implicit cursor closes automatically. However, PL/SQL does not immediately free the session memory associated with this cursor. It assumes that you may actually open it again. You will see, if you look in the V$OPEN_CURSOR view, that the CLOSE alone does not reduce the count of this session's open cursors.

It turns out that PL/SQL maintains its own "session cursor cache;" that is, it decides when to close a cursor for good. This cache can hold a maximum number of cursors, as specified by the OPEN_CURSORS database initialization parameter. Aleast-recently-used (LRU) algorithm determines which of the soft-closed cursors need to be hard-closed and hence deallocated. With good programming practices such as closing your cursors immediately after you are through fetching with them, PL/SQL's approach usually results in a net performance gain.

You should always explicitly close cursors that you open explicitly. If you fail to close the cursors that you open, you may subvert PL/SQL's cursor management algorithm.

There are a few ways that Oracle allows PL/SQL programmers to intervene in the default behavior. One way you can close all of your session cursors, of course, is to terminate the session! Less drastic ways include:

· Reset the package state, as discussed at the end of the later section Section 20.3.3.4.

· Use DBMS_SQL to gain explicit control over low-level cursor behavior. On the whole, though, memory gains provided by this approach may not offset the corresponding performance costs and programming challenges.