RE: Fun with WAIT Event "library cache: mutex X"

From: Mark W. Farnham <>
Date: Thu, 8 Dec 2011 16:05:36 -0500
Message-ID: <006501ccb5ed$23068c10$6913a430$>

A few points my clients and I have observed:
  1. AMM makes pretty good recommendations, but when it is near perfect it tends to oscillate a few granules back and forth between shared pool and the buffer cache if either is at all lean. Once you've run it on a variety of your workloads, it can be very stabilizing if you can spare the memory on the system to bump both to at least the ceiling of the recommendations and then turn it off. [Perhaps turning it back on once in a while under, say, 80% of peak pressure to see whether the recommendations have drifted.]
  2. If you turn AMM off, but anything is delaying clearing out long unused sql and new sql's need more space to avoid 4031 errors and the like, Oracle will still steal from the buffer cache to avoid the 4031 errors to some extent. I cannot completely enumerate which cases drive what yet, but I've seen server side automatic result cache leave thousands of duplicate (all but one marked invalid) result set queries stranded so they won't purge as one particularly reproducible case. (Which unfortunately the client would not spend the energy and cost on to file a SR/bug, because the ready solution was to turn off server side automatic result cache. So I've no idea whether this has been fixed or even whether Oracle has a good case in hand.)

If there are other reasons (than error avoidance) that granule reallocation can take place when it is supposed to be turned off I'm not aware of them (but I can't enumerate the cases where it seems to "fight back" to prevent errors, either.)

I agree with Gaja that we should have settings for how tightly this is restricted. My suggestions would be:

Never shift granules (which might force a shutdown in imaginable cases in the event space cannot be found in which to perform a critical system recursive sql.)
Allow for system panic only (which might still allow throwing some 4031s and the like, but would override if the alternative was a crash) What it currently does (which is steal a granule or four granules or so from the buffer cache if it needs parsing space to avoid an error)

For getting routine systems up and running at minimum cost and with minimal capacity planning investment, all these automation tools really do speed up the deployment, reduce total cost of ownership, and mitigate the need to understand performance details.

They can even provide good guidance in quite challenging systems. But at the margin, after you get the value from them, you often need to size things and turn off the automation, even if you need to schedule manual reallocations at ebbs of activity between workshifts of load that demand different settings.



-----Original Message-----
From: [] On Behalf Of Gaja Krishna Vaidyanatha
Sent: Thursday, December 08, 2011 1:58 PM To: Grzegorz Goryszewski
Cc:; '' Subject: Re: Fun with WAIT Event "library cache: mutex X"

Hi Greg,
I am tempted to term the "fight back" as an "undocumented feature" :) In that case, we absolutely need to find a way to disable it -- call this our fight back :)


Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC

Phone - +1-650-743-6060 Insights:Tales of the Oak Table Co-author:Oracle Performance Tuning 101 66

 From: Grzegorz Goryszewski <> To:
Cc: "" <>; "''" <> Sent: Thursday, December 8, 2011 10:49 AM Subject: Re: Fun with WAIT Event "library cache: mutex X"  

On 2011-12-08 19:24, Gaja Krishna Vaidyanatha wrote:
> But, I am still old school when it comes to buffer cache sizing, shared
pool & other pools sizing. I believe it should be part of a DBA's job and should be automated ONLY in applications/databases that demonstrate consistent, predictable and static behavior in workloads. For more dynamic and inconsistent workloads, I personally would go with manual memory settings. It is worthwhile incurring the additional cost of allocating more memory for the required structures and gain stability and consistency in return. Plus, the CPU overhead of MMAN (if and where relevant) can be avoided.

Please consider, despite of disabling AMM Oracle still fights back via: *"SGA Re-Sizes <javascript:;> Occurring Despite AMM/ASMM Being Disabled (MEMORY_TARGET <javascript:;>/SGA_TARGET <javascript:;>=0) [ID 1269139.1]"




Received on Thu Dec 08 2011 - 15:05:36 CST

Original text of this message