Oracle Buffer Busy Waits

buffers不能被鎖定，因為他們不是關係型結構。然而有許多原因，它們可以會臨時不能使用。當出現這種情況時，buffer確實很忙。將所有複雜效能提練為本質的東西，一個buffer busy waits等待事件是關於受限的併發性。一個程式需要訪問一個buffer，但不能訪問它因為其它的程式正在訪問buffer且不允許併發訪問。在很多情況下都會出現這種情況，每種情況都有特定的解決方案。

使用buffer busy waits等待事件具有挑戰性的是有多種可能的診斷方法。一種最常見的診斷buffer busy的方法是使用reason code。reason code由三個數字組成，每一個數字的值都揭示了為什麼buffer正忙且不能被立即訪問的的部分原因。但在Oracle 11g中，oracle從v$session_wait與v$session檢視中刪除了p3這一列。

在Oracle 10g中，Oracle採用了一種最常見的buffer busy情況並且指定了它特有的等待事件。因此可以看到read by other session等待事件用於buffer busy waits等待事件的一部分。雖然可能引起混淆但也使用的等待事件更加具體。

四步診斷法
解決buffer busy型別的等待事件(包括read by other session等待事件)的關鍵是首先要了解正在等待的buffer。僅僅知道top等待事件是buffer busy waits是不夠的。在瞭解正確解決方案前將需要收集額外的資訊。額外需要的資訊是buffer的型別與是否它是header塊。有了這些資訊後，就能對這個問題制定一些解決方案集。為了解決這個問題有一個四步診斷方法如下:
1.確定是否存在引數模式
2.識別buffer型別
3.確定是否是header塊
4.實現合適的解決方案集

確定是否存在引數模式
為了診斷重複抽樣buffer busy waits等待事件的p1與p2引數。p1引數是buffer的檔案號，p2引數是塊號。正常情況當重複抽樣p1與p2值時，它們將會出現變化，指示正在等待不同的buffers。通常也可以看到一些小資料塊集關聯到特定的物件。如果相同的buffer或看到兩次出現雖然很罕見但要特別注意。它們確實是hot塊。當你重複抽樣時，注意引數值，分析時需要使用它們。

SQL> @swswp buffer%busy
Database: prod16 30-MAR-10 02:22pm
Report: swswp.sql OSM by OraPub, Inc. Page 1
Session Wait Real Time w/Parameters
Sess
   ID Wait Event                   P1           P2        P3
----- ---------------------------- ------------ --------- -----
4391  buffer busy waits            4            54        0
4379  buffer busy waits            4            54        0
4381  buffer busy waits            4            54        0
4405  buffer busy waits            5            10340
4 rows selected.
SQL> l
1 select sid, event,
2 p1, p2, p3
3 from v$session_wait
4 where event like '&input%'
5 and state = 'WAITING'
6* order by event,sid,p1,p2
SQL>

識別buffer型別
每一個oracle塊buffer是Oracle段的一部分。每個Oracle段是一個段型別，比如資料段，索引段，undo段或者臨時段。解決方案集部分基於段型別。使用buffer busy waits等待事件的p1(檔案號)與p2(塊號),透過查詢dba_extents 與dba_data_files可以識別段型別

def file_id=&1
def block_id=&2
col a format a77 fold_after
set heading off verify off echo off
set termout off
col tablespace_name new_value ts_name
select ts.name tablespace_name
from v$tablespace ts, v$datafile df
where file# = &file_id
and ts.ts# = df.ts#
/
set termout on
select
'File number :'||&file_id a,
'Block number :'||&block_id a,
'Owner :'||owner a,
'Segment name :'||segment_name a,
'Segment type :'||segment_type a,
'Tablespace :'||e.tablespace_name a,
'File name :'||f.file_name a
from dba_extents e,
dba_data_files f
where e.file_id = f.file_id
and e.file_id = &file_id
and e.block_id < = &block_id and e.block_id + e.blocks > &block_id
and e.tablespace_name = '&ts_name'

上面三個會話正在等待檔案4的塊54。執行下面的指令碼來檢視物件型別

SQL> @objfb 4 54
File number :4
Block number :54
Owner :OE
Segment name :ORDERS
Segment type :TABLE
Tablespace :USERS
File name :/u01/oradata/prod16/OE01.dbf
1 row selected.

