从buffer busy waits看oracle内存使用机制

原文：http://www.itpub.net/thread-1372826-1-1.html

以前一直以为buffer busy waits是以为一个对象读取过于频繁导致的，可是事实是这样吗？看看我的实验吧。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

create table wxh_tbd as select * from dba_objects;
create index t on wxh_tbd(object_id);
select dbms_rowid.ROWID_RELATIVE_FNO(rowid) fn, dbms_rowid.rowid_block_number(rowid) bl, wxh_tbd.object_id from wxh_tbd where rownum<100;

这几步的工作是创建了一个表，在object_id上建立一个索引，最后一个查询是想找到几条记录位于一个数据块上，方便我们做实验。

实验1）都是查询的情况，看看有没有buffer busy wait.
分别在三个会话里运行如下查询，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

看到了吧，惊讶吧？根本没有buffer busy wait.

实验2）有查询、有DML的情况，看看有没有buffer busy wait.两个会话都开启10046
declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=199;
end loop;
end;
/
begin
for i in 1 ..400000 loop
UPDATE  wxh_tbd SET object_id=194 where object_id=194;
end loop;
end;
/

两个会话的等待时间分别如下：
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  latch: cache buffers chains                   226        0.00          0.05

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  rdbms ipc reply                               229        0.00          0.02
  log file switch completion                      9        0.99          1.46
  log buffer space                                2        0.10          0.21
  latch: cache buffers chains                   163        0.00          0.03
  log file switch (checkpoint incomplete)         2        0.08          0.12

也没有看到buffer busy wait.

实验3）两个会话都是DML的情况，看看有没有buffer busy wait事件，会话1开启10046。
begin
for i in 1 ..400000 loop
UPDATE  wxh_tbd SET object_id=194 where object_id=194;
end loop;
end;
/



begin
for i in 1 ..400000 loop
UPDATE  wxh_tbd SET object_id=198 where object_id=198;
end loop;
end;
/
会话1的等待事件如下：
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  undo segment extension                         34        0.00          0.00
  buffer busy waits                           20889        0.99          4.31
  log file switch completion                      5        1.00          2.11
  log file switch (checkpoint incomplete)         5        0.99          1.22
  latch: cache buffers chains                   151        0.00          0.02
  latch: library cache pin                        9        0.00          0.00
  latch: library cache                           13        0.00          0.00
  log buffer space                                3        0.11          0.32
********************************************************************************
终于看到了等待事件buffer busy waits.

先大概说下读取数据块的过程：
读取数据块的时候，如果数据块已经在内存里了，那么首先需要根据数据块的DBA即地址，HASH出它在哪一个桶（hash bucket)里，cache buffer chains 把桶里的数据块串了起来，如果想要访问数据块，需要获得cache buffer chain latch，这个latch是用来保护数据块的，获得latch以后，pin数据块。

buffer busy waits就发生在pin数据块的过程里，从实验来看，读读，读写都不会有冲突，也就是都兼容。可是写写的话，就会出现buffer busy waits。
几乎大多数的人都误解了，以为buffer busy waits事件表明读的太频繁了，其实不是。而是写（修改）的太频繁了。


我想之所以有误解，是因为10G以前，把一个等待事件read by other session也划入到了buffer busy waits里,read by other session的意思是，多个session并发请求相同的数据块，但因该数据块不在buffer_cache中而必须从磁盘读取，处理这种情况，oracle会只让其中一个sesion进行磁盘读取，此时其它session等待块从磁盘上读取进buffer_cache而抛出read by other session等待事件。10G以后，read by other session被单独了出来，buffer busy waits变的纯粹，就是上面我说的那种情况了。

