167
技術社區[雲棲]
深入剖析：關於cache buffers chains的經典案例處理詳解
盧文星
目前就職雲和恩墨，南區交付工程師，有超過8年超大型數據庫管理經驗，擅長Oracle數據庫性能優化與升級遷移。
某省稅務核心業務係統在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索引。
實施步驟：
dm_gy_swjg表創建兩個新的複合索引，複合列為（swjg_dm , xybz）、（swjg_dm, yxbz）。
使用hint將c85hrnmygbhz2、1tnz5r62b84gg分別指定使用新建立兩個複合索引，並獲取outline信息。
通過SQL PROFILE概要固定c85hrnmygbhz2、1tnz5r62b84gg執行計劃。
在優化調整實施後一周，客戶反饋，那3個sql在每小時3百萬執行量的情況下，已經無發現有明顯的latch: cache buffer chains等待，說明問題得以緩解。
原文發布時間為：2017-09-14
本文作者：盧文星
本文來自雲棲社區合作夥伴“數據和雲”，了解相關信息可以關注“數據和雲”微信公眾號
最後更新：2017-09-14 16:02:54