Re: How to isolate root cause of increasing libcache latch waits? (Subtitle: Hoping to upstage Oracle Support)

  • From: Rajeev Prabhakar <rprabha01@xxxxxxxxx>
  • To: "sacrophyte@xxxxxxxxx" <sacrophyte@xxxxxxxxx>
  • Date: Wed, 14 Oct 2009 10:21:12 -0400

What does the library cache dump show?

Oradebug dump library_cache level ( 1 thru 4)

On Oct 14, 2009, at 9:42 AM, Charles Schultz <sacrophyte@xxxxxxxxx> wrote:

Good day, list,

The Symptoms
We 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 System
Oracle 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 Support
In 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 Synopsis
Since 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.

--
Charles Schultz

Other related posts: