RAC Re: CBC Latch

  • From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
  • To: <racdba@xxxxxxxxxxxxx>
  • Date: Tue, 29 Nov 2005 10:05:40 -0800

Ravi,
 
I am by no means a RAC expert, but CBC latch contention is due to
excessive, concurrent LIOs against a set of objects. This condition is a
problem on non-RAC instances, but gets magnified on RAC due to
cross-instance block consistency issues. Most of the time (and I hope I
am not generalizing this too much), this is due to tight loops on
indexed reads. While the application team should tune and reduces LIOs,
can you also parallely look at which SQL is causing this and trace it?
Please be aware that some 'data pattern' issues can be handled outside
the code via the judicious use of histograms. (Look at my paper at OAUG
CP 2005 or SELECT for an example of this - I have had multiple people
come back to let me know that this fixed a number of vexing issues...)
 
Along with moving tables to smaller blocksizes, you might also want to
look at moving *indexes* to smaller blocksizes to spread out 'hot' data
onto multiple blocks. If Indexed reads are the majority of your LIOs,
this might not help as the root/specific branch blocks may still be
'hot'....
 
Regards,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
 
Co-Author: Oracle Database 10g Insider Solutions
http://www.samspublishing.com/title/0672327910
 
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **
 

________________________________

From: racdba-bounce@xxxxxxxxxxxxx [mailto:racdba-bounce@xxxxxxxxxxxxx]
On Behalf Of Ravi_Kulkarni@xxxxxxxx
Sent: Tuesday, November 29, 2005 6:04 AM
To: racdba@xxxxxxxxxxxxx
Subject: RAC CBC Latch



List, 

Any pointers on how to reduce contention on CBC latch ? 
I am seeing sessions waiting on multiple hladdr's too. 
Recreate tables with Hi-pctfree helped but need much more fine-tuning.
(app team has been doing their bit on reducing logical reads)

How to surely be able to tell the precise fix is (from where it is
waited on) (increasing hash buckets, moving tables to smaller blocksizes
)

10.1.0.4 (2Node) / RH 3.0. 


                                                                 NoWait
Waiter 
Latch Name               Where                       Misses     Sleeps
Sleeps 
------------------------ -------------------------- ------- ----------
-------- 
cache buffer handles     kcbzfs                                 0
8        6 
cache buffers chains  kcbgtcr: kslbegin excl           0     91,563
67,822 
cache buffers chains  kcbgtcr: fast path                  0     57,384
76,694 
cache buffers chains  kcbrls: kslbegin                     0     25,944
31,763 
cache buffers chains     kcbchg: kslbegin: bufs not       0        942
35 
cache buffers chains     kcbxbh                                   0
391       32 
cache buffers chains     kcbzgb: scan from tail. no        0        101
0 




Thanks,
Ravi. 

Other related posts: