When a cursor is being executed it is also pinned. After all, you don't want your SQL statement to suddenly disappear during execution! That's the good news. The potentially bad news is when the execution has completed, the pin is released. If no other process has the cursor pinned, Oracle is free to destroy-that is, deallocate-the associated memory. Now suppose someone wants to reexecute the cursor. If it has been deallocated, a hard parse results, since the entire cursor will have be rebuilt! Every application usage pattern is unique; so when combined with a perhaps smaller shared pool or a lot of unique SQL statements, or both, the memory management and the library cache activity can become incredibly intense. One way to reduce hard parsing is to keep the cursors pinned so they cannot be deallocated.

Oracle provides a special instance parameter that will keep all cursors for all sessions pinned until the cursor is closed. But this benefit comes at the cost of increased shared pool memory consumption, and therefore, the increased likelihood of receiving a 4031 error. Oracle is very aware of this, so to encourage deallocation to free up memory and decrease the likelihood of 4031 errors occurring, the cursor_space_for_time instance parameter is set to false by default.

If a system is experiencing 4031 errors, you should always check the value of cursor_space_for_time. If your system has experienced severe shared pool latch contention at some point in the past, someone may have understandably set cursor_space_for_time to true. While you may not decide to set the parameter to false, it is a valid option and should be seriously considered.