Buffer Busy Wait小結

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，可以通過如下查詢獲得：
PHP code:--------------------------------------------------------------------------------
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);--------------------------------------------------------------------------------
查詢的第一部分：如果等待的塊型別是segment header，那麼可以直接拿buffer busy waits事件的p1和p2引數去dba_segments檢視中匹配header_file和header_block欄位即可找到等待的segment名稱和segment型別，進行相應調整
查詢的第二部分：如果等待的塊型別是freelist groups，也可以在dba_segments檢視中找出對應的segment名稱和segment型別，注意這裡的引數p2表示的freelist groups的位置是在segment的header_block+1到header_block+freelist groups組數之間，並且freelist groups組數大於1
查詢的第三部分：如果等待的塊型別是普通的資料塊，那麼可以用p1、p2引數和dba_extents進行聯合查詢得到block所在的segment名稱和segment型別

對於不同的等待塊型別，我們採取不同的處理辦法：
1.data segment header：
程式經常性的訪問data segment header通常有兩個原因：獲取或修改process freelists資訊、擴充套件高水位標記，針對第一種情況，程式頻繁訪問process freelists資訊導致freelist爭用，我們可以增大相應的segment物件的儲存引數freelist或者freelist groups；若由於資料塊頻繁進出freelist而導致程式經常要修改freelist，則可以將pctfree值和pctused值設定較大的差距，從而避免資料塊頻繁進出freelist；對於第二種情況，由於該segment空間消耗很快，而設定的next extent過小，導致頻繁擴充套件高水位標記，解決的辦法是增大segment物件的儲存引數next extent或者直接在建立表空間的時候設定extent size uniform
2.data block：
某一或某些資料塊被多個程式同時讀寫，成為熱點塊，可以通過如下這些辦法來解決這個問題：
(1)降低程式的併發度，如果程式中使用了parallel查詢，降低parallel degree，以免多個parallel slave同時訪問同樣的資料物件而形成等待降低效能
(2)調整應用程式使之能讀取較少的資料塊就能獲取所需的資料，減少buffer gets和physical reads
(3)減少同一個block中的記錄數，使記錄分佈於更多的資料塊中，這可以通過若干途徑實現：可以調整segment物件的pctfree值，可以將segment重建到block size較小的表空間中，還可以用alter table minimize records_per_block語句減少每塊中的記錄數
(4)若熱點塊物件是類似自增id欄位的索引，則可以將索引轉換為反轉索引，打散資料分佈，分散熱點塊
3.undo segment header：
undo segment header爭用是因為系統中undo segment不夠，需要增加足夠的undo segment，根據undo segment的管理方法，若是手工管理模式，需要修改rollback_segments初始化引數來增加rollback segment，若是自動管理模式，可以減小transactions_per_rollback_segment初始化引數的值來使oracle自動增多rollback segment的數量
4.undo block：
undo block爭用是由於應用程式中存在對資料的讀和寫同時進行，讀程式需要到undo segment中去獲得一致性資料，解決辦法是錯開應用程式修改資料和大量查詢資料的時間

小結：buffer busy waits事件是oracle等待事件中比較複雜的一個，其形成原因很多，需要根據p3引數對照Oracle提供的原因程式碼表進行相應的診斷，10g以後則需要根據等待的block型別結合引起等待時間的具體SQL進行分析，採取相應的調整措施

9. 雖然buffer busy waits事件的發生可能至少有十個不同的原因，但是程式碼130和220是最常見的原因。基本上，小於200的程式碼號意味著這種等待是和I/O有關的。
10. 帶有原因碼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等待相同的檔案號和塊號。

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

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

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

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

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

4)具體方法說明：

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

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

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

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

12. 資料段頭(類#4)的爭用

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

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

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

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

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

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

13. 撤銷段頭(類#17)的爭用

1)如果buffer busy waits等待事件主要集中在撤銷段頭，這表明資料庫中的回滾段過少或者是它們的區尺寸太小，從而造成對段頭的頻繁更新。如果使用ORACLE9I的由資料庫系統管理UNDO段，就不需要處理這種問題，因為ORACLE會根據需要增加額外的的UNDO段。

2)可以建立並啟用私有回滾段，以減少每個回滾段的事務數量。需要修改init.ora檔案中的ROLLBACK_SEGMENTS引數。

3)如果使用公用回滾段可以減少初始化引數transactions_per_rollback_segment的值，ORACLE通過transactions/transactions_per_rollback_segment來獲取公有回滾段的最小數量。

14. 撤銷塊的爭用(類#18)\

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

2)這是應用程式存在問題，當應用程式在不同時間內執行查詢和DML時，這種問題不會存在。