【DB2】监控动态SQL语句

一、db2监控动态SQL（快照监控）

db2示例用户登陆后，使用脚本语句db2 get snapshot for all on dbname>snap.out

也可以使用db2 get snapshot for dynamic SQL on dbname>snap.out,此语句是只记录上一语句中的部分

Tips：如果需要监控尽可能多的SQL语句，建议增加DBM配置参数

以下是动态SQL快照

Dynamic SQL Snapshot Result

 Database name                      = QINDB

 Database path                      = /db2home/db2inst1/db2inst1/NODE0000/SQL00002/
 Number of executions               = 3 --编译次数，为累加值
 Number of compilations             = 1 --SQL语句编译的最长时间
 Worst preparation time (ms)        = 87
 Best preparation time (ms)         = 87
 Internal rows deleted              = Not Collected
 Internal rows inserted             = Not Collected
 Rows read                          = Not Collected
 Internal rows updated              = Not Collected
 Rows written                       = Not Collected
 Statement sorts                    = Not Collected
 Statement sort overflows           = Not Collected
 Total sort time                    = Not Collected
 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
 Buffer pool xda logical reads      = Not Collected
 Buffer pool xda physical reads     = Not Collected
 Buffer pool temporary xda logical reads    = Not Collected
 Buffer pool temporary xda physical reads   = Not Collected
 Total execution time (sec.microsec)= Not Collected --SQL语句的总执行时间
 Total user cpu time (sec.microsec) = Not Collected
 Total system cpu time (sec.microsec)= Not Collected
 Total statistic fabrication time (milliseconds) = Not Collected
 Total synchronous runstats time  (milliseconds) = Not Collected
 Statement text                     = select * from len.cust_info where cust_id like '%09' --SQL语句文本

Number of executetions：可以帮助找到最优的那些重要语句，它对于帮助计算语句的平均执行时间也很有用。

Rows read :可以帮助识别读取行数最多的动态SQL语句，如果读取行数最多，通常意味着进行全表扫描。

Total execution time:是将语句每次执行时间加起来得到的总时间，我们可以利用该时间除以Number of executrtions,可以得到平均执行时间。如果语句的平均执行时间很长，可能是因为表扫描或者出现锁等待。

基于上述使用语句db2 get snapshot for all on dbname生成的文本内容，我们可以使用grep对快照的输出内容执行搜索

例如：

<1> 识别是否存在死锁

grep -n "Deadlocks detected" snap.out |grep -v "= 0" |more

<2> 搜索执行最频繁的SQL语句

grep -n "Number of executions" snap.out | grep -v "= 0" | sort -k 6rn |more

<3> 查询总的执行时间

 grep -n "Total execution time" snap.out | grep -v "= 0.0"| sort -k 6nr| more

二、监控动态SQL(db2top)

使用db2top命令找出最频繁、最耗时的SQL(使用db2数据库用户登录)

db2top –d dbname 

按D，进入到SQL监控界面(如果没开大写，可以使用shift+d)

按z，倒序排序

输入排序列的序号，从0开始

按L，输入SQL的序列号，查看SQL明细

以下为示例操作：

（1）db2inst1@Linux:/qinys> db2top -d qindb

（2）按D进入

（3）按z倒序排序，此处我输入3，表示按照执行时间排序

（4）按L输入SQL序列号，查看SQL详细

从上图我们就可以查看SQL详细了