产生原因
官方定义：
This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is “busy”. The two main cases where this can occur are:
Another session is reading the block into the bufferAnother session holds the buffer in an incompatible mode to our request
模拟等待
创建测试表并插入数据：
SQL> create table tb1 (id int ,name varchar2(10));Table created.SQL> insert into tb1 values (1,'scott');1 row created.SQL> insert into tb1 values (2,'tom');1 row created.SQL> commit;Commit complete.
会话1修改数据：
SQL> select userenv('sid') from dual;USERENV('SID')--------------1SQL> beginfor i in 1..100000 loopupdate tb1 set name='rose' where id=2;commit;end loop;end;/
会话2修改数据：
SQL> select userenv('sid') from dual;USERENV('SID')--------------27SQL> beginfor i in 1..100000 loopupdate tb1 set name='john' where id=1;commit;end loop;end;/
在会话3查看等待情况：
--定位到造成等待的SQLSQL> SELECT g.inst_id,g.sid,g.serial#,g.event,g.username,g.sql_hash_value,s.sql_fulltextFROM gv$session g,v$sql sWHERE g.sql_hash_value = s.hash_value and username='HR' and event='buffer busy waits';INST_ID SID SERIAL# EVENTUSERNAME SQL_HASH_VALUE SQL_FULLTEXT---------- ---------- ---------- ------------------------------ ---------- -------------- --------------------------------------------------117 buffer busy waits HR 401484711 UPDATE TB1 SET NAME='rose' WHERE ID=21 27 49 buffer busy waits HR 2040921087 UPDATE TB1 SET NAME='john' WHERE ID=1--定位到热点快SQL> select event,sid,p1,p2,p3 from v$session_wait_history where event='buffer busy waits'EVENT SID P1 P2 P3-------------------- ---------- ---------- ---------- ----------buffer busy waits 14 54711buffer busy waits 14 54711buffer busy waits 14 54711buffer busy waits 14 54711buffer busy waits 14 54711buffer busy waits 274 54711buffer busy waits 274 54711buffer busy waits 274 54711buffer busy waits 274 54711buffer busy waits 274 5471110 rows selected.--P1=file#--P2=block#--定位到块所属的段SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME FROM DBA_EXTENTS A WHERE FILE_ID = 4 AND 5471 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;OWNERSEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAMEPARTITION_NAME---------- -------------------- ------------------ ------------------------------ ------------------------------HR TB1 TABLE USERS
解决方法
As buffer busy waits are due to contention for particular blocks then you cannot take any action until you know which blocks are being competed for and why. Eliminating the cause of the contention is the best option. Note that “buffer busy waits” for data blocks are often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index) - the first session processes the blocks that are in the buffer cache quickly but then a block has to be read from disk - the other sessions (scanning the same index) quickly ‘catch up’ and want the block which is currently being read from disk - they wait for the buffer as someone is already reading the block in.
The following hints may be useful for particular types of contention - these are things that MAY reduce contention for particular situations:
参考： WAITEVENT: “buffer busy waits” Reference Note (Doc ID 34405.1)
欢迎关注我的公众号，一起学习。
如果觉得《Oracle等待事件之buffer busy waits》对你有帮助，请点赞、收藏，并留下你的观点哦！
