Home >Database >Mysql Tutorial >深入解析bufferbusywaits
深入解析bufferbusywaits
- WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal
- 2016-06-07 16:12:281198browse
在写一个培训ppt的时候，为了深入理解buffe busy waits这个等待事件，做了一个仔细的测试，对大家也有帮助，经过测试，发现我个人以前的认识都有一点问题。大家一起探讨！ 1. 创建测试表 www.killdb.comconn roger/rogerConnected.www.killdb.comcreate tabl
在写一个培训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 www.killdb.com>/ EVENT COUNT(1) ---------------------------------------------------------------- ---------- SQL*Net message to client 1 control file sequential read 1 log file switch completion 1 。。。。。 EVENT COUNT(1) ---------------------------------------------------------------- ---------- SQL*Net message to client 1 latch: cache buffers chains 1 log file switch completion 1 www.killdb.com>/ EVENT COUNT(1) ---------------------------------------------------------------- ---------- SQL*Net message to client 1 latch: cache buffers chains 2 。。。。。。 www.killdb.com>/ EVENT COUNT(1) ---------------------------------------------------------------- ---------- SQL*Net message to client 1
比较奇怪，居然没有出现应该出现的buffer busy waits等待事件。难道读和写不会出现这个等待吗 ？
我们先保留这个疑问，继续测试写和写的并发是否会产生buffer busy waits。
4. 模拟2个会话并发同时写
---Session 1 www.killdb.com>declare 2 c number; 3 begin 4 for i in 1 .. 500000 loop 5 update t_buffer_busy_waits set object_id=99 where object_id=99; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. --Session 2 www.killdb.com>declare 2 c number; 3 begin 4 for i in 1 .. 500000 loop 5 update t_buffer_busy_waits set object_id=111 where object_id=111; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed.
ok，下面我们来看下第3个监控会话的结果：
www.killdb.com>l 1* select event,count(1) from v$session where wait_class#<>6 group by event www.killdb.com>/ EVENT COUNT(1) ---------------------------------------------------------------- ---------- SQL*Net message to client 1 buffer busy waits 1 latch: cache buffers chains 1 www.killdb.com>/ EVENT COUNT(1) ---------------------------------------------------------------- ---------- SQL*Net message to client 1 buffer busy waits 1 latch: cache buffers chains 1 www.killdb.com>select sid,username,p1,p2,p3,event from v$session where event like 'buffer%'; SID USERNAME P1 P2 P3 EVENT ---------- ---------- ---------- ---------- ---------- ---------------------------- 523 ROGER 5 28909 1 buffer busy waits
ok，终于出现了我们希望的结果了，这个buffer busy waits来的不容易。这里我们需要说明一点，从oracle 10.1版本开始，
这里的p3 不再是像之前的数据库版本中那样，用来表示buffer busy waits的reason了，这里表示的是所等待的block的数据块
类型。很明显，这里的p3为1，那么也就是说block type 为1，表示data block，这一点不用多说了吧！
大家看Oracle AWR，我想也经常看到buffer waits，其实也会出现在其他的block 类型上，不仅仅是data block。
只不过数据块的争用更为常见一些。如果你查询文档你会发现，有下面的一些block type类型。
1 data block 7 extent map 13 file header block 2 sort block 8 1st level bmb 14 unused 3 save undo block 9 2nd level bmb 15+2*x undo header block(x=usn#) 4 segment header 10 3rd level bmb 16+2*x undo block(x=usb#) 5 save undo header 11 bitmap block 6 free list 12 bitmap index block
既然有可能是其他类型的block也会出现，那么按理说读和写是不兼容的，也应该出现，难道我们的测试有问题？
可能是并发不够，这里我再次测试了第2个测试：
Session 1： www.killdb.com>declare 2 c number; 3 begin 4 for i in 1 .. 500000 loop 5 update t_buffer_busy_waits set object_id=105 where object_id=105; 6 end loop; 7 end; 8 / session 2： www.killdb.com>declare 2 c number; 3 begin 4 for i in 1 .. 500000 loop 5 update t_buffer_busy_waits set object_id=106 where object_id=106; 6 end loop; 7 end; 8 / session 3: www.killdb.com>declare 2 c number; 3 begin 4 for i in 1 .. 500000 loop 5 update t_buffer_busy_waits set object_id=107 where object_id=107; 6 end loop; 7 end; 8 / Session 4: www.killdb.com>declare 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.
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：【MongoDB】探讨《MongoDB权威指南》书籍中的几点错误Next article：node.jsmongodbReplSet