cache buffer chain latch可以以只讀模式共享
當多個程式請求同一類別的同一個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。
實驗:
分別在三個會話裡執行如下查詢,object_id 為198,194,199的都位於同一個資料塊,在會話一中開啟10046:
declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=198;
end loop;
end;
/
declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=194;
end loop;
end;
/
declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=199;
end loop;
end;
/
然後檢視會話一中跟蹤檔案的等待時間:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch: cache buffers chains 257 0.02 0.19
latch: library cache 13 0.00 0.01
latch: library cache pin 6 0.00 0.00
As far as I can tell, the shared read latch has only limited benefit for the cache buffers chains
latch.
On a typical 'get', the session has to acquire a
'pin' structure on the buffer header before reading the
buffer. (The sequence is: get latch, search chain, pin buffer header, drop latch, read buffer, get
latch unpin buffer header, drop latch), so most buffer reads start with a buffer header write and
therefore the latch get is an exclusive write, not a shared read.
There are 'consistent gets - examination' which I believe are buffer reads whilst holding the
latch, and these can use shared read. Typical cases for this are reads of index root block, reads
of undo blocks, and reads of single table hash cluster blocks with no collisions.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22034023/viewspace-682438/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 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
- 文件被鎖定以只讀模式開啟怎麼取消 檔案只讀模式怎麼解除模式
- 客戶資料庫出現大量cache buffer chains latch資料庫AI
- High Water Mark過高導致cache buffer chain等待嚴重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
- Cache Buffers chains,存在共享模式?AI模式
- Buffer Cache 原理
- cache buffers chains vs cache buffers lru chainAI
- row cache objects latch研究Object
- latch: cache buffers chainsAI
- 關於cache_buffer_lru_chain的疑問,知道的給小弟解答一下。AI
- IO之核心buffer----"buffer cache"
- Buffer Cache Hit Ratio
- Oracle Buffer Cache原理Oracle
- Oracle database buffer cacheOracleDatabase
- CACHE BUFFER CHAINSAI
- Database Buffer Cache (79)Database
- 直播網站程式原始碼,【openpyxl】只讀模式、只寫模式網站原始碼模式
- 資料讀取之邏輯讀簡單解析--關於BUFFER CACHE