By carefully adjusting these parameters, the performance analyst can increase the likelihood of a process finding a large amount of memory, while still maintaining plenty of memory of the relatively smaller objects. A report based on v$db_object_cache like the one shown earlier in Figure 7-14 can be very helpful to determine both the threshold size and the reserved memory size. While these parameters are typically not adjusted, if 4031 errors occur, their careful adjustment may fix the problem.

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.