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.

But as with all tuning changes, there is a trade-off. This instance parameter affects all cursors in the entire Oracle instance. Furthermore, it is not session-specific, and the parameter change requires an instance restart to take effect. The very real implication is much more shared pool memory will now be required to cache library cache objects. In fact, the effect can be so dramatic that the shared pool could effectively run out of memory, resulting in the dreaded 4031, "out of shared pool memory" error. So care must be taken when setting this parameter.