一、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

【DB2】监控动态SQL语句-风君雪科技博客


(2)按D进入

【DB2】监控动态SQL语句-风君雪科技博客


(3)按z倒序排序,此处我输入3,表示按照执行时间排序

【DB2】监控动态SQL语句-风君雪科技博客

(4)按L输入SQL序列号,查看SQL详细

【DB2】监控动态SQL语句-风君雪科技博客

【DB2】监控动态SQL语句-风君雪科技博客

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