確定是否是header塊
每個Oracle段有一個header塊。表段與undo塊有一個單獨的header塊。段的第一個extent中的第一個塊就是段的header塊。header塊不同於其它的物件塊，因為他們包含一些特定的資訊。這種特殊性依賴於段型別。這也就是為什麼首先要確實段型別的原因。

檢視dba_segments包含了關於單個Oracle段的資訊。它也包含了header塊的檔案號與塊號。因此一個簡單查詢將返回busy buffer是否是一個header塊。

SQL>
1 select *
2 from dba_segments
3 where header_file = 4
4* and header_block = 54
SQL> /
no rows selected

在識別buffer busy模式，物件型別與是否是header塊之後，就有足夠的資訊來直接選擇可用的解決方案了。

實現合適的解決方案集
buffer busy waits等待事件在我們選擇合適的解決方案集之前之所以能給我們診斷帶來許多麻煩， 是因為我們必須首先確定busy pattern，buffer型別與是否是header塊。

單個忙表塊的解決方案
如果相同的單個buffer幾乎總是busy buffer，那麼我們需要找出原因。在我們的示例中，一組緩衝區處於繁忙狀態，包括緩衝區4、54;也就是說，不僅僅是一個緩衝區幾乎總是繁忙的。但是，如果只有一個繁忙的緩衝區，那麼需要清楚地瞭解儲存在塊中的資訊(可能只查詢該塊中的行)以及為什麼應用程式對該塊如此感興趣。

可以基於等待會話的v$session.sql_id列來判斷正在等待的SQL語句。甚至你可能需要與開發人員溝通，因為這種情況通常是與應用程式相關的。最常見的情況(但不總是)是Oracle 序列號不能被使用。當問為什麼Oracle序列號不能被使用時，可能收到的回答是“我們想我們的應用程式獨立於資料庫”。

這種問題的原因是這種快速buffer訪問給buffer的內部結構提出了難以置信的高併發要求。當內部結構被改變後，塊對於其它程式是不能使用的，因此出現buffer busy waits等待事件。因為應用程式架構進行修改通常是不現實的，創造性的找到其它方法來解決問題。例如，如果每一行包含一個應用程式序列號，那麼將行記錄移動到它自己的塊中並增加一個大的固定長度的列，或將塊的pct_free屬性設定為一個很高的值來保證儲存最少的行記錄。這種解決方法是痛苦，但沒有buffer busy waits痛苦。

多個忙表的解決方案
多個忙表塊最有可能出現buffer busy waits與read by other session等待事件。在這種情況下，每次檢查busybuffers都是不同的，busy buffers是資料段並且它們不是頭段。當出現這種情況時，原因通常是查詢或者DML與查詢的混合。

對於純查詢來說，這將發生在多個會話查詢相關資料塊時，並且相同塊沒有存放在buffer cache。第一個會話呼叫IO子系統並post一個db file sequential read或db file scattered read等待事件。對於其它會話也呼叫IO子系統來查詢資料塊是愚蠢的，因此它們不僅僅要等待第一個會話完成IO呼叫，還要等待資料塊被存入buffer cache中。那麼其它會話就可以像任何其它會話一樣來訪問buffer。當其它會話正等待第二個會話完成時，從Oracle 10g開始，等待會話將post一個read by other session等待事件，對於之前的版本，等待會話將post一個buffer busy waits等待事件。因此會話實際上是在等待另一個會話完成讀取操作。

解決方案非常簡單，並且主要集中在增加資料塊存放在buffer cache中的可能性。如果buffer存放在buffer cache中，那麼這種busy buffer waits的情況將不會發生。因此請考慮如何增加資料塊在buffer cache中的可能性。

應用程式所關注的解決方案集中在找到top物理IO SQL語句並最佳化語句，關注減少物理IO。這個解決方案不僅由於塊訪問的減少而提高了語句效能，而且還減少了緩衝區繁忙等待的機會，並允許將來自其他物件的塊儲存在緩衝區快取中。Oracle所關注的解決方案是增加buffer cache來增加資料塊存放在buffer cache中的可能性因此而不需要進行IO呼叫。另一種有創意但不切實際的是減小資料庫塊大小。小塊與隨機行訪問模式組合會造成更有效的buffer cache.換句話說，快取更有效的存放真正被頻繁訪問的行。作業系統所關注的解決方案是減小IO讀取響應時間。快速檢索資料塊，會話將等待更少的時間。

