RE: Fun with WAIT Event "library cache: mutex X"
Date: Thu, 8 Dec 2011 16:05:36 -0500
Message-ID: <006501ccb5ed$23068c10$6913a430$_at_rsiz.com>
A few points my clients and I have observed:
- 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.]
- 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.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Gaja Krishna Vaidyanatha
Sent: Thursday, December 08, 2011 1:58 PM
To: Grzegorz Goryszewski
Cc: ChrisDavid.Taylor_at_ingrambarge.com; 'oracle-l_at_freelists.org'
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 :)
Cheers,
Gaja
Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com
Phone - +1-650-743-6060
http://www.linkedin.com/in/gajakrishnavaidyanathaCo-author:Oracle
Insights:Tales of the Oak Table
- http://www.apress.com/book/bookDisplay.html?bID14
Co-author:Oracle Performance Tuning 101
- http://www.amazon.com/gp/reader/0072131454/ref=sib_dp_pt/102-6130796-46257
66
From: Grzegorz Goryszewski <grzegorzof_at_interia.pl> To: gajav_at_yahoo.com
Cc: "ChrisDavid.Taylor_at_ingrambarge.com" <ChrisDavid.Taylor_at_ingrambarge.com>; "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org> 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]"
Regards
GregG
*
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 08 2011 - 15:05:36 CST