以前一直以為buffer busy waits是以為一個物件讀取過於頻繁導致的，可是事實是這樣嗎？看看我的實驗吧。

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0    Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

create table wxh_tbd as select * from dba_objects;
create index t on wxh_tbd(object_id);
select dbms_rowid.ROWID_RELATIVE_FNO(rowid) fn, dbms_rowid.rowid_block_number(rowid) bl, wxh_tbd.object_id from wxh_tbd where rownum<100;

這幾步的工作是建立了一個表，在object_id上建立一個索引，最後一個查詢是想找到幾條記錄位於一個資料塊上，方便我們做實驗。

實驗1）都是查詢的情況，看看有沒有buffer busy wait.
分別在三個會話裡執行如下查詢，object_id 為198，194，199的都位於同一個資料塊，在會話一中開啟10046：
declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=198;
end loop;
end;
/

declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=194;
end loop;
end;
/

declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=199;
end loop;
end;
/

然後檢視會話一中跟蹤檔案的等待時間：
Elapsed times include waiting on following events:
  Event waited on                            Times Max. Wait  Total Waited
  ---------------------------------------- Waited  ----------  ------------
  latch: cache buffers chains                257       0.02       0.19
  latch: library cache                         13       0.00       0.01
  latch: library cache pin                      6       0.00       0.00

看到了吧，驚訝吧？根本沒有buffer busy wait.

實驗2）有查詢、有DML的情況，看看有沒有buffer busy wait.兩個會話都開啟10046
declare
c number;
begin
for i in 1 ..600000 loop
select count(*) into c from wxh_tbd where object_id=199;
end loop;
end;
/
begin
for i in 1 ..400000 loop
UPDATE  wxh_tbd SET object_id=194 where object_id=194;
end loop;
end;
/

兩個會話的等待時間分別如下：
Elapsed times include waiting on following events:
  Event waited on                            Times Max. Wait  Total Waited
  ---------------------------------------- Waited  ----------  ------------
  latch: cache buffers chains                226       0.00       0.05

Elapsed times include waiting on following events:
  Event waited on                            Times Max. Wait  Total Waited
  ---------------------------------------- Waited  ----------  ------------
  rdbms ipc reply                            229       0.00       0.02
  log file switch completion                   9       0.99       1.46
  log buffer space                               2       0.10       0.21
  latch: cache buffers chains                163       0.00       0.03
  log file switch (checkpoint incomplete)       2       0.08       0.12

也沒有看到buffer busy wait.

實驗3）兩個會話都是DML的情況，看看有沒有buffer busy wait事件，會話1開啟10046。
begin
for i in 1 ..400000 loop
UPDATE  wxh_tbd SET object_id=194 where object_id=194;
end loop;
end;
/

begin
for i in 1 ..400000 loop
UPDATE  wxh_tbd SET object_id=198 where object_id=198;
end loop;
end;
/
會話1的等待事件如下：
Elapsed times include waiting on following events:
  Event waited on                            Times Max. Wait  Total Waited
  ---------------------------------------- Waited  ----------  ------------
  undo segment extension                      34       0.00       0.00
  buffer busy waits                         20889       0.99       4.31
  log file switch completion                   5       1.00       2.11
  log file switch (checkpoint incomplete)       5       0.99       1.22
  latch: cache buffers chains                151       0.00       0.02
  latch: library cache pin                      9       0.00       0.00
  latch: library cache                         13       0.00       0.00
  log buffer space                               3       0.11       0.32
********************************************************************************
終於看到了等待事件buffer busy waits.

先大概說下讀取資料塊的過程：
讀取資料塊的時候，如果資料塊已經在記憶體裡了，那麼首先需要根據資料塊的DBA即地址，HASH出它在哪一個桶（hash bucket)裡，cache buffer chains 把桶裡的資料塊串了起來，如果想要訪問資料塊，需要獲得cache buffer chain latch，這個latch是用來保護資料塊的，獲得latch以後，pin資料塊。

buffer busy waits就發生在pin資料塊的過程裡，從實驗來看，讀讀，讀寫都不會有衝突，也就是都相容。可是寫寫的話，就會出現buffer busy waits。
幾乎大多數的人都誤解了，以為buffer busy waits事件表明讀的太頻繁了，其實不是。而是寫（修改）的太頻繁了。

我想之所以有誤解，是因為10G以前，把一個等待事件read by other session也劃入到了buffer busy waits裡,read by other session的意思是，多個session併發請求相同的資料塊，但因該資料塊不在buffer_cache中而必須從磁碟讀取，處理這種情況，oracle會只讓其中一個sesion進行磁碟讀取，此時其它session等待塊從磁碟上讀取進buffer_cache而丟擲read by other session等待事件。10G以後，read by other session被單獨了出來，buffer busy waits變的純粹，就是上面我說的那種情況了。

[ 本帖最後由 wei-xh 於 2010-11-28 23:14 編輯 ]

buffer busy waits你誤解了嗎？

相關文章