Shared Read latches appeared in 9i (although I have heard that they may been used in a few cases in 8i). There are some statistics relating to their use: shared hash latch upgrades - no wait shared hash latch upgrades - wait
Check Tanel Poder's website for some information on the technology.
multiblock read objects 502,619 472 51which means you are doing a relatively large amount of
As others have mentioned, there is a (9i) view that reports segment level work and waits: v$segment_statistics if you want to use the friendly but expensive version, v$segstat if you want to use the cheap version. Depending on version, this can tell you which object is subject to most buffer busy waits, and most segment scans.
Regards
Jonathan Lewis http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Date: Mon, 24 Apr 2006 14:39:23 +0100 From: "Stephen Anderson" <st.anderson@xxxxxxxxx> 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.
-- //www.freelists.org/webpage/oracle-l