【恩墨學院】深入剖析：關於cache buffers chains的經典案例處理詳解?

故障現象

 

某省稅務核心業務系統在7月13日11-12點出現業務處理非常緩慢，偶爾出現卡住不動。某業務功能處理時間是平時的10倍以上。

 

已知情況：

1、近兩週開始，在白天業務高峰期業務系統會出現處理緩慢

2、資料庫層面出現大量latch:cache buffers chains等待會話

3、每次問題大概持續了30分鐘後，latch:cache buffers chains等待消失，業務恢復正常

4、緩慢期間系統CPU使用率達到80%

故障分析

 

瞭解了以上資訊後，我們首先獲取了故障期間1節點的awr資訊，一小時的dbtime高達58,354 min。我們知道dbtime是資料庫例項會話花費時間的總和，那麼從dbtime上看，期間資料庫確實出現問題，會話發生了嚴重的等待。

 

 

Top等待事件中看到latch: cache buffers chains等待事件排列第1，佔據了dbtime 82%，等待次數1千萬以上，其平均等待時間達到238ms。其餘等待事件佔比很少。可以推斷cache buffers chains事件跟本次故障有極強的相關性。因此我們接下來從該等待事件著手進行分析。

 

 

latch cache buffers chains 定義

我們可以看到，一個latch保護多個hash bucket，一個hash bucket對應一個hash chain list,而hash chain list掛載了一個或者多個buffer header（注意：buffer header與Data block一一對應）。

 

也就是說，如果我要訪問某個block,我們首先獲得這個latch。當有多個會話同時訪問一個hash chain時，就會發生競爭。Latch cbc等待就這樣出現了。

 

 

 

以下情況下會發生 cache buffers chains等待：

1、同一個cache buffers chains下不同block被頻繁訪問，稱為hot chains

2、同一個cache buffers chains下同一個block被頻繁訪問，稱為hot block

 

一個塊的訪問過程

 

一個塊的訪問過程，一般會有2次cbc latch的獲取、釋放。

 

官方對cbc latch的描述。

 

 

以上內容簡單來說就是一個使用者程式獲取latch來掃描buffer ,系統根據塊地址和型別將資料塊分配在buffer連結串列中，每個buffer連結串列會有一個latch來保護。防止其掃描過程連結串列裡的塊發生變動。

 

分析問題原因

 

首先，透過dba_hist_active_session_history檢視還原故障期間發生等待的會話資訊，包括使用者、正執行的SQL等。

 

 

dba_hist_active_sess_history檢視查詢十幾分鍾都沒出結果。

 

檢視該檢視基表WRH$_ACTIVE_SESSION_HISTORY，其分割槽達到了8GB，檢視裡又關聯了WRH$_EVENT_NAME、WRM$_SNAPSHOT表，所以查詢長時間未完成。

 

 

直接查基表WRH$_ACTIVE_SESSION_HISTORY。基表沒有event_name列，需要透過event_id來查，獲取latch: cache buffers chains的event_id 。

 

 

五分鐘執行完，檢視結果後發現，cph4kgcn7frzs、c85hrnmygbhz2、1tnz5r62b84gg 這三個sql執行時發生了嚴重的Latch cbc等待。

 

檢視這三個sql的SQLTEXT，發現它們的子查詢SQL一樣的，子查詢訪問的表為swjg_dm。

 

 

再觀察SQL的執行計劃（3個SQL執行計劃基本一樣，此處以c85hrnmygbhz2展示），子查詢裡訪問表swjg_dm是透過索引UK_DM_GY_SWJG

 

 

回到awr報告，Segments by Logical Reads部分TOP1可以看到是DM_GY_SWJG表的索引UK_DM_GY_SWJG，該索引正好3個SQL執行計劃中都用到的索引UK_DM_GY_SWJG。說明它被頻繁訪問。

 

awr報告SQL Statistics，1小時內，每個SQL執行次數都超過3百萬。

 

 

在以下查詢中p1為LATCH: CBC的address。顯示有三個不同的SQL，說明3個SQL競爭同一個LATCH:CBC。

 

 

根據latch地址，到v$latch_children檢視中可以查詢該latch .因為例項沒有重啟過，cbc對應latch的address沒有變動（如果資料庫重啟，則latch的addr會重新分配，就查不到了）.

 

