Re: How to isolate root cause of increasing libcache latch waits? (Subtitle: Hoping to upstage Oracle Support)
- From: Martin Berger <martin.a.berger@xxxxxxxxx>
- To: sacrophyte@xxxxxxxxx
- Date: Wed, 14 Oct 2009 20:26:15 +0200
Charles, have you read Tanel Poders http://blog.tanelpoder.com/2009/09/13/kgl-simulator-shared-pool-simulator-and-buffer-cache-simulator-what-are-these/ especially these quotes might match in your env:> As these allocations are dynamic, then when your application suddenly starts making lots of hard parses due newly released code, causing memory shortage, then the simulator can make things even worse as it can make relatively big allocations for itself too!
and > Note that I’m talking about big systems here, ...It's just out of a kind of reflex, every time I hear library latch I check Tanels blog, latchprofX and others are just too helpful. :)
might it help, Martin Am 14.10.2009 um 15:42 schrieb Charles Schultz:
Good day, list, The SymptomsWe have observed a gradual rise in library cache latch waits in our Production database. Oracle Enterprise Manager shows these as small, short-lived "spikes" in the Top Activity Monitor. Our experience is that these spikes generally grow in intensity and duration (impacting more users for a longer period of time). We have observed that turning off OEM and the agent helps to alleviate these latch waits.The SystemOracle EE 10.2.0.2 on Solaris 10. Upgrading to 10.2.0.4 this weekend. Hardware is Sun F15k with 44 CPUs and 96GB of RAM.The SupportIn our first SR with Oracle, the "resolution" was to stop using OEM. This is a very poor response in my opinion, for it does not address the root problem at all; it offers no insight as to what is causing the latch waits in the first place, nor is it a very effective way to treat a problem as it merely assuages the situation, not eliminating it.In our second SR, I have an analyst who wants me to turn on event 10046 level 12. Good and dandy, I would have done that myself if not for the fact that we often get hit with mutex hangs triggered by rowsource execution statistics. Besides, the analyst is simply fishing for something, he has given no indication that he really understands the situation. He has identified several large, cpu- intensive queries; how does cpu-intensive relate to latch waits, especially considering the fact that our box is only about 12% cpu utilized. I admit, perhaps I am especially dense today and he is spot on. Perhaps.The SynopsisSince it is library cache latches that we are talking about, I looked at the shared pool fragmentation. Since fragmentation generally gets worse as time progresses, it is my theory that perhaps the libcache latch requests are taking longer and longer to fulfill as the there are more (and smaller) chunks of memory to search. I am currently running a couple diagnostic scripts (forgot which Metalink note I got them from, but it is a memory diagnostic suite). We know from past experience that flushing the shared pool has a marginal affect; which makes sense, as x$kssmp shows us that a small fraction of the shared pool is flushable. We also know from past experience that if we let the latch waits continue to get worse, the system approaches a critical mass and we have to bounce it. And nobody likes to bounce the Production system. =)So, one big (obvious) caveat. We are upgrading to 10.2.0.4 this weekend which means we will inherit a ton of bug fixes, as well as those wonderful new bugs that come along with it. Unfortunately, the PSU (10.2.0.4.1 bundle) came out too late for our regression tests, but I have scheduled that for future regression tests. If memory fragmentation actually is the root problem, I have my doubts as to whether 10.2.0.4 is actually going to help us or not. If anyone has any insights as to whether or not this might be true, I am all ears.
Other related posts:
- » How to isolate root cause of increasing libcache latch waits? (Subtitle: Hoping to upstage Oracle Support) - Charles Schultz
- » Re: How to isolate root cause of increasing libcache latch waits? (Subtitle: Hoping to upstage Oracle Support) - Rajeev Prabhakar
- » Re: How to isolate root cause of increasing libcache latch waits? (Subtitle: Hoping to upstage Oracle Support) - Tim Gorman
- » Re: How to isolate root cause of increasing libcache latch waits? (Subtitle: Hoping to upstage Oracle Support) - Martin Berger
- » Re: How to isolate root cause of increasing libcache latch waits? (Subtitle: Hoping to upstage Oracle Support) - Tanel Poder