就我個人而言，我會同時認真考慮每個選項。但我預測效能變化是吞吐量增加且響應時間減少。

還有一些我可能一無所知的問題和事情需要考慮，也許還要考慮相關的業務與預算。因此我不會簡單地從SQL或增加buffer cache開始。我將收集資訊並幫助他人收集資訊。然後一起共同制訂計劃。

另一種常見的非header資料塊問題是在buffer被改變與被查詢時出現的。通常這會呼叫DML與查詢SQL語句，但DML SQL也可以在過濾時touch大量的buffers。可能出現的問題是DML正在更新內部buffer結構時而另一個程式想要查詢資料塊的內容或者也正想要更新內部buffer結構。記住這些改變不是行改變而是內部Oracle結構改變。如果有行或表鎖問題，一個佇列等待將會被posted。好的策略是集中減少併發。考慮減小塊地密度(移動行到其它塊或增加塊的pct_free屬性)，在高峰期間減小工作負載，併發活動，減少由DML與查詢SQL所touched的buffers數量。

表段頭塊解決方案
表的第一個區的第一個塊叫作段頭塊。與所有的段頭塊一樣，它們包含非常特定的內部Oracle結構與它們的段型別相關。對於表段，頭塊的部分內容是關於塊可以接受額外插入記錄的位置。這些塊也叫作free塊。當一個程式必須插入行記錄到一個表中，為了找到一個free塊首先檢索表的段頭塊，檢索buffer，然後插入行記錄。如果有許多程式併發插入行記錄到相同的表中，一個表段頭塊將導致buffer busy waits等待事件。

幸運地是解決方案非常簡單並且工作的很好。如果正使用手動段空間管理，那麼段空間管理由free lists控制。Oracle的free list方法通常工作的很好，但在高併發情況下，現有的free lists無法處理工作負載。幸運地是，我們能很容易地修改表來在另一個段塊中建立額外的free lists。這將導致一個頭塊減少被頻繁訪問的機率，因此減少buffer busy wait競爭。只要增加free lists直到競爭平息。free lists的數量可以在dba_segments檢視中的free lists列中找到。如果這個列為空，那麼我們知道free lists沒有被使用而是使用自動段空間管理(ASSM)。

另一種選項更側重於長期並且在維護期間可以執行。這就是將高併發段移動到使用ASSM的本地管理表空間中。ASSM不使用free lists，但使用點陣圖來管理可用空間。這通常增加了表段頭塊的併發性。

Undo段頭塊解決方案
Undo段不同於表段因為它包括了與事務回滾和讀一致性相關的資訊。對於回滾與讀一致性有一個Oracle結構叫作事務表。簡單地說，事務表是到一個undo段內容的對映。每個undo段包含一個單獨的事務表，它位於undo段頭塊中。當出現大量的DML，特別是與讀一致性活動組合出現時，事務表將成為競爭點，在undo段頭塊上會出現buffer busy waits等待事件。在討論如何解決這個問題之前先來了解事務表。

預設情況下，每個Oracle事務會生成redo(前滾)與undo(回滾)資訊，就像實際資料更改與可能的索引更改一樣。undo資訊被儲存在undo段中，並且每個undo段的對映被儲存在它的事務表中。每個undo段的事務表可以持有多個事務條目。從關係型資料庫角度來考慮，每個條目關聯事務表中的一行記錄。有關鍵字表作為名字的一部分是不幸的，因為事務表不是一個關係型資料庫結構。事實上，Oracle核心開發人員將事務表條目稱作為slots(插槽)。

因為事務表被儲存在Oracle塊中，所以它能持有的slots數是有限的，這依賴於Oracle塊大小。如果事務表被填滿並且有新條目必須被新增，舊的非活動事務條目將被新條目覆蓋。如果事務是活動的且在事務表中沒有空間或者多個會話需要改變事務表，那麼將出現buffer busy waits等待事件。

