cache buffer chain latch可以以只读模式共享-CSDN博客

当多个进程请求同一类别的同一个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.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1229436447262

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-682438/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-682438/