模拟bufferbusywaits等待事件

 SQL> create table buffer_busy(id number,name varchar2(20));

表已创建。
SQL> insert into buffer_busy values(1,&＃39;robinson&＃39;);

已创建 1 行。

SQL> insert into buffer_busy values(2,&＃39;robinson&＃39;);

已创建 1 行。

SQL> commit;

提交完成。

session 1中
SQL> select sid from v$mystat where rownum&＃61;1;

       SID
----------
       150

begin
 
 for i in 1..10000 loop
    update buffer_busy set name&＃61;&＃39;luoluo&＃39; where id&＃61;1;
    commit;
   end loop;
end;
/

session 2中
SQL> select sid from v$mystat where rownum&＃61;1;

       SID
----------
       147
begin
 
 for i in 1..10000 loop
    update buffer_busy set name&＃61;&＃39;luoluo&＃39; where id&＃61;2;
    commit;
   end loop;
end;
/
session 3中运行如下查询语句
SQL> select event,sid,p1,p2,p3 from v$session_wait_history where sid in(150,147);

EVENT                                                                   SID         P1         P2      P3
---------------------------------------------------------------- ---------- ---------- ---------- ----------
buffer busy waits                                                       147          5         28       1
buffer busy waits                                                       147          5         28       1
buffer busy waits                                                       147          5         28       1
buffer busy waits                                                       147          2        105      29
buffer busy waits                                                       147          2         89      27
buffer busy waits                                                       147          5         28       1
buffer busy waits                                                       147          5         28       1
SQL*Net message to client                                        147 1111838976          1       0
log file sync                                                                147       4380          0       0
buffer busy waits                                                       147          5         28       1
buffer busy waits                                                       150          5         28       1
buffer busy waits                                                       150          5         28       1
buffer busy waits                                                       150          5         28       1
buffer busy waits                                                       150          5         28       1
buffer busy waits                                                       150          5         28       1
buffer busy waits                                                       150          2        137      33
SQL*Net message to client                                        150 1111838976          1       0
log file sync                                                                150       5715          0       0
buffer busy waits                                                       150          5         28       1
buffer busy waits                                                       150          5         28       1

SQL> select * from v$waitstat where count>0;

CLASS                   COUNT       TIME
------------------ ---------- ----------
data block               2107          0
undo header               385          0
undo block                  3          0

直接查询v$session_wait可能查不出什么&＃xff0c;因为10000次循环很快就过了&＃xff0c;如果想要通过v$session_wait直接查询到buffer busy waits 等待事件
可以将循环次数增加到100000次&＃xff0c;我这里就不做了。

可以通过如下语句查询出造成该等待事件的SQL&＃xff0c;但是如果该等待事件不是很显著&＃xff0c;一闪而过&＃xff0c;下面的SQL就不能查询到了&＃xff0c;而我这个实验确实不能查询到什么
select sql_text from V$sqlarea  where (address,hash_value) in (select sql_address,sql_hash_value from v$session  where event like &＃39;%buffer busy%&＃39;);

我们可以观察上面等待事件的参数p1,p2,p3&＃xff0c;这里p1绝大部分为5&＃xff0c;p2绝大部分为28&＃xff0c;p3大部分为1&＃xff0c;由于数据库是10g,p3代表的是v$waitstat中class列的block id&＃xff0c;这里p3为1&＃xff0c;说明是data block.通过如下语句找出可疑的对象
SQL> select owner,segment_name,segment_type,block_id from dba_extents where file_id&＃61;5 ;

OWNER                          SEGMENT_NAME                                                              SEGMENT_TYPE         BLOCK_ID
------------------------------ --------------------------------------------------------------------------------- ------------------ ----------
ROBINSON                       TEST                                                                      TABLE                       9
ROBINSON                       ROBINSON                                                                  TABLE                      17
ROBINSON                       BUFFER_BUSY                                                               TABLE                      25
我们能确定引起buffer busy waits等待事件的对象是ROBINSON.BUFFER_BUSY&＃xff0c;因为只有buffer_busy这个表的block_id最接近28
然后我们可以通过如下查询
SQL> select sql_text from v$sqlarea where sql_text like &＃39;%buffer_busy%&＃39;;

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------

begin   for i in 1..10000 loop     update buffer_busy set name&＃61;&＃39;luoluo&＃39; where id&＃61;1;     commit;    end loop; end;
begin   for i in 1..10000 loop     update buffer_busy set name&＃61;&＃39;luoluo&＃39; where id&＃61;2;     commit;    end loop; end;
select sql_text from v$sqlarea where sql_text like &＃39;%buffer_busy%&＃39;
SQL> select sql_text from v$sqlarea where sql_text like &＃39;%BUFFER_BUSY%&＃39;;

SQL_TEXT
------------------------------------------------------------------------------

select sql_text from v$sqlarea where sql_text like &＃39;%BUFFER_BUSY%&＃39;
UPDATE BUFFER_BUSY SET NAME&＃61;&＃39;luoluo&＃39; WHERE ID&＃61;2
UPDATE BUFFER_BUSY SET NAME&＃61;&＃39;luoluo&＃39; WHERE ID&＃61;1
到这里我们就能够发现引起buffer busy waits等待事件的SQL了。