Oracle數據庫緩沖區忙等待的原因解析

2024-08-29 13:50:57

字體：大中小

來源：轉載

供稿：網友

眾多Oracle有關問題中，其中最重要的一個是緩沖區忙等待(buffer busy wait)事件。緩沖區忙等待是I/O-bound Oracle系統中最常見的現象，尤其是在Oracle STATSPACK報告的前五個忙等待的讀(順序/分散)系統中，如前5個定時事件：　　　　　　　　　　　　　　　　　　　　　　　　　　

　　　　% 總和事件　　　　　　　　　　　　等待　　　　　時間(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視圖有助于監視緩沖區忙等待。當能夠正確地識別和修正緩沖區忙等待的原因時，你所努力的付出一定會得到回報的。

上一篇：跟蹤Oracle 9i的CPU和I/O使用代價

下一篇：Oracle中輕松取得建表和索引的DDL語句