Re: High value for latch free

<Ranko.Mosic_at_gmail.com> wrote in message news:1133449188.473523.111050_at_g47g2000cwa.googlegroups.com...
> Top 5
>
> Event Waits Time (s) % Total Elapsed Time
> latch free 6,788,898 334,102 69.52
> CPU time 145,702 30.32
> library cache lock 15,458 197 0.04
> db file scattered read 26,956 177 0.04
> global cache cr request 61,197 108 0.02
>
> cache buffer handles 161,151 0
> cache buffers chains ############## 2.6
> cache buffers lru chain 53,782 0

> 1) how is it possible latch free has such a high value for wait time
> and elapsed time ?
>
> 2) if you add up wait times in latch breakdown table you come nowhere
> near the figure of 334,102 s in top 5 events
>
> 3) what is the problem here ? ( I also have enqueue showing up in other
> top 5 reports )
>
> Regards, Ranko.
>

You're hammering the cache buffers chains latch to death by the look of it, with at least 1 billion gets in the interval. so it's not too surprising that you end up with an unusual number of sleeps. An average sleep time of 5/100sec is pretty bad, but it's what happens when you get too busy.

On top of the basic contention, it is possible that the machine is sufficiently busy that when a process goes to sleep to wait for a latch, it loses extra time between becoming runnable and running because of the excess activity of other processes on the run queue.

The sum(time_waited) problem could be because there is a known bug in some versions of Oracle where the time waited on "shared read" latches is not recorded properly. Check your statistics for "shared hash latch upgrades (no) wait".

-- 
Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005