Re: v$latch_misses statistics

Amir.Hameed_at_usa.xerox.com (Amir Hameed) wrote in message news:<334de71f.0303250826.35cdf776_at_posting.google.com>...
> 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.

Hi, Amir,

v$latch_misses is one of the most difficult views in Oracle because we don't have access to the source code. In your case, "kglhdgn: child:" has a high sleep count. It probably means too many processes want to get library cache handle names waiting on library cache child latches. Check to see if you have too many synonyms particularly public synonyms. (I know Apps does, and you can't do anything about it!). kgllkdl means kernel generic layer, lock deallocation (or delete). kglpnal is pin allocation and kglpndl is pin deallocation. Other names are: hdgc for handle get call, pnc for pin call, upc for unpin call. The longop free list latch is acquired when your code calls dbms_application_info.set_session_longops.

NWFAIL_COUNT column always showing 0 is a bug. I think Steve Adams' book or online note mentions it.

One child latch experiencing unusually high sleeps is not uncommon. There's not much you can do to evenly distribute them (even bumping up _kgl_latch_count won't help). If there's not too much code, which is not the case for Oracle Apps, you can dump the library cache and see what SQLs are handled by this child latch and slightly modify some of them so they'll be handled by other latches.

Yong Huang Received on Fri Mar 28 2003 - 12:00:14 CST