RE: Cache buffer chains (kslbegin)
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <st.anderson@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 24 Apr 2006 10:37:10 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Stephen Anderson
Sent: Monday, April 24, 2006 9:39 AM
To: oracle-l@xxxxxxxxxxxxx
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.
Other related posts:
