[摘錄]Oracle Wait Interface之Buffer busy waits事件

當程式需要存取SGA中的buffer的時候，它會依次執行如下步驟的操作：

1.獲得cache buffers chains latch，遍歷那條buffer chain直到找到需要的buffer header
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 header pin，是因為為了保證資料的一致性，同一時刻一個block只能被一個程式pin住進行存取，因此當一個程式需要存取buffer cache中一個被其他程式使用的block的時候，這個程式就會產生對該block的buffer busy waits事件。

截至Oracle 9i，buffer busy waits事件的p1,p2,p3三個引數分別是file#,block#和id，分別表示等待的buffer block所在的檔案編號，塊編號和具體的等待原因編號，到了Oracle 10g，前兩個引數沒變，第3個引數變成了塊型別編號，這一點可以透過查詢v$event_name檢視來進行驗證：

PHP code:



Oracle 9i


SQL> select parameter1,parameter2,parameter3 from v$event_name where name='buffer busy waits';


PARAMETER1                  PARAMETER2                 PARAMETER3


------------------------ ------------------------ ------------------------


file#                             block#                          id


Oracle 10g


PARAMETER1                  PARAMETER2                 PARAMETER3


------------------------ ------------------------ ------------------------


file#                             block#                          class#

在診斷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);

查詢的第一部分：如果等待的塊型別是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進行分析，採取相應的調整措施

附錄：

 

P1 = file# (Absolute File# in Oracle8 onwards)
P2 = block#
P3 = id (Reason Code) ，在10g中P3表示塊型別編號 class#

原因程式碼P3 ：

A block is being read
=====  
100       We want to NEW the block but the block is currently being read by another session (most likely for undo).  

200       We want to NEW the block but someone else has is using the current copy so we have to wait for them to finish.  

230       Trying to get a buffer in CR/CRX mode , but a modification has started on the buffer that has not yet been completed.  
          -  A modification is happening on a SCUR or XCUR buffer, but has not yet completed  
          (dup.) 231  CR/CRX scan found the CURRENT block, but a modification has started on the buffer that has not yet been completed.  

130       Block is being read by another session and no other suitable block image was found, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefor it will read the CR version of the block.  

110       We want the CURRENT block either shared or exclusive but the Block is being read into cache by another session, so we have to wait until their read() is completed.  
          (duplicate)  120  We want to get the block in current mode but someone else is currently reading it into the cache. Wait for them to complete the read. This occurs during buffer lookup.  

210       The session wants the block in SCUR or XCUR mode. If this is a buffer exchange or the session is in discrete TX mode, the session waits for the first time and the second time escalates the block as a deadlock and so does not show up as waiting very long. In this case the statistic: "exchange deadlocks" is incremented and we yield the CPU for the "buffer deadlock" wait event.  
          (duplicate)  220  During buffer lookup for a CURRENT copy of a buffer we have found the buffer but someone holds it in an incompatible mode so we have to wait.