首先,先建立一个监控器。
db2 "create event monitor SQLCOST for statements write to file '/home/db2inst1'"
再设置事务状态为打开。
db2 "set event monitor SQLCOST state=1"
注:1为打开,0为关闭,收集数据之后,记得关闭你的监控器,否则……
跑你的测试后,在你的/home/db2inst1目录下会生成一些evm文件
用下面指令获取诊断信息:
db2evmon -db eos51 -evm SQLCOST>sqlcost1.txt
完成之后删除你的监控器
db2 "drop event monitor SQLCOST"
生成的采样例子,从下面的例子中,你可以清除的看到SQL执行的时间,CPU消耗情况,排序是否溢出,BufferPool的使用情况,根据这些信息,SQL的执行效率一目了然:
26) Statement Event ...
Appl Handle: 336
Appl Id: C0A80421.O905.0ABDA5065446
Appl Seq number: 0657
Record is the result of a flush: FALSE
-------------------------------------------
Type : Dynamic
Operation: Execute
Section : 7
Creator : NULLID
Package : SYSSN300
Consistency Token : SYSLVL01
Package Version ID :
Cursor : SQL_CURSN300C7
Cursor was blocking: FALSE
Text : update WFProcessInst set relateData=? where processInstID= ?
-------------------------------------------
Start Time: 04/25/2007 14:57:19.402248
Stop Time: 04/25/2007 14:57:19.409622
Exec Time: 0.007374 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds [licl1]
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0 [licl2]
Rows read: 1
Rows written: 1
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 9
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 3
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0 [licl3]
SQLCA:
sqlcode: 0
sqlstate: 00000
------------------------------------------------------
[licl1]SQL执行时间和CPU消耗情况。
[licl2]SQL的排序情况,可以看到这个SQL没有排序,当然也没有排序溢出。
[licl3]Bufferpool的使用情况,逻辑读和物理读的对比。