If you review Table 7-1, you'll notice the key to solving mutex-related contention is understanding both the wait event and what is occurring within your application. For example, if the wait event is cursor: pin S (which is the most likely), it's possible that the same cursor is being repeatedly executed by a few users, a few cursors are being executed by many users, or even one simple SQL statement is being executed by hundreds of users concurrently. Once you understand this, you'll then look for the SQL statement with a relatively high execution rate and do anything you can to reduce its execution rate. Using the wait event to lead you to the particular cursor-related situation and understanding the nature of your application is your best solution path.

And again, it is very unlikely mutex waits will be the top wait event (when there is not a related mutex bug), but it occasionally does occur. So it's important to understand mutex serialization control (see Chapter 3) as well as library cache internals and diagnosis.

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.