Re: v$latch_misses statistics

Amir-

A couple of quick notes and corrections to other replies

KGL is kernel generic library, the library cache code layer

There was a bug under 816-something that involved the longop freelist latch, fixed in 902. I think there might be a patch for 817, check bug 1847782.

The kglhdgn miss source is pretty mundane. It is just a normal "lookup" resulting from a parse call. If this is a serious source of contention in your instance, look at the application code and see if it is repeatedly parsing cursors and then closing them after each usage. A better technique is to leave the cursor open and simply reexecute as needed, closing only when the application is done with it.

The "heat" you see on one particular child latch inevitably means you have a single "hot" object in the cache, rather than generalized contention among many cursors. Look at the lib cache objects for an object with an extremely high parse count. You might compare parses to executes and find a clue as to what part of your app code you need to fix.

If you cannot fix the app code, you might have some success by setting session_cached_cursors to maybe 50. In 8i, it's best not to set it over 100, since it incurs too much overhead itself at that point. It just lets you reopen a cursor without doing the lib cache lookup and thereby avoids the latch get, which ought to reduce sleeps dramatically.

I would not worry about the NWFAIL_COUNT thing. That only applies to situations where a latch is requested in "no wait" fashion and fails. After that failure we simply get it with a "willing to wait" call which can result in sleeps. In the case of the libary cache, we are inevitably willing to wait. I am not sure if we ever request a lib cache latch in nowait mode. In any case, nowait misses are never of any consequence in the bigger picture of instance tuning.

• ricky

Amir Hameed wrote:
>
> Hi folks,
> I am trying to understand the statistics in this view. I am
> particularly interested in the statistics related to "libarary cache"
> and "longop free list" latches. What's happening is that at a
> particular interval of time, the database is showing high values for
> the following:
>
> SLEEP WAITER LONG
> SLEEP SLEEP HOLD
> PARENT NAME WHERE COUNT COUNT
> COUNT
> -------------- --------------------------------- ------- -------
> -------
> library cache kglget: child: KGLDSBRD 20 114
> 14
> library cache kglhdgc: child: 48 82
> 18
> library cache kglhdgn: child: 1,156 17,809
> 615
> library cache kglidp: parent 451 2
> 411
> library cache kgllkdl: child: cleanup 1,655 119
> 1,479
> library cache kglpnal: child: alloc space 400 643
> 194
> library cache kglpnal: child: before processing 58 762
> 28
> library cache kglpnal: parent held, no purge 3,006 3
> 1,904
> library cache kglpnc: child 50 24,449
> 24
> library cache kglpndl: parent: purge 47,568 232
> 45,818
> library cache kglupc: child 30 3,635
> 19
> longop free list ksuloget 2,370 2,371
> 83
>
> The "NWFAIL_COUNT" statistic for these latches is "0". Could anyone
> please help me understand how to interpret these so that I can get to
> the root cause. This may be internal Oracle information as I did not
> find anything on Metalink, ixora, hotsos, jlcomp, etc. When this
> happens, one particular child library cache latch (#6) reports the
> highest number of sleeps.
>
> regards
> Amir
Received on Fri Apr 04 2003 - 09:14:14 CST