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.

Most DBAs know one way to ensure large packages are successfully loaded into the shared pool is use the dbms_shared_pool.keep procedure. When key packages are loaded into memory immediately after the instance starts, your chances of receiving an "out of shared pool memory" error are significantly reduced. In earlier versions of Oracle, especially Oracle8i, this could dramatically reduce the likelihood of running out of shared pool memory.