DB2診斷系列之捕獲SQL執行情況

在DB2應用使用過程中，我們經常會碰到應用響應時間很慢，甚至沒有響應，但是應用服務器可能并不是很繁忙，cpu利用率也非常低，引起這種狀況的原因有很多種，比如環境問題，應用資源泄漏，數據庫原因等等，本文主要是從一次應用性能診斷過程來談談如何通過數據庫診斷應用性能問題。

問題：

測試過程中發現應用中某個跳轉頁面執行時間比較長，系統壓力不大，cpu利用很低，該頁面需要從cache中取數據，第一次的時候加載cache(從數據庫中查詢回數據并cache)。

診斷：

頁面邏輯比較簡單，我們先用loadrunner模擬并發測試一下這個頁面，然后再數據庫端捕獲sql執行情況。

1、打開db2監控開關

#db2 connect to eos

#db2 update monitor switches using statement on

#db2 reset monitor all

2、幾分鐘之后，我們收集sql統計快照

#db2 get snapshot for dynamic sql on eos > dysqlstatus.out

現在統計信息已經存放在dysqlstatus.out中，你可以使用任意方便的文本處理工具查看，我一般用windows上的gvim來處理，打開dysqlstatus.out

Number of executions = 1

Number of compilations = 1

Worst preparation time (ms) = 2

Best preparation time (ms) = 2

Internal rows deleted = 0

Internal rows inserted = 0

Rows read = 2

Internal rows updated = 0

Rows written = 0

Statement sorts = 0

Statement sort overflows = 0

Total sort time = 0

Buffer pool data logical reads = Not Collected

Buffer pool data physical reads = Not Collected

Buffer pool temporary data logical reads = Not Collected

Buffer pool temporary data physical reads = Not Collected

Buffer pool index logical reads = Not Collected

Buffer pool index physical reads = Not Collected

Buffer pool temporary index logical reads = Not Collected

Buffer pool temporary index physical reads = Not Collected

Total execution time (sec.ms) = 0.000377

Total user cpu time (sec.ms) = 0.010000

Total system cpu time (sec.ms) = 0.000000

Statement text = select ACTIVITYDEFID,ACTIVITYINSTID from wfworkitem where PROCESSINSTID=104199 and CURRENTSTATE = 4

......

簡單說一下vi中的處理

:g!/Total execution time/d

只保留文本中的sql執行時間，我們要按照執行時間來排序

通過vim的visual功能選擇執行時間塊(等號后面的數字)，然后排序

Total execution time (sec.ms) = 0.050590

Total execution time (sec.ms) = 0.000170

Total execution time (sec.ms) = 0.000247

Total execution time (sec.ms) = 0.000292

Total execution time (sec.ms) = 0.000474

Total execution time (sec.ms) = 0.000330

Total execution time (sec.ms) = 0.000348

Total execution time (sec.ms) = 0.000279

Total execution time (sec.ms) = 0.000385

Total execution time (sec.ms) = 0.000296

Total execution time (sec.ms) = 0.000261

Total execution time (sec.ms) = 0.000195

Total execution time (sec.ms) = 0.000226

Total execution time (sec.ms) = 0.000227

Total execution time (sec.ms) = 0.000193

......

:'<,'>!sort

排序后的結果(部分)

Total execution time (sec.ms) = 2.027776

Total execution time (sec.ms) = 2.203624

Total execution time (sec.ms) = 2.504677

Total execution time (sec.ms) = 2.951256

Total execution time (sec.ms) = 3.119875

Total execution time (sec.ms) = 3.303277

Total execution time (sec.ms) = 3.303517

Total execution time (sec.ms) = 4.017133

Total execution time (sec.ms) = 4.043329

Total execution time (sec.ms) = 4.252125

Total execution time (sec.ms) = 4.400952

Total execution time (sec.ms) = 4.606765

Total execution time (sec.ms) = 5.208087

Total execution time (sec.ms) = 5.778598

Total execution time (sec.ms) = 8.117470

Total execution time (sec.ms) = 9797.905136

可以看到最長時間的sql total執行時間耗費了3797.905123s.

現在我們到dysqlstatus.out中去找這條語句

Number of executions = 4602

Number of compilations = 4294967295

Worst preparation time (ms) = 2

Best preparation time (ms) = 2

Internal rows deleted = 0

Internal rows inserted = 0

Rows read = 2963688

Internal rows updated = 0

Rows written = 0

Statement sorts = 0

Statement sort overflows = 0

Total sort time = 0

Buffer pool data logical reads = Not Collected

Buffer pool data physical reads = Not Collected

Buffer pool temporary data logical reads = Not Collected

Buffer pool temporary data physical reads = Not Collected

Buffer pool index logical reads = Not Collected

Buffer pool index physical reads = Not Collected

Buffer pool temporary index logical reads = Not Collected

Buffer pool temporary index physical reads = Not Collected

Total execution time (sec.ms) = 9797.905136

Total user cpu time (sec.ms) = 9.290000

Total system cpu time (sec.ms) = 1.230000

Statement text = select * from XXXX_T_CNFACTIVITYDEF

這條語句總共執行了4602次，平均每次的執行時間2S，而且這些數據應該是被cache起來的 ;)

總結：

上面的方法簡單總結了從數據庫層面對應用的性能問題診斷，希望對大家有所幫助，對于數據庫快照診斷問題的思路對于任意數據庫通用