下面將P1轉化為p1raw與檢視addr關聯

 

 

可以看到該latch地址是一個cache buffers chains latch

 

目前，我們只確認熱塊在索引UK_DM_GY_SWJG，但具體哪個塊，我們還不確定。再根據latch的地址，透過x$hb聯合dba_objects檢視來檢視。

 

再透過x$hb聯合dba_objects檢視來檢視該CBC下中有哪些物件、塊等

 

 

看到熟悉的物件索引UK_DM_GY_SWJG，它是9號檔案的31109號塊在這個CBC中。該cbc裡只有1個UK_DM_GY_SWJG索引塊。那麼該塊是不是熱塊，該索引的其他塊在哪個cbc？

 

我們透過dump索引的結構來確認以下。

 

 

我們看到tree dump該索引有1個枝塊和3個葉塊，總共4個塊，該所索引有1千多條記錄。透過索引塊地址dba轉換後，看到9號檔案31109號塊是索引的葉子塊。

 

再次透過x$bh確認,該索引的4個塊分別在4個CBC中

 

 

分析結論

 

本次系統故障原因是，由於業務高峰期“cph4kgcn7frzs”、“c85hrnmygbhz2”、“1tnz5r62b84gg”這3個SQL執行頻繁，併發訪問索引UK_DM_GY_SWJG的9號檔案31109號，對應的latch addr:07000100F6A6C8E8，引起嚴重latch: cache buffers chains競爭阻塞，從而導致業務處理緩慢。

最佳化措施

 

官方提供的Solution方法

1、Splitting the buffer pool into multiple pools

我們的問題情況是熱塊不是熱鏈，不適合

 

2、Altering PCTFREE/PCTUSED to allow fewer rows per block, thus reducing contention on a certain block

對於索引記錄分塊，因為SQL訪問該索引塊中存在一定的熱記錄。所以對LATCH CBC問題的緩解效果不是很明顯。

 

3、Reducing the frequency the application accesses the object in question.

客戶確認，業務量上看該SQL不需要這麼多次執行，與開發商確認存程式在BUG，但開發商回覆bug短期內無法修復。

 

4、Tuning queries so that they won’t touch as many blocks. This will alleviate the problem with this latch if the query is heavily executed.

從前執行計劃上看，對索引UK_DM_GY_SWJG訪問，基本沒有最佳化空間。

 

5、 Avoid doing too many concurrent DML and Queries against the same row/block. Too many concurrent DML and Queries against the same block can result in multiple versions of the block created in the same cache buffer chain. Longer chains means more time spent by the session traversing through the chain while holding on to the latch.

不存在dml，主要query SQL

 

我們問題的現狀：

 

 

最佳化構思：

 

最佳化措施

實現方法：

 

我們可以在表dm_gy_swjg 的swjg_dm列，再建立兩個索引（複合索引），swjg_dm為字首列，透過SQL PROFILE概要檔案將c85hrnmygbhz2、1tnz5r62b84gg索引訪問分別指向新建的兩個複合索引，cph4kgcn7frzs不變動，則使用原UK_DM_GY_SWJG索引。

 

實施步驟：

 

1.  dm_gy_swjg表建立兩個新的複合索引，複合列為（swjg_dm , xybz）、（swjg_dm, yxbz）。

2.  使用hint將c85hrnmygbhz2、1tnz5r62b84gg分別指定使用新建立兩個複合索引，並獲取outline資訊。

 

透過SQL PROFILE概要固定c85hrnmygbhz2、1tnz5r62b84gg執行計劃。

 

最佳化結果

 

在最佳化調整實施後一週，客戶反饋，那3個sql在每小時3百萬執行量的情況下，已經無發現有明顯的latch: cache buffer chains等待，說明問題得以緩解。

 
恩墨學院隸屬於雲和恩墨（北京）資訊科技有限公司，致力於提供專業高水準的與大資料培訓服務，挖掘培養大資料與資料庫人才。恩墨學院提供包括個人實戰技能培訓、個人認證培訓、企業內訓在內的全方位大資料和資料庫技術培訓。ACE級別超強師資，配備專業實驗室，沉浸式學習與訓練，專業實驗室、配備專業助教指導訓練。能迅速融入專家圈子，業內資源豐富，迅速積累職場人脈。課程包括：班、Oracle 、Oracle OCP考試等。