緩衝區忙等待是I/O-bound Oracle系統中比較常見的現象，特別是在Oracle STATSPACK報告的前五個忙等待的讀(順序/分散)系統中……
緩衝區忙等待是I/O-bound Oracle系統中比較常見的現象，特別是在Oracle STATSPACK報告的前五個忙等待的讀(順序/分散)系統中，如前5個定時事件：[@more@]
% 總和事件 等待 時間(s) 消逝時間
------------------ ------------ ----------- -----------
db檔案順序讀 2,598 7,146 48.54
db檔案分散讀 25,519 3,246 22.04
庫緩衝區載入死鎖 673 1,363 9.26
CPU時間 2,154 934 7.83
日誌檔案平行寫 19,157 837 5.68 |
減輕緩衝區忙等待的主要方式是減少系統中的I/O，這可以透過SQL使用更少的塊讀(block reads，比如新增索引)的方式得以實現。即使對於一個比較大的db_cache_size，我們也可以減少緩衝區忙等待的時間。
為了能夠檢視整個系統的等待事件，我們可以查閱v$system_event效能檢視。這一效能檢視提供了等待事件的名稱，等待事件與時間的總和，以及每一事件的平均等待時間。
可以透過v$waitstat檢視來查詢導致等待的緩衝區的型別。這一檢視列出了每一緩衝區型別的等待，COUNT是類所有的等待總和，TIME是這一類所有等待的時間總和，如下所示：
select * from v$waitstat;
類 COUNT TIME
------------------ ---------- ----------
data block 1961113 1870278
segment header 34535 159082
undo header 233632 86239
undo block 1886 1706 |
當一個session訪問緩衝區的塊時，就有可能產生緩衝忙等待。這一緩衝區忙等待的產生可能由以下的原因造成的：
塊可能被其它的session讀到緩衝區，所以session必須等待塊的讀入結束。
session可能有與等待的session查詢不協調的緩衝塊。
由於緩衝區忙等待是由不同特定的塊之間的競爭而造成的，所以只能透過識別哪些塊發生衝突和衝突產生的原因，你才有可能做出判斷，相應的調整包括識別和消除塊競爭的原因。
v$session_wait效能檢視，提供了識別等待產生原因的方法。
v$session_wait檢視的列代表的緩衝區忙等待事件如下：
P1—與等待相關的資料檔案的全部檔案數量。
P2—P1中的資料檔案的塊數量。
P3—描述等待產生原因的程式碼。
這裡是一個這些值的Oracle資料詞典查詢:
select
p1 "File #".
p2 "Block #",
p3 "Reason Code"
from
v$session_wait
where
event = 'buffer busy waits'; |
轉自：
如果以上查詢的結果顯示一個塊在忙等待，以下的查詢將顯示這一塊的名稱和型別：
select
owner,
segment_name,
segment_type
from
dba_extents
where
file_id = &P1
and
&P2 between block_id and block_id + blocks -1; |
一旦這一塊被識別，v$segment_statistics效能檢視促使塊水平統計的實時監控。這一過程使得DBA識別與獨立列表與索引有關的問題。
我們也可以查詢dba_data_files以確定捲入等待的檔案的file_name，方法是使用v$session_wait中的P1。
從v$session_wait中查詢P3(原因編碼)的值可以知道session等待的原因。原因編碼的範圍從0到300，並可以解碼。
在一個SCUR或XCUR緩衝區產生且沒有結束的改變。
0 塊被讀入緩衝區。
100 我們想要NEW(建立)一個塊，但這一塊當前被另一session讀入。
110 我們想將當前塊設為共享，但這一塊被另一session讀入，所以我們必須等待read()結束。
120 我們想獲得當前的塊，但其他人已經將這一塊讀入緩衝區，所以我們只能等待他人的讀入結束。
130 塊被另一session讀入，而且沒有找到其它協調的塊，所以我們必須等待讀的結束。緩衝區死鎖後這種情況也有可能產生。所以必須讀入塊的CR。
200 我們想新建立一個block，但其他人在使用，所以我們只好等待他人使用結束。
210 Session想讀入SCUR或XCUR中的塊，如果塊交換或者session處於非連續的TX模式，所以等待可能需要很長的時間。
220 在緩衝區查詢一個塊的當前版本，但有人以不合法的模式使用這一塊，所以我們只能等待。
230 以CR/CRX方式獲得一個塊，但塊中的更改開始並且沒有結束。
231 CR/CRX掃描找到當前塊，但塊中的更改開始並且沒有結束。
原因編碼：正如我在開始時所說的那樣，緩衝區忙等待是I/O
bound系統中最常見的現象。資料塊等待導致的I/O競爭通常是由當掃描相同的索引時，多個session重複讀入相同的塊。在這樣的情況
下，session 1快速掃描緩衝區的塊，然後塊從磁碟被讀入。當session
1等待磁碟讀完成過程中，其它塊掃描相同的索引，並很快捕捉session 1，並想從磁碟上讀入相同的塊。由此產生了緩衝區忙等待。
以下規則有助於解決提及的當處於競爭時的情況：
資料塊競爭—透過改變PCTFREE或者PCTUSED值來識別和消除程式中的HOT塊，以減少資料塊的數量。
Freelist塊競爭—增加FREELISTS值，當使用Parellel伺服器時，一定確保每一事例有自己的FREELIST GROUPs。
Segment header競爭—增加FREELISTS值，並使用FREELIST GROUPs。
Undo header塊—增加回滾段(rollback segments)的數量。
註釋：緩衝區忙等待的識別和解決是比較複雜和棘手。Oracle提供了v$segment_statistics檢視有助於監視緩衝區忙等待。當你能夠正確地識別和修正緩衝區忙等待的原因時，你的付出肯定會得到回報的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/789833/viewspace-1039991/，如需轉載，請註明出處，否則將追究法律責任。