被誤讀的buffer busy waits

讨論主題：

1、你能重制buffer busy waits等待嗎？

2、重制buffer busy waits等待之後，你抓住産生buffer busy waits 的原兇了嗎？

3、加大log buffer會減少buffer busy waits等待嗎？

4、找到原兇之後如何優化buffer busy waits等待？

測試DB版本11g：

SQL> select * from v$version where rownum=1;

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

（一）、用Dtrace +mdb跟蹤模拟buffer busy waits等待事件

SQL> select sid from v$mystat where rownum=1;

SID           

125           

SQL> select spid from v$session s,v$process b where s.paddr=b.addr and s.sid in(select sid from v$mystat where rownum=1);

SPID

1887

SQL> select rowid,name from t1 where id=1;

ROWID NAME

AAASTVAAGAAAACnAAA BBBBBB

SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from t1;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

6                                  167           

SQL> select hladdr from x$bh where file#=6 and dbablk=167;

HLADDR

00000003A41E4080

（二）、再開一個視窗用mdb設mdb斷點

-bash-3.2# mdb -p 1887

（三）、再回到125号會話：對 rowid='AAASTVAAGAAAACnAAA'這條記錄做修改

update t1 set name='BBBBBB' where rowid='AAASTVAAGAAAACnAAA';

這時已被阻塞

（四）、接第二步驟開始用mdb跟蹤

Loading modules: [ ld.so.1 libc.so.1 ]

sskgslcas :b

:c

mdb: stop at sskgslcas

mdb: target stopped at:

sskgslcas: movq %rsi,%rax

sskgsldecr :b

mdb: stop at sskgsldecr

sskgsldecr: lock subq %rsi,(%rdi)

::step

sskgslcas+3: lock cmpxchgq %rdx,(%rdi)

（五）、打開144号會話：查詢rowid='AAASTVAAGAAAACnAAA'的這行記錄

SQL> conn gyj/gyj

Connected.

SQL> select distinct sid from v$mystat;

SID           

144           

SQL> select * from t1 where rowid='AAASTVAAGAAAACnAAA';

這時查詢被阻塞

（六）、打開138号會話：觀察等待事件

SQL> select sid,event,total_waits,time_waited_micro from v$session_event where lower(event) like 'buffer%';

SID EVENT           

TOTAL_WAITS TIME_WAITED_MICRO

144 buffer busy waits
      1           7005803           

重制了buffer busy waits等待事件

buffer busy waits 的原兇是：寫阻塞讀的原因！

一、當log_buffer=5210112時分别在兩會話上跑SQL，産生buffer busy waits等事

1.查目前的log_buffer大小,即為5M

sys@OCM> show parameter log_buffer;

NAME TYPE VALUE

log_buffer integer 5210112

2.分别在兩會話上跑sql

begin

for i in 1 .. 500000 loop

update t1 set id=id+0 where rowid='AAAS1wAADAAAAFUAAA';

commit;

end loop;

end;

/

declare

m_name varchar2(50);

for i in 1..500000 loop

select name into m_name from gyj.t1 where rowid='AAAS1wAADAAAAFUAAA';           

end loop;

3.産生的buffer busy waits等事

sys@OCM> sys@OCM> select sid,event,total_waits,time_waited_micro from v$session_event where event in('log buffer

space','buffer busy waits') and sid in(141,24);

SID EVENT                                                            TOTAL_WAITS TIME_WAITED_MICRO           

24 buffer busy waits                                                      14915          13445559
    24 log buffer space                                                          41          12871945
   141 buffer busy waits                                                       1866            554125
   141 log buffer space                                                          31          14763344
           

二、當log_buffer=40960000時分别在兩會話上跑SQL，産生buffer busy waits等事

1. 修改log_buffer=5210112為20480000

   sys@OCM> alter system set log_buffer=20480000 scope=spfile;

   sys@OCM> startup force;

   sys@OCM> show parameter log_buffer

   log_buffer integer 20480000

select name into m_name from gyj.t1 where rowid='AAAS1wAADAAAAFUAAA';           

3.産生buffer busy waits等事

sys@OCM> select sid,event,total_waits,time_waited_micro from v$session_event where event in('log buffer

space','buffer busy waits') and sid in(142,20);

SID EVENT                                                            TOTAL_WAITS TIME_WAITED_MICRO           

20 buffer busy waits                                                      17259           7585360
   142 buffer busy waits                                                       4902           1343520
           

三、對步驟一與步驟二對比

sys@OCM> sys@OCM> select sid,event,total_waits,time_waited_micro from v$session_event where event in('log buffer

SID EVENT                                                            TOTAL_WAITS TIME_WAITED_MICRO           

24 buffer busy waits                                                      14915          13445559
    24 log buffer space                                                          41          12871945
   141 buffer busy waits                                                       1866            554125
   141 log buffer space                                                          31          14763344
           

SID EVENT                                                            TOTAL_WAITS TIME_WAITED_MICRO           

20 buffer busy waits                                                      17259           7585360
   142 buffer busy waits                                                       4902           1343520           

比較TIME_WAITED_MICRO，明顯是步驟一的比步驟二的大。步驟一的log_buffer=5M ，步驟二的log_buffer=20M

說明步驟二的buffer busy waits的等待時間少。即增大log_buffer可以減少buffer busy wait等待的時間。

4、找到原兇之後如何優化buffer busy waits等

buffer busy waits優化的重點是寫,即寫操作的時間更快一點或着寫操作的次數更少一點！！！