深入解析bufferbusywaits - 数据库编程

e 2 c number; 3 begin 4 for i in 1 .. 500000 loop 5 update t_buffer_busy_waits set object_id=108 where object_id=108; 6 end loop; 7 end; 8 / Session 5: www.killdb.com>declare 2 c number; 3 begin 4 for i in 1 .. 1000000 loop 5 select count(*) into c from t_buffer_busy_waits where object_id =109; 6 end loop; 7 end; 8 /

我这里同时模拟了5个会话进行操作，其中4个是update，一个进行select查询，注意，这里重复操作的5条数据，其实都是在同一个数据块中。
下面我们来看下第6个监控会话的结果是什么呢？

session 6:
www.killdb.com>select sid,username,p1,p2,p3,event from v$session where event like 'buffer%';
SID USERNAME           P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------
521 ROGER               2       5657         23 buffer busy waits
www.killdb.com>/
SID USERNAME           P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------
521 ROGER               2       5657         23 buffer busy waits
www.killdb.com>/
SID USERNAME           P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------
521 ROGER               2       5657         23 buffer busy waits
www.killdb.com>/
SID USERNAME           P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------
521 ROGER               2       5657         23 buffer busy waits
w.killdb.com>select sid,username,p1,p2,p3,event from v$session where event like 'buffer%';
SID USERNAME           P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------
534 ROGER               5      28909          1 buffer busy waits
www.killdb.com>/
SID USERNAME           P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------
521 ROGER               5      28909          1 buffer busy waits
523 ROGER               5      29037          1 buffer busy waits
524 ROGER               5      29037          1 buffer busy waits
537 ROGER               5      29037          1 buffer busy waits
www.killdb.com>/
SID USERNAME           P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------
521 ROGER               5      28909          1 buffer busy waits
523 ROGER               5      29037          1 buffer busy waits
524 ROGER               5      29037          1 buffer busy waits
537 ROGER               5      29037          1 buffer busy waits
www.killdb.com>/
SID USERNAME           P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------
520 ROGER               5      29037          1 buffer busy waits
521 ROGER               5      28909          1 buffer busy waits
534 ROGER               5      29037          1 buffer busy waits
www.killdb.com>/
SID USERNAME           P1         P2         P3 EVENT
---------- ---------- ---------- ---------- ---------- ----------------------------------------
520 ROGER               5      29037          1 buffer busy waits
521 ROGER               5      28909          1 buffer busy waits
534 ROGER               5      29037          1 buffer busy waits

我们可以看到，不仅仅是数据块，这里undo 也出现的buffer busy waits，很明显，这里的file 2是undo datafile。

因此，下面我们来个简单的总结：
1) Buffer busy waits等待的本质是因为写的缘故出现争用.
2) Oracle里面写写是不兼容的，写和读也是不兼容的，有可能出现buffer busy waits等待.
3) 实际上读写并发容易产生回滚段的争用，等待事件也是buffer busy waits.

深入解析bufferbusywaits(三)