Re: "latch: cache buffers chains" wait in NON-RAC Benchmark Runs

From: Brian MacLean <bpmaclean.oracle_at_gmail.com>
Date: Fri, 15 Feb 2008 08:09:46 -0700
Message-ID: <e9b137d60802150709h3ef9e2fdu3574ff8effc73ed5@mail.gmail.com>


See

http://www.dba-oracle.com/t_high_cache_buffer_chain_waits_contention.htm and the text below is from

http://www.quest-pipelines.com/newsletter-v5/Resolving_Oracle_Latch_Contention.pdf

*

Avoiding Hot Blocks
*

Cache buffers chains latch contention is one of the most intractable types of latch contention.

There are a couple of things you can do at the application level to reduce the severity of this type of contention.

Firstly, identify the blocks that are "hot." Metalink note 163424.1, "How to Identify a Hot

Block Within The Database" describes how to do this. Having identified the identity of the hot block, you will most likely find that it is an index root or branch block. If this is the case, there are two application design changes that may help.

  1. Consider partitioning the table and using local indexes. This might allow you to spread the heat amongst multiple indexes (you will probably want to use a hash partition to ensure an even spread of load amongst the partitions).
  2. Consider converting the table to a hash cluster keyed on the columns of the index. This allows the index to be bypassed completely and may also result in some other performance improvements. However, hash clusters are suitable only for tables of relatively static size, and determining an optimal setting for the SIZE and HASHKEYS storage parameters are essential.

On 2/14/08, VIVEK_SHARMA <VIVEK_SHARMA_at_infosys.com> wrote:
>
>
> Folks
>
> In a Benchmark Run, 512 concurrent BATCH Processes using Dedicated
> Sessions (NON-MTS) showing the following Waits:-
>
> How is the "latch: cache buffers chains" wait to be addressed?
>
> Should "_write_clones" be set to "0"
> Current default value is "3"
>
> Will share the Statspack Report, as needed.
>
> Config:-
> Oracle 10.2.0.3 ( NON-RAC )
> Solaris 10
>
> Cheers
>
> Vivek
>
> Statspack Report :-
>
> Elapsed: 19.23 (mins)
>
> Top 5 Timed Events Avg
> %Total
> ~~~~~~~~~~~~~~~~~~
> wait Call
> Event Waits Time (s)
> (ms) Time
> ----------------------------------------- ------------ ----------- ------
> ------
> CPU
> time 28,932
> 27.2
> latch: cache buffers chains 379,062 18,092
> 48 17.0
> db file scattered read
> 134,021 17,649 132 16.6
> db file sequential read 155,494 13,199
> 85 12.4
> SQL*Net break/reset to
> client 4,314,656 10,430 2 9.8
> -------------------------------------------------------------
> ^LHost CPU (CPUs: 120)
> ~~~~~~~~ Load Average
> Begin End User System Idle WIO
> WCPU
> ------- ------- ------- ------- ------- -------
> --------
> 1.55 6.65
> 40.30 30.85 28.84 0.00 77.42
>
> **************** CAUTION - Disclaimer *****************
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended
> solely for the use of the addressee(s). If you are not the intended
> recipient, please notify the sender by e-mail and delete the original
> message. Further, you are not to copy, disclose, or distribute this e-mail
> or its contents to any other person and any such actions are unlawful. This
> e-mail may contain viruses. Infosys has taken every reasonable precaution to
> minimize this risk, but is not liable for any damage you may sustain as a
> result of any virus in this e-mail. You should carry out your own virus
> checks before opening the e-mail or attachment. Infosys reserves the right
> to monitor and review the content of all messages sent to or from this
> e-mail address. Messages sent to or from this e-mail address may be stored
> on the Infosys e-mail system.
> ***INFOSYS******** End of Disclaimer ********INFOSYS***
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 15 2008 - 09:09:46 CST

Original text of this message