并发update/update引起的buffer busy wait

什么是buffer busy wait?        A session that reads or modifies a buffer in the SGA must first acquire the cache buffers chains latch and traverse the buffer chain until it finds the necessary buffer header. Then it must acquire a buffer lock or a pin on the buffer header in shared or exclusive mode, depending on the operation it intends to perform. Once the buffer header is pinned, the session releases the cache buffers chains latch and performs the intended operation on the buffer itself. If a pin cannot be obtained, the session waits on the buffer busy wait event. This wait event does not apply to read or write operations that are performed in sessions’ private PGAs.一个会话读或者修改一个SGA中的buffer，首先必须获得cache buffers chains latch和遍历缓冲区链表，直到找到所需的缓冲区头。会话必须获得一个buffer lock或者pin在 shared or exclusive 模式下,这取决于它打算执行的操作。一旦缓冲区头被pin住，会话释放的超高速缓冲存储器锁存器链，并且执行在缓冲本身预定的操作。如果不能获得pin后，会话的缓冲区忙等待事件等待。这个等待事件不适用于读取或写入都在会话的私有PGA的执行的操作。读写时兼容的，写写是不兼容的:此实验是不断的去更新同一个块中的不同记录,通过这种方式可以造成buffer busy wait等待.--创建测试表create table test_db(   ID NUMBER,   NICK VARCHAR2(30)   );   SQL> insert into test_db values(1,'zhaolin');1 row created.SQL> commit;Commit complete.SQL> insert into test_db values(2,'nature');1 row created.SQL> commit;Commit complete.SQL>  select rowid,t.* from test_db t;ROWID   ID NICK------------------ ---------- ------------------------------AAATKfAAEAAAtZHAAA    1 zhaolinAAATKfAAEAAAtZHAAB    2 nature--检查这两条记录是否在同一个块上SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) file#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block# from test_db;     FILE#     BLOCK#---------- ---------- 4     185927 4     185927 --session 1SQL> select sid from v$mystat where rownum<2;       SID----------        33declare   i number:=0;begin     loop        update test_db set nick='session1' where id=1;        i:=i+1;        if mod(i,100)=0 then            commit;        end if;     end loop;end; --session 2SQL> select sid from v$mystat where rownum<2;       SID----------        1declare   i number:=0;begin     loop        update test_db set nick='session2' where id=2;        i:=i+1;        if mod(i,100)=0 then            commit;        end if;     end loop;end;    --我们再启动会话session3SQL> col name format a30SQL> select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#            from v$active_session_history ash, v$event_name enm            where ash.event#=enm.event#          and ash.session_id in (1,33)  2    3    4    5  ;SESSION_ID NAME   P1     P2 P3  WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- --------------33 latch: In memory undo latch   836651180    243  0    0     78495       4 18592733 buffer busy waits   4 185927  1    0     78495       4 185927 1 latch: cache buffers chains   884779988    150  0    0-1       0      0 1 buffer busy waits   4 185927  1    0     78495       4 18592733 latch: cache buffers chains   884779988    150  0    0-1       0      0 1 buffer busy waits   4 185927  1    0     78495       4 185927 1 latch: cache buffers chains   884150708    150  0    0-1       0      0 1 latch: cache buffers chains   884779988    150  0    0-1       0      0 1 latch: cache buffers chains   884649516    150  0    0-1       0      0 1 buffer busy waits   4 185927  1    0     78495       4 185927 1 buffer busy waits   4 185927  1    0     78495       4 185927SESSION_ID NAME   P1     P2 P3  WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- --------------33 latch: cache buffers chains   884779988    150  0    0-1       0      0 1 latch: cache buffers chains   884779988    150  0    0-1       0      033 latch: In memory undo latch   836651180    243  0    0     78495       4 185927 1 latch: cache buffers chains   884779988    150  0    0-1       0      0 1 buffer busy waits   4 185927  1    0     78495       4 18592733 buffer busy waits   4 185927  1    0     78495       4 18592733 buffer busy waits   4 185927  1    0     78495       4 185927 1 latch: In memory undo latch   836651280    243  0    0     78495       4 185927 1 latch: cache buffers chains   884779988    150  0    0-1       0      0 1 buffer busy waits   4 185927  1    0     78495       4 18592733 latch: cache buffers chains   884709944    150  0    0-1       0      0SESSION_ID NAME   P1     P2 P3  WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#---------- ------------------------------ ---------- ---------- ---------- ---------- ------------ ------------- -------------- 1 latch: cache buffers chains   884779988    150  0    0-1       0      033 latch: In memory undo latch   836651180    243  0    0     78495       4 18592733 latch: cache buffers chains   884862440    150  0    0-1       0      0 1 latch: cache buffers chains   884779988    150  0    0-1       0      0 1 latch: cache buffers chains   884779988    150  0    0-1       0      0 1 buffer busy waits   4 185927  1    0     78495       4 185927 1 buffer busy waits   4 185927  1    0     78495       4 18592733 latch: cache buffers chains   884779988    150  0    0-1       0      033 db file sequential read   1   7802  1    040       1   780233 db file sequential read   1  57936  1    062       1  57936 1 db file sequential read   2   5549  1    0-1       0      0查看p1 p2参数select owner,segment_name,segment_type from dba_extents      where file_id = 4 and 185927between block_id and block_id+blocks-1; SQL> SQL>   2  OWNER       SEGMENT_NAME SEGMENT_TYPE------------------------------ --------------------------------------------------------------------------------- ------------------SCOTT       TEST_DB TABLE