RE: Cache buffer chains (kslbegin)

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Mon, 24 Apr 2006 10:37:10 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF2707A5D198@AABO-EXCHANGE02.bos.il.pqe>

Stephen,

You say you saw "buffer busy waits", right? If so, I think there is a bit of a misunderstanding here. The "cache buffers chains" is a latch. Waits on these (or any) latch would be reported under the "latch free" wait event. If your big hitting wait event is "buffer busy wait", then you need to look at the class of block being waited on, and the "reason code". Statspack is not the best tool for this type of investigation. See V$WAITSTAT to see how many waits have accumulated against different block types, but, beware, these are cumulative stats since the instance started. Best is if you can catch it in action. Look at V$SESSION_WAIT, for event 'buffer busy wait'. When you see that wait event, look at column P3, which is the reason code. Most common is 130, which is "buffer is locked because it's being read by another session". Most common causes are concurrent full table scans and concurrent usage of a very inefficient index. There are also other reason codes, which have different interpretations and diagnoses. See MetaLink Doc ID 34405.1 for more info.

Hope that helps,

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephen Anderson Sent: Monday, April 24, 2006 9:39 AM
To: oracle-l_at_freelists.org
Subject: Cache buffer chains (kslbegin)

Hello,
I have a 10 minute statspack report from a busy time period, and have seen that buffer busy waits were the highest wait events. Would I be correct to then look at the Latch Miss Source at the largest sleeps? I have the following information there:

Latch Sleep breakdown for DB: MERLIN Instance: MERLIN Snaps: 244 -245
-> ordered by misses desc

                                      Get
Spin & 
Latch Name                       Requests      Misses      Sleeps Sleeps
1->4


-------------------------- -------------- ----------- -----------

------------

cache buffers chains          156,103,492      15,773       4,067
0/0/0/0/0 
simulator hash latch            5,788,739         604          25
579/25/0/0/0
multiblock read objects           502,619         472          51
421/51/0/0/0
library cache                     486,962         409          44
365/44/0/0/0 
          -------------------------------------------------------------

Latch Miss Sources for DB: MERLIN Instance: MERLIN Snaps: 244 -245
-> only latches with sleeps are shown
-> ordered by name, sleeps desc

                                                     NoWait
Waiter
Latch Name               Where                       Misses     Sleeps

Sleeps
------------------------ -------------------------- ------- ----------

cache buffers chains kcbgtcr: kslbegin excl 0 2,439 3,749

cache buffers chains     kcbrls: kslbegin                 0        973
58
cache buffers chains     kcbzib: multi-block read:        0        308
0 
cache buffers chains     kcbzgb: scan from tail. no       0        199

0
....

Can anyone confirm what "kslbegin excl" is? I found something listing kslbegin as a "macro to lock a latch", but didn't realise you could lock a latch in anything other than exclusive. With high sleeps on "kslbegin excl", where would be the best place to start digging into what caused these cache buffer chain sleeps?

Many thanks,
Steve.

--

http://www.freelists.org/webpage/oracle-l Received on Mon Apr 24 2006 - 09:37:10 CDT