cache buffer chain latch只讀共享?
應該說cache buffer chain latch可以以只讀模式有條件的共享:單獨從查詢來說,不會產生任何的lock,enqueue,對chain的結構也不會破壞,所以說可以共享。但是資料庫會預設3秒鐘記錄一下buffer的訪問情況,即更新tch,而這個資訊記錄在buffer header上,而chain又是由buffer header串起來的,所以更新時,需要有latch,最後是我的驗證。
參考:http://space.itpub.net/?uid-22034023-action-viewspace-itemid-682438
當多個程式請求同一類別的同一個latch時,是需要發生等待的,同一類別的同一latch是互斥的,即都是x模式的。只有cache buffer chain latch比較特殊,ORACLE宣稱,9I以後這種latch如果只是只讀操作,可以被共享。
可是我們實驗發現(實驗放在最後面,有需要的看下),只讀也會產生cache buffer chain latch。這是怎麼回事呢?
我們知道cache buffer chain latch保護的是cache buffer chain,這個cache buffer chain上串了同一hash值的buffer header。
當我們需要讀取一個資料塊的時候,ORACLE首先需要獲得cache buffer chain latch,然後在cache buffer chain上找header,找到header後,需要獲得 buffer lock,然後就可以讀資料塊了。大概就是這麼一個過程。
既然只讀是可以共享的,那為什麼還能產生cache buffer chain latch。
問題就出在了找到header後,程式訪問buffer的時候,需要修改header的資訊。這個時候需要以x模式獲得cache buffer chain latch。因此導致瞭如果有其他程式有讀取請求,也會產生cache buffer chain latch等待。
修改的是header的什麼資訊呢?
首先看下header上都有哪些資訊。
BH (0xa5f72538) file#:9 rdba: 0x02400e81 (9/3713) class: 1 ba: 0xa514c000
set: 10 bsz: 8192 bsi: 0 sflg: 0 pwc: 0, 25 lid: 0x00000000,0x00000000
dbwrid: 0 obj: 12224 objn: 12224 tsn: 5 afn: 9
hash: [0x9ecd8740,0x9ecd8740] lru: [0xa9070438,0xa5fa1c98]
ckptq: [NULL] fileq: [NULL] objq: [0x681f5218,0x681f5218]
use: [0xb30892b8,0xb30892b8] wait: [NULL]
st: XCURRENT md: SHR tch: 3
flags:
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
cr pin refcnt: 0 sh pin refcnt: 0
重點關注一下use: [0xb30892b8,0xb30892b8] wait: [NULL]
st: XCURRENT md: SHR tch: 3,use是當前使用者列表,wait是當前等待著列表,MD是mode的意思,可以取的值有excl,代表獨佔模式,如dml操作。SHR代表shared模式,如select操作。TCH是touch的意思,是lru演算法的核心內容。
當獲取buffer lock的時候,需要短暫的以x模式獲得cache buffer chain latch,去修改buffer header上的一些metadata資訊。同樣的,釋放buffer lock的瞬間,也需要以x模式,短暫的獲得cache buffer chain latch。
以下主要測試tch:
SQL> create table testchn as select * from dba_objects where rownum <100;
Table created.
SQL> create unique index idx_object_id on testchn(object_id);
Index created.
SQL> select f,b from (
2 select dbms_rowid.rowid_relative_fno(rowid) f,
3 dbms_rowid.rowid_block_number(rowid) b
4 from testchn) group by f,b order by b;
F B
---------- ----------
1 52514
1 52515
SQL> select file#,dbablk,tch from x$bh where bj=
2 (select data_object_id from dba_objects
3 where wner='SYS' and object_name='TESTCHN')
4 order by dbablk;
FILE# DBABLK TCH
---------- ---------- ----------
1 52513 4
1 52514 2
1 52515 2
--清空buffer
SQL> alter system flush buffer_cache;
System altered.
SQL> select file#,dbablk,tch from x$bh where bj=
(select data_object_id from dba_objects
2 3 where wner='SYS' and object_name='TESTCHN')
order by dbablk; 4
FILE# DBABLK TCH
---------- ---------- ----------
1 52513 0
1 52514 0
1 52515 0
SQL> select file#,block#,status from v$bh where bjd=
2 (select data_object_id from dba_objects
3 where wner='SYS' and object_name='TESTCHN')
4 order by block#;
FILE# BLOCK# STATUS
---------- ---------- -------
1 52513 free
1 52514 free
1 52515 free
SQL> select file#,dbablk,tch from x$bh where bj=
2 (select data_object_id from dba_objects
3 where wner='SYS' and object_name='TESTCHN')
4 order by dbablk;
FILE# DBABLK TCH
---------- ---------- ----------
1 52513 0
1 52514 0
1 52515 0
--只讀查詢,
SQL> select * from testchn where object_id=44;
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS
I_USER1
44 44 INDEX
22-AUG-10 22-AUG-10 2010-08-22:20:45:20 VALID N N N
SQL> /
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS
I_USER1
44 44 INDEX
22-AUG-10 22-AUG-10 2010-08-22:20:45:20 VALID N N N
--block 52514 的TCH 增加了兩次
SQL> select file#,dbablk,tch from x$bh where bj=
2 (select data_object_id from dba_objects
3 where wner='SYS' and object_name='TESTCHN')
4 order by dbablk;
FILE# DBABLK TCH
---------- ---------- ----------
1 52513 0
1 52514 2
1 52514 0
1 52515 0
--可通過dump buffer header來驗證tch:
alter session set events 'immediate trace name buffers level 1';
BH (0x23ff9fbc) file#: 1 rdba: 0x0040cd22 (1/52514) class: 1 ba: 0x23fa2000
set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 0
dbwrid: 0 obj: 46133 objn: 46133 tsn: 0 afn: 1
hash: [243eb96c,290c5e88] lru: [26018f80,26018a00]
ckptq: [NULL] fileq: [NULL] objq: [27bbdc18,27bbdc18]
st: XCURRENT md: NULL tch: 2
flags:
LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/758322/viewspace-700616/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- cache buffer chain latch可以以只讀模式共享AI模式
- Cache Buffer Chain Latch等待事件AI事件
- cr塊和latch buffer cache chainAI
- cache buffer lru chain latch等待事件AI事件
- latch free 中 cache buffer chain 的整理AI
- buffer cache實驗5-latch:cache buffers chainAI
- cache buffer chainAI
- cache buffers LRU chain latchAI
- latch free(cache buffers chain)AI
- oracle實驗記錄(buffer_cache分析(3)cbc lru chain latch)OracleAI
- Latch: cache buffer chains (%)AI
- 等待模擬-cache buffer chainAI
- 等待事件_cache_buffers_lru_chain_latch事件AI
- cbc latch或cache buffer chains latch系列一AI
- 【BUFFER】Oracle buffer cache之 latch 學習記錄Oracle
- buffer cache實驗6-latch:cache buffers lru chainsAI
- buffer cache與相關的latch等待事件事件
- oracle實驗記錄 (buffer_cache分析(2)cbc latch)Oracle
- High Water Mark過高導致cache buffer chain等待嚴重AI
- 客戶資料庫出現大量cache buffer chains latch資料庫AI
- 共享池之六:shared pool latch/ library cache latch /lock pin 簡介
- cache buffers lru chainAI
- buffer busy waits, latch cache buffers chains, read by other session區別AISession
- Buffer Cache 原理
- cache buffers chains vs cache buffers lru chainAI
- 關於cache_buffer_lru_chain的疑問,知道的給小弟解答一下。AI
- row cache objects latch研究Object
- latch: cache buffers chainsAI
- IO之核心buffer----"buffer cache"
- Buffer Cache Hit Ratio
- Oracle Buffer Cache原理Oracle
- Oracle database buffer cacheOracleDatabase
- CACHE BUFFER CHAINSAI
- Database Buffer Cache (79)Database
- 資料讀取之邏輯讀簡單解析--關於BUFFER CACHE
- buffer cache實驗9-從buffer caceh中讀取資料塊解析-從邏輯讀到物理讀
- latch:cache buffers chains案例AI
- Latch: Row Cache Objects (One bug?)Object