Oracle PL/SQL for DBAs

Soft-Closing Cursors

When I am building applications, I am a firm believer in extending assumptions in order to squeeze out every little bit of extra performance or throughput. I’m forever asking questions—for example, if the same Oracle session executes my PL/SQL function repeatedly, why don’t I cache some of the setup information instead of querying it each time? Or, if I know that underlying data changes only hourly, why can’t I cache the result sets and avoid requerying the database until 60 minutes pass?

One assumption that Oracle itself extends is that once a session uses a cursor, it will eventually reuse it—even if it is explicitly closed. The trickery used to accomplish this feat is known as soft closing, or what I refer to as “the close that’s not a close.”

Consider this simple example of an implicit cursor.

    SQL> SELECT NULL
      2    FROM DUAL;

    N
    -

    1 row selected

The implicit cursor was created, opened, fetched, and closed—all in the SELECT statement. So now it should be disassociated with the session that executed it, right? Not so fast. In order to take better advantage of potential reuse, the cursor is soft-closed only within the session to make subsequent reuse by that session faster.

The cursors associated with a particular session are shown in the V$OPEN_CURSOR view . That view includes any cursors currently open as well as those that have been soft-closed. Here’s what the view shows for the session querying the DUAL table.

    SQL> SELECT sql_text
      2    FROM v$open_cursor
      3   WHERE ...