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

The Burleson article and the Metalink note are both seriously defective.

"Cache buffers chains" latch activity is a natural consequence of accessing data buffers. Contention for "cache buffers chains" latches is more likely in a system with a high degree of concurrency where lots of small jobs are visiting the same (relatively small) number of data blocks.

The biggest problem with the Burleson article is that he seems to be confusing latch contention with buffer busy waits.

The biggest problem with the Metalink article is that it supplies a ridiculous query
to report the "problem object" from the child latch address. Do NOT run that query on a real production system. See the following note that comments on the problem:
 http://www.jlcomp.demon.co.uk/kiddy_scripts.html#_Sometimes_the_best_thing_to_do_is_tIf you want an efficient query to get the relevant information in 10.2, then allyou need isa query like: select obj, tch from sys.x$bh where hladdr = '6F9CBE00' -- adjust as necessary ;The OBJ is the data_object_id of the object (or dataobj# from obj$,since you're prepared to poke around at the level of the x$).Another thing to consider is that the TCH can be relatively high for anobject that is not subject to much access. The TCH is only increased atmost once every three seconds. Take a look at the following OBJ TCH---------- ---------- 51813 1 50303 10 2 13 41383 1 2 13 9006 1 9076 1 2 13 237 438It's Object 237 has been touched at least once every threeseconds for the last 22 minutes (in fact it's job$). Object50303 has been   hammered to death for the last 30 seconds.You have to temper the touch count with knowledge aboutwhat MIGHT have been happening to the objects.To help you address the problem, you may get more cluesfrom a simple statspack/awr report - find the SQL that doesmost gets, and check the segment statistics for the objectssubject to most buffer gets - cross reference to see if thetwo sets of information are consistent, then see if youcan reduce the work done by those statements.RegardsJonathan Lewishttp://jonathanlewis.wordpress.comAuthor: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.htmlThe Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html----- Original Message -----From: "Brian MacLean" <bpmaclean.oracle@gmail.com>To: <VIVEK_SHARMA@infosys.com>Cc: <oracle-l@freelists.org>Sent: Friday, February 15, 2008 3:09 PMSubject: Re: "latch: cache buffers chains" wait in NON-RAC Benchmark Runs> See>> http://www.dba-oracle.com/t_hig  h_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