buffer busy wait 解析

一：首先解釋buffer busy wait等待事件出現的原因：

程式獲取SGA中的buffer會遵循以下步驟：

1.獲得cache buffers chains latch，遍歷那條buffer chain直到找到需要的buffer header。 #塊在快取中的排列成的鏈 就是 cache buffers chains

2.根據需要進行的操作型別(讀或寫)，它需要在buffer header上獲得一個共享或獨佔模式的buffer pin或者buffer lock。

3.若程式獲得buffer header pin，它會釋放獲得的cache buffers chains latch，然後執行對buffer block的操作。

4.若程式無法獲得buffer header pin，就會產生buffer busy waits事件等待。

那麼什麼情況下會產生buffer busy waits呢？

1，塊可能被其它的session讀到緩衝區，所以session必須等待塊的讀入結束。
2，session可能有與等待的session查詢不協調的緩衝塊。,

3，出現了hot block，所謂hot block指的是同時有若干個session，同時對同一個block進行操作，導致此block比較忙；

導致block比較忙的原因是：oracle資料庫的資料存放的最小單位就是block，資料每次讀取資料的時候只能以block為單位；
如果有多條記錄存放在一個block上的話，那麼導致多個session訪問同一個block的可能性就越大；

題外話：【 block的freelist和pctused兩個引數：

我們知道Oracle資料庫的讀取單位是資料塊（Block），而一個Block是否允許被寫入資料是基於一定的空閒度，這就是大家知道的pctfree和pctused儲存引數設定。

假設pctfree=10, pctused=40，這就表明當一個Block的空間使用率達到了90%（100-pctfree）時，這個block就不再允許被用於新增資料（insert），而保留下來的這10%的空間則被預留為行更新（update）所可能需要的空間擴充套件，我們說此時這個block就從freelist上被摘走了（實際上還有另外一種情況，就是當塊剩餘空間不足以插入一條記錄並且該塊的使用率已經超過了pctused定義的值並且該塊位於freelist header處時，該塊也會從freelist上被摘走，術語稱為UNLINK）。當有資料刪除（delete）的時候，只有該block中的資料被刪除到一定的程度，該塊才會重新被加入到freelists中，而這個程度就是pctused引數定義的數值，如我們這個例子中，只有塊中的資料降低到40%以下的時候，該塊才被重新允許用於新增資料。

透過上面的描述，可以知道所謂freelists，就是一個指定了所有可以用於insert操作的資料塊的列表。存在在這個列表中的資料塊才能用於insert操作，一旦一個資料塊無法用於insert（達到了pctfree引數指定的限度）則立刻從這個列表中被摘除。freelists的作用就在於管理高水位標誌（HWM）以下的空閒空間。】

由上面括號內容可知 增加freelist，減小pctused；這樣的目的是將一個block上可以使用的空間減少，這樣的話：一個block上的資料存放的較少，可以提高應用的訪問併發率，減少了一個塊上存多條資料的可能，減少hot block的產生；

1，帶有原因碼130的資料塊(類#1)爭用

1) 等待集中在資料塊上，並且原因碼是130，則意味著多個會話併發請求相同的資料塊，但該資料塊並不在緩衝儲存器中，並且必須從磁碟讀取。

2) 當多個會話請求不在緩衝儲存器中的相同資料塊時，ORACLE可以聰明地防止每個會話進行相同的作業系統I/O呼叫。否則，這可能嚴重地增加系統I/O的數量，所以，ORACLE只允許一個會話執行實際的I/O，而其他的會話在buffer busy waits上等待塊，執行I/O的會話在db file sequential read或db file scattered read等待事件上等待。

3) 可在v$session檢視中檢查SESSION的註冊時間，並且等待事件db file sequential(scattered) read和buffer busy waits等待相同的檔案號和塊號。

 解決方法：最佳化SQL語句，儘可能地減少邏輯讀和物理讀；

2，帶有原因碼220的資料塊(類#1)爭用

1) 等待集中在資料塊上，並且原因碼是220，則意味著多個會話同時在相同的物件上執行DML(相同塊中的不同行)。

