Cache Buffers chains,存在共享模式？
Latch cache buffers chains大約是Oracle中child latch數量最多，使用最為頻繁的閂鎖了。其子閂總數受到初始化引數(8i中的db_block_buffers/4)的影響，Oracle中有大量kernel函式有機會接手持有cache buffer chains latch:
col parent_name for a25
col location for a40
SELECT t1.ksllasnam "parent_name",
t2.ksllwnam "location"
FROM x$ksllw t2, x$kslwsc t1
WHERE t2.indx = t1.indx
AND ksllasnam = 'cache buffers chains';
parent_name location
------------------------- ----------------------------------------
cache buffers chains kcbw_activate_granule
cache buffers chains kcbw_first_buffer_free
cache buffers chains kcbwxb
cache buffers chains kcbw_examine_granule
cache buffers chains kcbw_next_free
cache buffers chains kcbw_first_buffer_free_2
cache buffers chains kcbbckb
cache buffers chains kcbbioe
cache buffers chains kcbbic1
cache buffers chains kcbbcwd
cache buffers chains kcbbxsv
parent_name location
------------------------- ----------------------------------------
cache buffers chains kcbbwdb
cache buffers chains kcbbic2
cache buffers chains kcbkzs
cache buffers chains kcbrmf2so
cache buffers chains kcbget: exchange rls
cache buffers chains kcbralloc
cache buffers chains kcbgcur: deadlock
cache buffers chains kcbgcur: buf pinned
cache buffers chains kcbgtcr
cache buffers chains kcbchg: no fast path
cache buffers chains kcbchg: apply change
parent_name location
------------------------- ----------------------------------------
cache buffers chains kcbrra: buf exists
cache buffers chains kcbrra: update buf flags
cache buffers chains kcbema: find buf
cache buffers chains kcbtema: find buf
cache buffers chains kcbget: prewarm wait
cache buffers chains kcbrfrebuf
cache buffers chains kcbsod1
cache buffers chains kcbrbrl
cache buffers chains kcbgcur: kslbegin
cache buffers chains kcbgtcr: kslbegin shared
cache buffers chains kcbrls: kslbegin
parent_name location
------------------------- ----------------------------------------
cache buffers chains kcbchg: kslbegin: bufs not pinned
cache buffers chains kcbchg: kslbegin: call CR func
cache buffers chains kcbnlc
cache buffers chains kcbget: exchange
cache buffers chains kcbget: pin buffer
cache buffers chains kcbnew: new latch again
cache buffers chains kcbgkcbcr
cache buffers chains kcbget: in cur_read
cache buffers chains kcbesc: escalate
cache buffers chains kcblbi
cache buffers chains kcbcge
parent_name location
------------------------- ----------------------------------------
cache buffers chains kcbfrl
cache buffers chains kcbzsc
cache buffers chains kcbibr
cache buffers chains kcbnew_1
cache buffers chains kcbema
cache buffers chains kcbsrbd
cache buffers chains kcbso1: set no access
cache buffers chains kcbtema
cache buffers chains kcbso1: in done_clr
cache buffers chains kcbsod2
cache buffers chains kcbzcg
parent_name location
------------------------- ----------------------------------------
cache buffers chains kcbzre1
cache buffers chains kcbrlb1
cache buffers chains kcbchkrsod
cache buffers chains kcbxbh
cache buffers chains kcbzsck
cache buffers chains kcbgtcr: fast path
cache buffers chains kcbgtcr: kslbegin excl
cache buffers chains kcbgtcrf
cache buffers chains kcbfdgd
cache buffers chains kcbdng
cache buffers chains kcbbufaddr2hdr
parent_name location
------------------------- ----------------------------------------
cache buffers chains kcbo_ivd_process
cache buffers chains kcbo_write_process
cache buffers chains kcbo_exam_buf
cache buffers chains kcb_pre_apply: kcbhq61
cache buffers chains kcb_post_apply: kcbhq62
cache buffers chains kcb_post_apply: kcbhq63
cache buffers chains kcbnew : new esc failed
cache buffers chains kcbesc : escalate failed
cache buffers chains kcb_private_owner
cache buffers chains kcb_is_private
cache buffers chains kcb_unprivatize
parent_name location
------------------------- ----------------------------------------
cache buffers chains kcb_restore_block_headers
cache buffers chains kcb_flush_undo_buffers
cache buffers chains kcbgcur - DEADL
cache buffers chains kcbtbd
cache buffers chains kcbzwc
cache buffers chains kcbzwx
cache buffers chains kcbrmflx
cache buffers chains kcbzwb
cache buffers chains kcbzgb: get latch after post
cache buffers chains kcbzgb: scan from tail. nowait
cache buffers chains kcbzgb: exit_loop
parent_name location
------------------------- ----------------------------------------
cache buffers chains kcbzib: multi-block read: nowait
cache buffers chains kcbzib: finish free bufs
cache buffers chains kcbzcb
cache buffers chains kcbzdh
cache buffers chains kcbdpr
cache buffers chains kcbcxx
cache buffers chains kcbzrn
cache buffers chains kcbdpd: for specific dba
cache buffers chains kcbdpd: dump all buffers
cache buffers chains kcbzib: exchange rls
cache buffers chains kcbzpnd: dump buffers
parent_name location
------------------------- ----------------------------------------
cache buffers chains kcbzhngcbk1: get hash chain latch no wai
t
cache buffers chains kcbo_cxx
cache buffers chains kcbz_check_obj_reuse_sanity
cache buffers chains kcbzib_grlk
cache buffers chains kcbz_force_maps
cache buffers chains kcbrldflx: recover in-flux bufs
cache buffers chains kcbra1fbuf: recover in-flux bufs
cache buffers chains kcbrafb: flashback bufs:1
cache buffers chains kcbrafb: flashback bufs:2
parent_name location
------------------------- ----------------------------------------
cache buffers chains kcbr_media_apply: find buffer
cache buffers chains kcbr_issue_read: alloc buffer
cache buffers chains kcbr_issue_read: retry alloc
cache buffers chains kcbr_validate_read: mark corrupt
cache buffers chains kcbr_apply_change: after apply
cache buffers chains kcbr_mapply_change
cache buffers chains kcbr_mrcv_clear_fgda
cache buffers chains kclwlr
cache buffers chains kclebs_1
cache buffers chains kclcls
cache buffers chains kclcsr_1
parent_name location
------------------------- ----------------------------------------
cache buffers chains kclpred
cache buffers chains kclcls_1
cache buffers chains kclple_1
cache buffers chains kclple_2
cache buffers chains kclcls_2
cache buffers chains kcllwr
cache buffers chains kclwcrs
cache buffers chains kclcrs_1
cache buffers chains kclcsr
cache buffers chains kclrls
cache buffers chains kclwcrs_1
parent_name location
------------------------- ----------------------------------------
cache buffers chains kclfbst_1
cache buffers chains kclpdc_1
cache buffers chains kclwcrs_2
cache buffers chains kclwcrs_3
cache buffers chains kclfpdb
cache buffers chains kclfpdb_2
cache buffers chains kclpdc_2
cache buffers chains kcllkopb
cache buffers chains kclgrantlk
cache buffers chains kclwrt
cache buffers chains kcllkopb_1
parent_name location
------------------------- ----------------------------------------
cache buffers chains kclwcrs_4
cache buffers chains kcllkopb_2
cache buffers chains kclcls_4
cache buffers chains kclpred_1
cache buffers chains kclrclr_2
cache buffers chains kclrecbst
cache buffers chains kclgrantlk_1
cache buffers chains kclcls_5
cache buffers chains kclrwrite_1
cache buffers chains kclrwrite_2
cache buffers chains kclcopy
parent_name location
------------------------- ----------------------------------------
cache buffers chains kclswrite
cache buffers chains kclchash
cache buffers chains kclcfusion
cache buffers chains kclfchk_1
cache buffers chains kclcfusion_1
cache buffers chains kclblkdone
cache buffers chains kclcfusion_2
cache buffers chains kclrenounce
cache buffers chains kclbla
cache buffers chains kclpto_1
cache buffers chains kclgrantlk_2
parent_name location
------------------------- ----------------------------------------
cache buffers chains kclcomplete
cache buffers chains kclshrshr
cache buffers chains kclclaim
cache buffers chains kclhngcbk1
cache buffers chains kclblkdone_1
cache buffers chains kclgvlk
cache buffers chains kclblkdone_2
cache buffers chains kclcclaim
cache buffers chains kclrechk_1
cache buffers chains kclrechk_2
cache buffers chains kclbr
parent_name location
------------------------- ----------------------------------------
cache buffers chains kclpto
cache buffers chains kclpdcl
cache buffers chains kclpdc_3
cache buffers chains kclpdc_4
cache buffers chains kclgcr_1
cache buffers chains kclcls_6
cache buffers chains kclevict
cache buffers chains kcldle
cache buffers chains kclrcopy
cache buffers chains kclenter
cache buffers chains kclrbast
parent_name location
------------------------- ----------------------------------------
cache buffers chains kclexpand
cache buffers chains kclcls_3
cache buffers chains kclverify
cache buffers chains kclaffinity
cache buffers chains kclassert
cache buffers chains kclobj
cache buffers chains kclobj_1
cache buffers chains kclobj_2
cache buffers chains kclgclk
cache buffers chains kclwcrs_5
cache buffers chains kclscrs
parent_name location
------------------------- ----------------------------------------
cache buffers chains kclstalepi
cache buffers chains kclstalepi_1
cache buffers chains kclstalepi_2
cache buffers chains kclgvlk_1
cache buffers chains kclgclk_1
cache buffers chains kclgclk_2
cache buffers chains kclcsr_2
cache buffers chains kcbvm
204 rows selected.
我們一般認為Latch結構是Mostly exclusive access的，也就是極少會有共享訪問閂的機會。但Oracle一般對外宣稱讀取資料時服務程式是以共享模式使用cache buffers chains閂，這就造成了許多人誤以為讀讀是不會出現latch: cache buffers chains爭用的。
但是實際上查詢語句大部分情況下仍需要以exclusive模式持有該類子閂(有時會以SHARED模式持有，這取決於讀取時是使用kcbgtcr: kslbegin shared還是kcbgtcr: kslbegin excl；kcbgtcr是Oracle rdbms中重要的獲取一致性讀的函式，其含義為Kernal Cache Buffer GeT Cosistents Read，顯然該函式存在兩種獲取cache buffers chains的方式即kslbegin shared和excl。與之相對應的是kcbgcur: kslbegin，kcbgcur的含義為Kernel Cache Buffer Get Current，該函式用以獲取當前塊以便修改，也就是”寫”；很顯然kcbgcur: kslbegin函式只有以excl排他方式持有child cache buffers chains latch的必要)，原因在於雖然是查詢語句但同樣需要修改buffer header結構，譬如修改tch搶手度、holder list的hash變數us_nxt、us_prv以及waiter list的hash變數wa_prv、wa_nxt等。換而言之讀讀是會引起Latch free:cache buffers chains等待的，而非許多人認為的僅有讀寫、寫寫會導致快取鏈閂爭用。
這個問題我們再用實驗證明一遍:
SQL> drop table maclean;
drop table maclean
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create table maclean tablespace users as select * from dba_objects;
Table created.
SQL> select count(*) from maclean;
COUNT(*)
----------
51944
SQL> /
COUNT(*)
----------
51944
SQL> /
COUNT(*)
----------
51944
SQL> select spid from v$process where addr =(select paddr from v$session where sid=(select distinct sid from v$mystat));
SPID
------------
6023
另開一個命令列視窗:
[maclean@rh2 ~]$ gdb $ORACLE_HOME/bin/oracle 6023
GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-23.el5)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
...
Reading symbols from /s01/10gdb/bin/oracle...(no debugging symbols found)...done.
Attaching to program: /s01/10gdb/bin/oracle, process 6023
Reading symbols from /s01/10gdb/lib/libskgxp10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libskgxp10.so
Reading symbols from /s01/10gdb/lib/libhasgen10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libhasgen10.so
Reading symbols from /s01/10gdb/lib/libskgxn2.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libskgxn2.so
Reading symbols from /s01/10gdb/lib/libocr10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libocr10.so
Reading symbols from /s01/10gdb/lib/libocrb10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libocrb10.so
Reading symbols from /s01/10gdb/lib/libocrutl10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libocrutl10.so
Reading symbols from /s01/10gdb/lib/libjox10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libjox10.so
Reading symbols from /s01/10gdb/lib/libclsra10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libclsra10.so
Reading symbols from /s01/10gdb/lib/libdbcfg10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libdbcfg10.so
Reading symbols from /s01/10gdb/lib/libnnz10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libnnz10.so
Reading symbols from /usr/lib64/libaio.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libaio.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.
[Thread debugging using libthread_db enabled]
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
0x00000039f280d8e0 in __read_nocancel () from /lib64/libpthread.so.0
(gdb) break kcbgtcr
Breakpoint 1 at 0x108c72c
回到原sqlplus視窗再次執行查詢語句,會hang住:
SQL> select count(*) from maclean;
在gdb視窗:
(gdb) break kslfre
Breakpoint 2 at 0x7a133c
Breakpoint 1, 0x000000000108c72c in kcbgtcr ()
(gdb) c
Continuing.
Breakpoint 1, 0x000000000108c72c in kcbgtcr ()
(gdb) c
Continuing.
Breakpoint 1, 0x000000000108c72c in kcbgtcr ()
(gdb) c
Continuing.
Breakpoint 2, 0x00000000007a133c in kslfre ()
(gdb) c
Continuing.
多次continue直到出現kslfre核心函式，開一個新的sqlplus視窗:
SQL> set autotrace on;
SQL> select count(*) from v$latchholder;
COUNT(*)
----------
3
Execution Plan
----------------------------------------------------------
Plan hash value: 1575818826
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | FIXED TABLE FULL| X$KSUPRLAT | 1 | 13 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
v$latchholder的資料來源於X$KSUPRLAT內部檢視，因為v$latchholder不帶mode欄位，所以我們直接觀察X$KSUPRLAT
SQL> select KSUPRLAT "address",KSUPRLNM "name",KSUPRLMD "mode" from X$KSUPRLAT;
address name mode
---------------- ------------------------- ---------------
00000000FCE40040 cache buffers chains EXCLUSIVE
00000000FA696978 simulator lru latch EXCLUSIVE
00000000FA6CDCE0 simulator hash latch EXCLUSIVE
/* 可以看到即便是查詢語句也是以EXCLUSIVE mode持有child cache buffers chains latch */
(gdb) quit
A debugging session is active.
Inferior 1 [process 6235] will be detached.
Quit anyway? (y or n) y
Detaching from program: /s01/10gdb/bin/oracle, process 6235
/* 可以透過以下statistics可以看到以上讀取為純粹的邏輯讀，沒有物理讀取的部分干擾*/
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
719 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-708296/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- cache buffers chains and cache buffers lru chainsAI
- 0821Cache Buffers chains與共享模式疑問4AI模式
- 0330Cache Buffers chains與共享模式疑問AI模式
- cache buffers chains vs cache buffers lru chainAI
- 0330Cache Buffers chains與共享模式疑問2AI模式
- 1104Cache Buffers chains與共享模式疑問3AI模式
- 1104Cache Buffers chains與共享模式疑問4AI模式
- latch: cache buffers chainsAI
- latch:cache buffers chains案例AI
- 用於排查cache buffers chainsAI
- 深入理解latch: cache buffers chainsAI
- Trouble shooting latch: cache buffers chainsAI
- ORACLE等待事件latch: cache buffers chainsOracle事件AI
- 等待事件_cache_buffers_chains_latch事件AI
- latch：cache buffers chains解決步驟AI
- latch：cache buffers chains的優化思路AI優化
- buffer cache實驗6-latch:cache buffers lru chainsAI
- latch：cache buffers chains的最佳化思路AI
- latch: cache buffers chains---AWR實戰分析AI
- 模擬cache buffers chains與library cache pin等待事件AI事件
- 熱點塊競爭和解決--cache buffers chainsAI
- latch: cache buffers chains故障處理總結(轉載)AI
- 解決一例latch:cache buffers chains小記AI
- 一次latch cache buffers chains問題的處理AI
- 處理 latch_cache_buffers_chains等待事件一例AI事件
- buffer busy waits, latch cache buffers chains, read by other session區別AISession
- 使用了索引就一定能避免cache buffers chains爭用嗎索引AI
- latch: cache buffers chains-熱塊的簡單模擬實驗AI
- Bug 3797171 cache buffers chains latch contention increased in 10g-3797171.8AI
- CACHE BUFFER CHAINSAI
- cache buffers lru chainAI
- Oracle Cache Buffer ChainsOracleAI
- Latch: cache buffer chains (%)AI
- cache buffers LRU chain latchAI
- latch free(cache buffers chain)AI
- 【恩墨學院】深入剖析：關於cache buffers chains的經典案例處理詳解?AI
- buffer cache實驗5-latch:cache buffers chainAI
- 等待事件_cache_buffers_lru_chain_latch事件AI