Buffer Busy Waits是怎么产生的？
作为一个Oracle Dba，如果你从未遇到过Buffer Busy Waits等待，那么你算不上一个真正的Oracle Dba。Buffer Busy Waits是Oracle 数据库非常常见的一个等待，特别是在并发写比较频繁的环境里。说起为什么会产生这个等待，首先要描述下，Oracle读写数据块的过程：
1）首先依据数据块地址计算出（HASH算法）数据块所在的HASH BUCKET。
2）根据桶的编号，计算出保护这个桶的CBC LATCH，然后申请CBC LATCH，找寻数据块在不在桶里（内存里），我们这里假设在内存里。
3）读取/修改数据块。
4）释放CBC LATCH。
以上的描述看似是非常通畅，但是存在一个问题，CBC LATCH的持有是排他的（我们暂时不考虑复杂情况：共享LATCH的持有情况），如果在排他持有CBC LATCH的情况下，读取数据块内容，那么这个LATCH的持有时间就会比较长，因为相对于LATCH的获取和释放这种CPU原子操作，读取数据块的内容是非常耗时的，因此在持有CBC LATCH的情况下，读取数据块，对于读写频繁的数据库/块，那么势必会造成CBC LATCH的争用。为了解决这个问题，Oracle引入了buffer pin（buffer lock)的功能。
我们有必要对读取数据块的内容重新做下描述，大致步骤如下：
1）首先需要判断数据块所在的HASH BUCKET。
2）然后申请CBC LATCH，定位到数据块。
3）以S/X模式获取数据块的buffer pin/lock。（读取获得s模式，修改获得x模式，S和S模式具有兼容性，S和X、X和X模式不具有兼容性）。
4）释放CBC LATCH
5）在PIN的保护下，读取/修改数据块。
6）获得CBC LATCH。
7）释放（UNPIN)BUFFER PIN(BUFFER LOCK)。
8）释放CBC LATCH。
看似步骤复杂了，CBC LATCH获取/释放了两次，可是却大大的提高了并发度。上面描述的步骤里，持有CBC LATCH的目的变得单纯，只是为了修改BUFFER的PIN模式，然后依靠PIN的模式兼容性来保护数据块，例如：S和S模式的PIN是兼容的，可以并发的读取，S和X模式是不兼容的，后来的会话需要产生等待。
虽然LATCH的持有是排他的，但是这个时间极端，引起争用的可能性不大，如果大家都是来读数据块的，那么BUFFER LOCK的S模式之间都是具有共享性的，不会产生争用。但是同一个时刻，如果一个进程以S模式持有了数据块的BUFFER LOCK，另一个进程想以X模式持有，那么就会出现争用，因为道理很简单，S模式的BUFFER LOCK和X模式的BUFFER LOCK不兼容。同理，两个同时欲修改同一个数据块的进程，也会遭遇BUFFER LOCK冲突.这个冲突以ORACLE 等待事件表示出来就是Buffer Busy Waits，也就是说Buffer Busy Waits等待的本质是buffer lock的争用导致的。
我们平时经常说读不阻塞写，写不阻塞读，那是在物理的数据块级别，在内存里，读写/写写在同一个时刻都是互相阻塞的。只有读读不阻塞。
为了方便理解，上面很多步骤做了简化，下面对某些点做些补充：
1）一旦你PIN住了一个数据块，不需要立即去UNPIN（移除PIN）它。ORACLE认为你的本次调用后还有可能去访问这个数据块，因此保留了PIN，直到本次调用结束再UNPIN。
2）Oracle在对唯一索引/undo块/唯一索引的回表/索引root、branch块的设计上，在访问(读取）的时候，获取的是共享的CBC LATCH，不需要去PIN数据块，在持有共享CBC LATCH的情况下读取数据块。可能的原因是这些块修改的可能性比较小，因此Oracle单独的采用这种机制。因此对于普通数据块的读取都是需要获取2次CBC LACTH，而对于这种特殊的数据块，只获取一次共享CBC LATCH就OK 了。
3）我们上面所说的情况都是在数据块已经存在在内存里的情况。如果数据块不在内存，有可能会产生READ BY OTHER SESSION争用等待。有时间我们再看这个等待的原因。
4）上面描述只符合10G后的版本。在10G前读读也会产生BUFFER BUSY WAITS，10G后把这方面的BUFFER BUSY WAITS归到了READ BY OTHER SESSION等待里。
5）上面的描述基本都采用了数据块这个词，更准确的表达应该是buffer block。

JL 写的的一个流程:
   1. Calculate the hash bucket/chain id (using the tablespace, file, and block numbers, and block type).
   2. Grab the relevant cache buffers chains latch
   3. Search the hash bucket/chain
   4. Pin the buffer header if found
   5. drop the latch
   6. Use the buffer (if it isn’t held by another session in an incompatible mode)
   7. Grab the latch
   8. Unpin the buffer header
   9. Drop the latch


读写时兼容的，写写是不兼容的:


session a:
SQL> begin
  2  loop
  3  update update_me set t1=t1+0.01;
  4  end loop;
  5  commit;
  6  end;
  7  /

session B:

SQL> declare s int;
  2  begin
  3  for i in 1..99999 loop
  4  select t1 into s from update_me;
  5  end loop;
  6  end;
  7  /

SQL> select dbablk,mode_held,state from x$bh bh where bh.MODE_HELD!=0;

    DBABLK  MODE_HELD      STATE
---------- ---------- ----------
     67802          2          3

SQL> select dbablk,mode_held,state from x$bh bh where bh.MODE_HELD!=0;

    DBABLK  MODE_HELD      STATE
---------- ---------- ----------
     67802          1          3
     67802          2          3