Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: v$latch_misses statistics

Re: v$latch_misses statistics

From: Ricky Sanchez <rsanchez_at_more.net>
Date: Thu, 10 Apr 2003 17:43:57 GMT
Message-ID: <3E95AD64.56065073@more.net>


Amir-

Comments in-line.

Amir Hameed wrote:
>
> -- 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

Since this is a cumulative value, you have to divide by the number of concurrent sessions to get a meaningful value. No matter, you can't really do anything to reduce it anyway.

There is no magic to the value of 100 for session_cached_cursors. You can experiment with different values to find the optimum for your instance. Perhaps start with 100, then 200. If performance is better with 200, maybe try 150. Maybe 50 is idea, who knows? Somewhere there is a point on a curve that is optimal for you.

The problem with the session_cached_cursors structure is that it is a linked list pre-9i. So, the longer it is, the more time it takes to find (or not find) a particular cursor reference. At some point -- maybe 100 or so-- it becomes just as cheap to simply do a soft parse. Largely a function of cpu speed, right? In 9i, that structure becomes an internal hash table and is much faster at large values. So, experiment with care, taking notes, to find the best value for your instance.

>
> -- setting _kgl_bucket_count parameter: When I dump the library cache
> at level 2, I see the "LIBRARY CACHE HASH TABLE: size=32762" which

The mod mod thing will find objects associated with a given hash chain, by way of the child latch. Since there are more buckets than latches, Oracle assigns latches to buckets in a more-or-less round robin fashion. So yes, object distribution is a function of both hash bucket count and latch count. Of course, bucket count is dynamic, while latch count is fixed. Makes diagnostics non-trivial.

If you have a truly hot library cache object, no amount of hash table or latch tuning will make it better. Since the time that your table resized itself, did the problem go away? If not, then messing with the underscore parameter will do you no good. Instead focus on finding the object.

By extending your method, you can do it by finding the "hot" child latch (highest sleeps for a fixed period), then dumping the lib cache at level 2 to find the current bucket count, then use your mod function to find all objects that are on chains protected by that latch. Then you have to dump the lib cache again to make sure the hash table did not resize itself while you were poking around. Big hassle, easy to screw up.

It might be easier to just look at parses and executions by cursor, look for the hottest object -- most parses, in your case -- during some brief test interval. Do something like a CTAS on v$sql to create a small table with cursor hash value, address, parse count at some busy time. Then wait thirty seconds and repeat, creating a new table. Then, run a query that orders by the diff between the two parse count columns, joining on hash and address. You should see some dramatic difference between the top few and the rest. Those are your "hotties".

>
> Do you support my theory ?
>
> thank you
> Amir
Received on Thu Apr 10 2003 - 12:43:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US