JAVA系统运行db2查询很慢_db2诊断系列之---捕获sql执行情况

作者：tacy lee

在应用使用过程中，我们经常会碰到应用响应时间很慢，甚至没有响应，但是应用服务器可能并不是很繁忙，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起来的   ；)

总结：

上面的方法简单总结了从数据库层面对应用的性能问题诊断，希望对大家有所帮助，对于数据库快照诊断问题的思路对于任意数据库通用

补充一个unix上脚本处理方式：

sqlsort.sh

awk 'BEGIN{RS="";FS="\n";ORS="\n"};/Statement text/{print $1, $21, $24}' $1 | awk '$5 > 0 {print "AvgTime:", $11/$5, "\t", $0}'| sort -n | head -n $2|awk '{print $0, "\n"}'

使用：#sqlsort.sh dysqlstate.out 10(显示Top ten)

posted on 2007-11-25 14:51 tacy lee 阅读(472) 评论(0)  编辑  收藏 所属分类: 性能相关 、数据库