讲解捕获IBM DB2 SQL的执行快照的方法

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的使用情况，逻辑读和物理读的对比。