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.

The shared pool latches are used to serialize shared pool memory management. This means operations such as searching for memory, LRU activity, allocating memory, and deallocating memory require a shared pool latch. Because multiple subpools exist starting in Oracle9i, and each subpool has it own shared pool latch, simply running this version or later greatly reduces the likelihood of shared pool latch contention. But sometimes that is still not enough. The following are some possible solutions that will decrease latch acquisition time, latch hold time, or both.