Oracle has an incredibly difficult challenge in managing shared pool memory. This is rather obvious by all the changes, bugs, patches, and various performance problems over the years. While that may invoke some feelings of sympathy, when faced squarely with a nasty memory management-related issue, sympathy quickly turns to anger. In this section, I will explain how the shared pool memory is managed, its management progression over the years, how memory is allocated and deallocated, how to deal with the feared 4031 error, and finally how to resolve shared pool latch contention.

In Oracle 7 and Oracle8i, shared pool memory management was performed with the help of an interesting hashing structure. If you recall our discussions on the cache buffer hash chains and the library cache hash chains, then this will make perfect sense, but there was a twist.

When a process needed memory in the shared pool, its resulting hash bucket and chain were related to the memory size required. The chains are commonly called a heap, which is a linked list of available memory chunks. So conceptually, the first few chains were related to around 1KB chunks of memory, the next few chains were related to around 2KB chunks of memory, and so forth. While this was indeed ingenious, after a while of allocating and deallocating memory of nonuniform sizes, the chains could become literally a few thousand nodes long. Keep in mind the hash buffer chain size averages between zero and one. So a chain of a couple thousand nodes is massive. And to make matters worse, there was only one single shared pool latch to cover all the hash chains! Flushing the shared pool helped quite a bit because the chains would be reduced to a respectable size. But that was no way to operate a large production database, so Oracle had to make a change.