2) 如果資料塊的尺寸較大(>=16K)，則可能強化這種現象，因為較大的塊一般在每個塊中包含更多的行。

3) 減少這種情況的等待的方法：減少併發；減少塊中行的數量；在另一個具有較小塊尺寸的表空間中重新構建物件。

 具體方法說明：

使用較大的PCTFREE和較小的PCTUSED重新構建表或索引；

使用alter table <table_name> minimize records_pre_block命令改變表以最小化每個塊的最小行數

從ORACLE9i開始，可以在另一個具有較小塊尺寸的表空間中移動或重新構建物件。

注：雖然這些方法可以最小化buffer busy waits問題，但它們無疑會增加全表掃描時間和磁碟空間利用率。

3，資料段頭(類#4)的爭用

1) 如果buffer busy waits的等待事件主要集中在資料段頭(即表或索引段頭，並且不是UNDO段頭)上，這意味著資料庫中一些表或索引有高段頭活動。

注：程式出於兩個主要原因訪問段頭，一是，獲得或修改FREELISTS資訊；二是，為了擴充套件高水位標記(HWM)。

減少這種情況的等待的方法：

>> 對使用自由表進行段管理的表，增加確認物件的FREELISTS和FREELIST GROUPS(注：FREELIST GROUPS的增加也是必須的)；

>> 確保FCTFREE和PCTUSED之間的間隙不是太小，從而可以最小化FREELIST的塊迴圈。

>> 下一區的尺寸不能太小，當區高速擴張時，建立的新區需要修改在段頭中區對映表。可以考慮將物件移動到合理的、統一尺寸的本地管理的表空間中。

4，撤銷段頭(類#17)的爭用   (undo segement header )

1) 如果buffer busy waits等待事件主要集中在撤銷段頭，這表明資料庫中的回滾段過少或者是它們的區尺寸太小，從而造成對段頭的頻繁更新。

解決：

segment header爭用是因為系統中undo segment不夠，需要增加足夠的undo segment，根據undo segment的管理方法，若是手工管理模式，需要修改rollback_segments初始化引數來增加rollback 

segment，若是自動管理模式，可以減小transactions_per_rollback_segment初始化引數的值來使oracle自動增
多rollback segment的數量

5，撤銷塊的爭用(類#18)   (undo block)

1) 如果buffer busy waits等待事件主要集中在撤銷塊上，這表明有多個併發會話為保證一致性讀同時查詢更新的資料。

解決辦法是錯開應用程式修改資料和大量查詢資料的時間.

註釋：

在診斷buffer busy waits事件的過程中，獲取如下資訊會很有用：
1.獲取產生buffer busy waits事件的等待原因編號，這可以透過查詢該事件的p3引數值獲得

2.獲取產生此事件的SQL語句，可以透過如下的查詢獲得：
select sql_text from v$sql t1,v$session t2,v$session_wait t3
where t1.address=t2.sql_address and t1.hash_value=t2.sql_hash_value
and t2.sid=t3.sid and t3.event='buffer busy waits';

3.獲取等待的塊的型別以及所在的segment，可以透過如下查詢獲得： select 'Segment Header' class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$session_wait b

where a.header_file=b.p1 and a.header_block=b.p2 and b.event='buffer busy waits' union

select 'Freelist Groups' class,a.segment_type,a.segment_name,a.partition_name from dba_segments a,v$session_wait b

where a.header_file=b.p1 and b.p2 between a.header_block+1 and (a.header_block+a.freelist_groups) and a.freelist_groups>1 and b.event='buffer busy waits' union

select a.segment_type||' block' class,a.segment_type,a.segment_name,a.partition_name from dba_extents a,v$session_wait b

where a.file_id=b.p1 and b.p2 between a.block_id and a.block_id+a.blocks-1 and b.event='buffer busy waits' and not exists(select 1 from dba_segments where 

header_file=b.p1 and header_block= b.p2);