RE: Fun with WAIT Event "library cache: mutex X"
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <gajav@xxxxxxxxx>, "'Grzegorz Goryszewski'" <grzegorzof@xxxxxxxxxx>
- Date: Thu, 8 Dec 2011 16:05:36 -0500
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.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Gaja Krishna Vaidyanatha
Sent: Thursday, December 08, 2011 1:58 PM
To: Grzegorz Goryszewski
Cc: ChrisDavid.Taylor@xxxxxxxxxxxxxxx; 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxx>
To: gajav@xxxxxxxxx
Cc: "ChrisDavid.Taylor@xxxxxxxxxxxxxxx" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>;
"'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
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
*
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