如果資料庫沒有使用自動undo管理(AUM)，而是使用了傳統回滾段，那麼解決方案很簡單。就是建立額外的回滾段，這也將建立額外的事務表，因此分佈事務表活動。將會注意到buffer busy waits競爭消失。記住增加額外的回滾段直到buffer busy waits等待事件從top等待事件中消失。

大多數Oracle系統現在都利用Oracle AUM功能。預設情況下，Oracle嘗試給每個undo段只指派一個活動事務。如果每個undo段有一個活動事務並且在undo表空間中有可用空間，Oracle將會自動建立額外的undo段。這通常要小心buffer busy waits等待。然而，如果在undo表空間中沒有可用空間，多個事務將會被指派到一個undo段上，並且這最終將導致undo段頭塊競爭。解決方案就是向undo表空間增加另外的資料檔案，因此能讓Oracle建立額外的undo段，就是這麼簡單。

索引葉子塊解決方案
簡單地說，索引是有序結構。這種有序結構允許索引被用來執行快速搜尋。對索引的任何改變必須導致對有序結構進行維護。如果有序結構不被維護，搜尋將不能快速完成，並且索引將毫無價值且被損壞。因此有序結構是一個索引必須被維護的。這對效能有深遠的影響。

當索引排序後能使用快速搜尋，在高併發插入的情況下，它可能造成嚴重的效能問題。例如，假設一個索引是基於一個增長序列號(例如,1,2,3等等)，這通常叫作單調遞增值。如果一個表列包含這個序列號並且列被索引，在索引葉子塊中，各個索引項將彼此相鄰放置。因為索引必須維護順序，並且索引是基於升序排列的。

當許多會話併發插入記錄時會出現問題。當每個插入語句獲得下一個序列號時，索引條目很可能被物理地放置在相同的索引葉子塊中，或者不太可能被放置在相鄰的索引葉子塊中。如果併發數足夠，會話將需要等待另一個會話完成索引的葉子塊更改而等待很長時間。當會話等待時，它將post一個buffer busy waits等待事件，並且被等待的buffer是一個索引葉子塊。這種情況可能變得非常嚴重並且嚴重影響效能。

一種解決方案是讓Oracle對該列建立反向鍵索引。從DBA角度來說，序列號和以前一樣，但它的內部位元組被反轉了。因為索引條目必須以內部排序的方式來儲存，索引條目將可能被存放在不同的葉子塊中，這將有效的消除buffer busy waits等待。假設序列號用4個位元組來表示。因此前四個序列號(1,2,3,4)將以0001,0010,0011與0100來表示。如果這四個值被索引，因為索引排序結構必須被維護，它們將彼此相鄰而儲存。然而如果它們的位元組被反轉，那麼它們將以1000,0100,1100與0010來表示。因為它們必須被以排序的方式儲存索引，它們將很可能不會被儲存在相同的索引葉子塊中。事實，它們將分散到所有的索引葉子塊中。由於索引鍵反轉的結果因此buffer busy waits將會消除。

建立一個反向鍵索引非常怎麼著，使用與正常建立索引相同的DDL語句，只是簡單地在結尾處增加了一個reverse關鍵字。

SQL> create unique index special_cases_rk_u1 on special_cases (object_id) reverse;
Index created.

因為在每個索引葉子塊中有頻繁的索引插入操作，為了減少葉子塊的頻繁分裂，錄建立反向鍵索引時可以考慮增加pctfree 50的儲存引數。

反向鍵的優點與缺點
反向鍵的缺點是當解決了buffer busy wait問題時，可能對查詢效能有顯著的影響。假設我們基於序列號列建立的索引最佳化了查詢。但因為buffer busy waits問題，我們刪除了nonreversed索引，然後建立了一個reversed索引。現在這些排序的序列號被分散到所有的索引葉子塊中。高效能的索引範圍掃描可能不能執行地很好。事實上基於成本的最佳化器應該能識別並選擇另一種執行計劃。否則查詢可能潛在touch每個索引葉子塊和大量的資料塊。

當反向鍵索引可能增加插入併發與吞吐量時，也可能對之前已經最佳化過的查詢有一些負面影響。對於效能最佳化來說，你的職責就是找到一種效能折中的方案或實現一種創造性解決方案(比如對索引或表進行分割槽)來對插入與查詢操作來實現最大效能最佳化。