以前一直以为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变的纯粹，就是上面我说的那种情况了。

[ 本帖最后由 wei-xh 于 2010-11-28 23:14 编辑 ]

ITPub博客

buffer busy waits你误解了吗？

最新文章