buffer busy waits引起的會話突增

某天，客戶反映其監控平臺發現其一套資料庫7月20日及24日在早晨7：03分和8：09分兩個時間段節點1出現會話數突增情況，持續時間較短，問題時間段應用並未受到影響，客戶希望幫其查詢原因。

環境為11.2.0.4的雙節點RAC。

透過收集的ASH資訊我們可以在7月20日的07:03分和7月24日的08: 09分看到會話出現突增情況，其他時間段會話相對較平穩，問題時間點過後，會話數再次恢復平穩。

7月20日：

7 月 24 日：

在對問題時間段等待事件進行檢視，該時間段內出現大量的 ”buffer busy waits” 等待事件，等待的會話並無 blocking session ，語句型別均為 INSERT ， SQL_ID 均為： 8qy4f5hpsrmu4 ，而物件為 98866 ，在 7 月 24 日，問題時間段又伴有少量的 ”latch: cache buffers chains” 和大量的 ”enq: HW -contention” 。

7 月 20 日：

7 月 24 日：

對等待事件進行彙總後，等待次數排名第一位的等待事件均為 ”buffer busy waits” ，如下：

7 月 20 日

7 月 24 日

透過上面的資訊在表象中看到是由於系統中大量併發 INSERT 語句同時向 98866 物件插入資料，而引起的會話數突增，且會話均等待 ”buffer busy waits” 。  

那系統中為何同時出現大量 INSERT 語句的會話？該時間點內應用確實發出如此多的 INSERT 語句？而又同時向相同的物件中插入資料產生的 ”buffer busy waits” ？還是由於 ”buffer busy waits” 而引起的大量會話出現阻塞？

根據應用反饋，系統中的資料插入較平穩，問題時間段業務量並未發生變化，這樣可以排除第一種可能性。  

先看何為 ”buffer busy waits” ， “buffer busy waits” 等待事件的發生情況為當會話以獨佔模式持有 buffer pin 鎖時，其他會話以非相容模式申請 buffer pin 鎖，此時申請 buffer pin 鎖的會話產生的等待事件為 buffer busy waits 。 引起系統 ”bufferbusy wiats” 的原因很多，常見的情況有如下情況：

1. 大量的併發 DML 語句，修改相同的資料塊

2. DML 語句慢，導致以排他模式持有 bufferpin 時間過長（ DML 語句慢的原因可能由於系統 redo 量過高或者 lgwr 慢等原因導致）  

另外，透過對等待事件所發生位置進行統計發現 ”buffer busy waits” 均發生在少數的資料塊中。

7 月 20 日

7 月 24 日

大量的會話又為何向同一個資料塊中插入資料？透過對發生大量 ”buffer busy waits” 的資料塊進行 dump ，有如下發現 :

218/4161664

19/8250

其中 218/4161664 為 L1 ， 19/8250 為段頭， L1 與段頭居然出現了大量的 ”buffer busy waits” ，如果對資料插入時的機制有所瞭解，問題原因其實已經展現出來，在使用 ASSM 的段空間管理方式下，正常插入語句在向段中插入資料時，將會根據會話的 pid 資訊計算 hash 值 n ，根據計算結果在 L3/L2 中選擇第 n 個 L1 ，這裡需要注意插入時所選擇的 L1 是在高水位線之下的 L1 ，在根據 pid 計算 hash 值將資料插入到 L1 中的第 n 個資料塊裡，會話不同，插入的資料將分散到不同的塊中，當 L1 中指向的資料塊使用率出現變化 (25% 50% 75% FULL) 時將會修改 L1 中對該資料塊空間使用情況標記位的修改，因其為修改，將以獨佔模式持有 L1 段頭的 buffer pin 鎖，在問題時間段內同時伴有大量的高水位推進 ”enq: HW - contention” 等待事件，段的高水位線記錄在段頭，這樣也解釋了為什麼段頭會出現大量的 ”buffer busy waits” ，其實根據上面的資訊基本可以分析出是由於系統中持續插入資料，由於高水位線低 ( 高水位線的推進是以 L1 中指向的塊的數量進行推進 ) ，大量的資料插入時聚集在高水位線下 L1 中指向的資料塊，而引起大量的“ buffer busy waits ”，導致會話突增，這種問題較少見，如果需要避免該問題，可以採用手工推高高水位的方式，這樣可選擇的 L1 數量，資料塊的數量也隨著增多，避免插入的資料塊過於集中。

| 作者簡介

楊禹航·沃趣科技高階資料庫技術專家

熟悉Oracle資料庫內部機制，豐富的資料庫及RAC叢集層故障診斷、效能調優、OWI、資料庫備份恢復及遷移經驗。