Shared Pool Latch Contention

Recently I was looking at a system that had some shared pool instability. Once a week during the day it would start flushing and loading the objects back in when needed. This resulted in large library cache pin and library cache load lock waits. That problem was attacked with some simple changes. One of the problems was that this customer had changed the reserved size minimal alloc (hidded parameter) from the default 5120 bytes to 51200 bytes. As a result the (large) reserved shared pool wasn’t used.

Another strange problem was that every hour there was a spike in shared pool latch waits. It turned out that an DBA had built to script to check the shared pool (queries against x$ksmsp) and that caused some problems. While querying this view, Oracle needs to hold the shared pool latch. So if there are many small pieces that need to be checked, one can hold on a long time for this latch. When we killed the script, the spikes also dissappeared 🙂