Re: latch contention

The total number of misses is very small compared to the total number of gets - however, the sleeps are very high compared to misses, so when you miss you miss it a lot. (Can I assume that the spin_gets on this latch was very low, can I also assume that you haven't reduced the _spin_count parameter ?)

Lost time is about 24 hours since database startup - I forgot to say that we needed to know how long the database had been up to gauge whether this was highly significant - and how many CPUs it was spread across.

Initially I would suspect a very busy block as the cause of the problem. Possibly even a couple that happen to be on the same latch. Have a look at v$latch_children (for cache buffers chains) to see if there are one or two latches with an individual high sleep count (start with >150000 as a guess).

If there are one or two anomalous latches do:

    select obj, dbarfil, dbablk from x$bh     where hladdr = {address of child latch}

This gives you the data_object_id, and file/block of the blocks possibly causing the problem. This may give you some clues.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html






Alexey Ogol wrote in message <9hc7l7$2l7h$1_at_pandora.alkar.net>...


>> Which version of Oracle ?

 8.1.7


>>

>> How much contention ? Check v$system_event

>> for the event 'latch free' to see how much time has

>> been lost to latch contention since the database

>> started up (assuming timed_statistics=true).

>select total_timeouts, time_waited from v$system_event where event='latch

>free'

>Output:

>760523 | 8836738

>

>>

>> Also, show us the v$latch row for that latch.

>select name, gets, misses, sleeps, waits_holding_latch from v$latch where

>latch#=66

>Returns:

>cache buffers chains | 18983360432 | 153228 | 753151 | 1091

>

>