深入解析bufferbusywaits - 数据库编程

在写一个培训ppt的时候，为了深入理解buffe busy waits这个等待事件，做了一个仔细的测试，对大家也有帮助，经过测试，发现我个人以前的认识都有一点问题。大家一起探讨！

1. 创建测试表

www.killdb.com>conn roger/roger
Connected.
www.killdb.com>create table t_buffer_busy_waits as select * from dba_objects where rownum < 10000;
Table created.
www.killdb.com> create index t on t_buffer_busy_waits(object_id);
Index created.
www.killdb.com>select dbms_rowid.rowid_relative_fno(rowid) fn#,
2         dbms_rowid.rowid_block_number(rowid) blk#,
3         object_id
4    from t_buffer_busy_waits
5   where object_id = 100
6  /
FN#       BLK#  OBJECT_ID
---------- ---------- ----------
5      28909        100
www.killdb.com>select dbms_rowid.rowid_relative_fno(rowid) fn#,
2         dbms_rowid.rowid_block_number(rowid) blk#,
3         object_id
4    from t_buffer_busy_waits
5   where object_id = 101
6  /
FN#       BLK#  OBJECT_ID
---------- ---------- ----------
5      28909        101
www.killdb.com>c/101/99
5*  where object_id = 99
www.killdb.com>/
FN#       BLK#  OBJECT_ID
---------- ---------- ----------
5      28909         99
www.killdb.com>c/99/111
5*  where object_id = 111
www.killdb.com>/
FN#       BLK#  OBJECT_ID
---------- ---------- ----------
5      28909        111

注意，我这里就拿这几条在同一个block内的数据，来进行测试模拟 2. 模拟高并发读取模拟高并发读取的测试过程中，我同时开了3个窗口，进行测试，另外还开了第4个窗口进行检测event，如下：

--session 1
www.killdb.com>select sid from v$Mystat where rownum < 2;
SID
----------
523
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 = 99;
6    end loop;
7  end;
8  /
PL/SQL procedure successfully completed.
--session 2
www.killdb.com>select sid from v$Mystat where rownum < 2;
SID
----------
534
www.killdb.com>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 = 100;
6    end loop;
7  end;
8  /
PL/SQL procedure successfully completed.
--session 3
www.killdb.com>select sid from v$mystat where rownum < 2;
SID
----------
520
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 = 111;
6    end loop;
7  end;
8  /
PL/SQL procedure successfully completed.
www.killdb.com>

通过第4个会话窗口的检测，我发现，并没有buffer busy waits等待出现，但是出现了大家非常熟悉的latch：cache buffer chains.
监控会话（session 4）：

ww.killdb.com>select event,count(1) from v$session where wait_class#<>6 group by event;
EVENT                                                              COUNT(1)
---------------------------------------------------------------- ----------
SQL*Net message to client                                                 1
latch: cache buffers chains                                               1
www.killdb.com>/
EVENT                                                              COUNT(1)
---------------------------------------------------------------- ----------
SQL*Net message to client                                                 1
latch: cache buffers chains                                               1

从第一个测试来看，并发读取实际上并不会产生buffer busy waits。
3. 模拟2个并发会话，分别为读与写

--session 1
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 = 99;
6    end loop;
7  end;
8  /
PL/SQL procedure successfully completed.
www.killdb.com>
---session 2
www.killdb.com>declare
2    c number;
3  begin
4    for i in 1 .. 1000000 loop
5      update  t_buffer_busy_waits set object_id=111 where object_id=111;
6    end loop;
7  end;
8  /
declare
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'
ORA-06512: at line 5

大家注意，这里测试的时候必须使用位于同一个数据块内的行，前面我们已经知道object_id=99和111的这2条数据是在
同一个数据块内（实际上，object_id 从99到111都是在同一块内，不用说明了吧？）
ok，下面我们来看下第3个监控会话的信息是什么样的？

---session 3

深入解析bufferbusywaits(一)