Re: v$latch_misses statistics

From: Amir Hameed <Amir.Hameed_at_usa.xerox.com>
Date: 7 Apr 2003 13:16:39 -0700
Message-ID: <334de71f.0304071216.4cb4b52d@posting.google.com>

Ricky and Yong,
Thank you both for providing useful information in response to my query. Here are a few things that I have been thinking and would appreciate your thoughts on:
-- using session_cached_cursor parameter. In 11i database, this is set to "zero" by default and an Oracle document was suggesting a value of 200. I am glad that Ricky pointed out a value of 100 and some constraints related to it due to the Oracle release (we are at 8.1.7.4, 64-bit). The reason I was thinking of setting this parameter was because as I was sampling the following statistics from v$sesstat view, I was seeing high value for the "opened cursors cumulative" and "parse count (total)" statistics for some sessions. Following are statistics from one such session:

          Statistic Name                   Value
        -------------------------------    ------


        * opened cursors cumulative        5,137

        * opened cursors current             100

        * parse time cpu                     102

        * parse time elapsed                 100

        * parse count (total)              5,139

        * parse count (hard)                  46

        * session cursor cache hits            0

        * session cursor cache count           0

High values for soft parses (parse count total - parse count hard) made me think that these sessions are either opening too many cursors or opening cursors too many times and by setting this parameter, I may alleviate some load from the library cache that these soft parses are putting.

• setting _kgl_bucket_count parameter: When I dump the library cache at level 2, I see the "LIBRARY CACHE HASH TABLE: size=32762" which suggests that the library cache hash table expanded sometime after instance startup and simply doubled its size and did not maintain the prime number count (in this case 32749). From what I have learned from Steve Adams and some other sources that the objects in library cache are evenly distributed if the value of this parameter is a prime number. Also, because for a KGL object, the child library cache latch is computed by using formula: mod(mod(kglnahsh, #hash-buckets), #kgl-latches) ; which suggests that it is a function of both "hash buckets" and "latch count", I was thinking about setting the "_kgl_bucket_count" to the a value of "7" (65521 hash buckets) and then see that if by re-hashing objects alleviates load from some very hot latches.

Do you support my theory ?

thank you
Amir Received on Mon Apr 07 2003 - 15:16:39 CDT