In Oracle9i, the Oracle process acquires a subpool latch and will search the subpool up to five times before giving up. Allowing for multiple passes increases the likelihood of finding memory, as the memory situation can change dramatically and quickly. However, after five searches, while holding the respective shared pool latch, if the appropriate memory chunk size cannot be found, Oracle gives up, posts the feared 4031, "out of memory" message, and the session stops processing. As every Oracle DBA knows, this is totally unacceptable behavior in a production system.

In Oracle Database 10g, the Oracle process is more fervent in its memory quest. If after searching five times in the current shared pool the memory is not found, the process moves on to another subpool. This will continue until all the defined subpools have been searched. If, at this point, the memory cannot be found, as before, Oracle gives up, posts the 4031 error message, and stops processing. What Oracle has done in this version is reduce the chances of returning the error message in exchange for the possibility of consuming more CPU and holding a shared pool latch longer. From a database operational perspective, slower performance is better than no performance. At least work can be performed while we resolve the performance problem.

When memory is in short supply, Oracle will deallocate unpopular chunks of memory. You probably have experienced this when attempting to retrieve a SQL statement's text and it is no longer cached in the shared pool. Fortunately, Oracle will not deallocate memory that is in use. For example, if a cursor is pinned, Oracle will not deallocate the associated memory, regardless of how unpopular it may be. In fact, even flushing the shared pool will not remove pinned cursors! If you really want to empty the shared pool and start from the beginning, you must recycle the instance.