Re: Latch / Wait Events
Date: Fri, 13 Nov 2009 07:12:11 -0800 (PST)
Message-ID: <b6c0bf79-914b-406d-9972-831f3cbb0787_at_h34g2000yqm.googlegroups.com>
On Nov 12, 10:11 am, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> I've been reading some documentation on latches to try and see if our
> database can be improved. While the concept is rather simple, I
> cannot really find information on which latches are really important
> and what numbers may indicate a problem.
>
> We have many, many events "rdbms ipc message". Some values are:
>
> Total Waits: 27674285
> Timeouts: 535295
> Time Waited: 164032311
>
> If this bad? Why are the so many of these events?
>
> Here is another one related to redo logs:
>
> log file parallel write
> Total Waits: 27705118
> Total Timeouts: 0
> Time Waited: 983231
>
> This query has been posted everywhere. I've run it for 3 days and the
> same addresses appear at the top:
>
> select CHILD# "cCHILD"
> , ADDR "sADDR"
> , GETS "sGETS"
> , MISSES "sMISSES"
> , SLEEPS "sSLEEPS"
> from v$latch_children
> where name = 'cache buffers chains'
> order by 5 desc, 1, 2, 3;
>
> sMisses: 364252
> sSleeps: 8957
>
> This good or bad???
>
> I know that any value for 'cache buffers chains' or 'latch_free' is
> probably not good. But, how to decide what latch wait is bad, what
> value is unacceptable and what can be done?
>
> We are running 10gR2.
>
> Many Thanks
To add to what mgogala said most Oracle wait statistic values have meaning only in relation to other Oracle statistics. If you wait 1,000 times for a resoure is that significant? The answer depends on what the resource is and how many times it was requested. If the total request count was 10,000 then a 10% wait factor is probably an issue worth further research but 10,000 waits against 100,000,000 requests would be a less significant wait percentage so the absolute values of the statistics are not what counts.
One way to help learn which statistics might be significant is to run statspack or if Licensed AWR reports against multiple databases hosting similar applications and compare the reports looking to see if you can spot identical patterns in the relationship between statistics.
HTH -- Mark D Powell -- Received on Fri Nov 13 2009 - 09:12:11 CST