Posted by: akolk | February 5, 2009

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🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: