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

7/8/2008来源:DB2教程人气:8709

首先,先建立一个监控器。

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