如下图一,假设有个进程A正在读10号文件的11号数据块,此时又有一个进程B准备写该数据块。首先获取cbc latch然后复制内存中的10号文件11号块并新增一个buffer header(bh),并把原来的bh中status改为xcur,复制完成后释放cbc latch。然后如图二,获得cbc latch修改原块状态为CR,复制块状态为XCUR并把buffer pin修改为X,然后可以释放latch cbc,然后进程B 可以修改该数据块,此时如果进程c要读该数据块,那么进程c就要进行等待,等待事件即buffer busy wait。附实验证明

图一:

图二:

实验一:

会话一:

SQL> select distinct sid from v$mystat;

       SID
----------
      853

会话二:

SQL> select distinct sid from v$mystat
  2  ;

       SID
----------
      1713

会话三:

SQL> select sid,event,total_waits,total_timeouts,time_waited_micro/100,time_waited_micro/total_waits/100
  2  from v$session_event where sid in (853,1713) and event in ('buffer busy waits');

no rows selected

会话一进行读数据块:

 declare
   j number;
    begin
  for i in 1..3000000 loop
   select id into j from ye where rowid='AAAWt/AAKAAAAHaACV';
   end loop;
   end;
/

会话二进行写数据块:

begin
for i in 1..300 loop
update ye set id=id+0 where rowid='AAAWt/AAKAAAAHaACV';
commit;
end loop;
end;
/

会话三查询等待事件为853会话等待:
       SID EVENT                                    TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED_MICRO/100 TIME_WAITED_MICRO/TOTAL_WAITS/100
---------- ---------------------------------------- ----------- -------------- --------------------- ---------------------------------
       853 buffer busy waits                                 81              0                 30.32                        .374320988

上述步骤不变,将会话二中更新数据增到1000次
在会话三中发现也出现了3次buffer busy wait:

SQL> /

       SID EVENT                                    TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED_MICRO/100 TIME_WAITED_MICRO/TOTAL_WAITS/100
---------- ---------------------------------------- ----------- -------------- --------------------- ---------------------------------
       853 buffer busy waits                                499              0                238.01                        .476973948
      1713 buffer busy waits                                  3              0                  1.67                        .556666667

这3次等待其实发生在undo block上,当会话853构造cr块是需要在undo上加上共享模式buffer bin,在会话1713写block需要向undo写入前印象,因此需要等待853构造完cr块。

会话1713分别改为:

begin
for i in 1..60000 loop
update ye set id=id+0 where rowid='AAAWt/AAKAAAAHaACV';
if mod(i,500)=0 then
commit;
end if;
end loop;
commit;
end;
/

begin
for i in 1..60000 loop
update ye set id=id+0 where rowid='AAAWt/AAKAAAAHaACV';
commit;
end loop;
commit;
end;
/

会话三的结果:


SQL> select sid,event,total_waits,total_timeouts,time_waited_micro/100,time_waited_micro/total_waits/100
2 from v$session_event where sid in (853,1713) and event in ('buffer busy waits');


SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED_MICRO/100 TIME_WAITED_MICRO/TOTAL_WAITS/100
---------- ---------------------------------------- ----------- -------------- --------------------- ---------------------------------
853 buffer busy waits 12923 0 4291.97 .332118703
1713 buffer busy waits 1448 0 567.66 .392030387


SQL> /


SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED_MICRO/100 TIME_WAITED_MICRO/TOTAL_WAITS/100
---------- ---------------------------------------- ----------- -------------- --------------------- ---------------------------------
853 buffer busy waits 32471 0 11886.11 .366053094
1713 buffer busy waits 1568 0 605.15 .3859375

 

说明长事务会等待读构造cr块比短事务等待读构造cr块更久