Re: Latch / Wait Events

From: Mladen Gogala <no_at_email.here.invalid>
Date: Thu, 12 Nov 2009 15:56:41 +0000 (UTC)
Message-ID: <pan.2009.11.12.15.56.39_at_email.here.invalid>

On Thu, 12 Nov 2009 07:11:50 -0800, The Magnet 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

Do you want to "improve database" or do you want to improve the response time of the applications? If latter is the case, try seeing where the applications are spending time and see how you can reduce this time. The phrase "improve database" doesn't make much sense as the database is just a storage area where your data is stored. You don't improve the performance of the warehouse, you improve the business processes that access the warehouse. The answer to your numbers will be the only correct one: "it depends". How long since you started the instance? What are these latches? What documentation did you read?

-- 
http://mgogala.freehostia.com

Received on Thu Nov 12 2009 - 09:56:41 CST