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.

Personally, I do not enable this option unless there is clearly a parsing problem, identified by at least two of three situations: CPU consumption dominated by parse time and either shared pool latch contention or library cache latch or mutex contention. Conversely, if there are "out of shared pool memory" errors occurring, be sure to check that cursor_space_for_time is set to false.