It should be clear now that building a cursor is a relatively expensive operation. The CPU consumption and possible IO to bring objects into the library cache can significantly slow performance. This typically manifests into increased parsing CPU consumption and, in particular, the library cache latch or mutex becoming the top wait event. So one obvious objective is to keep cursors in the library cache. However, a balance must be maintained, or other performance-inhibiting issues will arise. The shared pool must contain many types of objects, and library cache objects are just one of those types. Plus memory is a limited resource. The following subsections discuss a variety of ways to influence Oracle to keep cursors in the cache.

Oracle cannot deallocate open cursors. Even if the shared pool is flushed, open cursors are pinned and therefore cannot be deallocated. Normally, when cursor execution is complete, the cursor is closed, the cursor pin is removed, and if there are no other sessions pinning the cursor, Oracle can deallocate the associated memory. This allows for newer and active cursors to remain in memory while less active cursors are naturally deallocated. But if parsing becomes a significant performance problem, as performance analysts, we become motivated to influence Oracle to keep the cursors in memory. One of the ways to do this is keep the cursors open.

Oracle does allow us to keep cursors open longer than usual. When set to true (the default is false), the instance parameter cursor_space_for_time keeps all cursors pinned until they are specifically closed. Even after cursor execution has completed, Oracle will keep the cursor pinned, and it will stay that way until the cursor is closed.