db2 "create event monitor SQLCOST for statements write to file '/home/db2inst1'"

再設(shè)置事務(wù)狀態(tài)為打開。

db2 "set event monitor SQLCOST state=1"

注：1為打開，0為關(guān)閉，收集數(shù)據(jù)之后，記得關(guān)閉你的監(jiān)控器，否則……

跑你的測試后，在你的/home/db2inst1目錄下會生成一些evm文件

用下面指令獲取診斷信息：

db2evmon -db eos51 -evm SQLCOST>sqlcost1.txt

完成之后刪除你的監(jiān)控器

db2 "drop event monitor SQLCOST"

生成的采樣例子，從下面的例子中，你可以清除的看到SQL執(zhí)行的時間，CPU消耗情況，排序是否溢出，BufferPool的使用情況，根據(jù)這些信息，SQL的執(zhí)行效率一目了然：

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執(zhí)行時間和CPU消耗情況。

[licl2]SQL的排序情況，可以看到這個SQL沒有排序，當(dāng)然也沒有排序溢出。

[licl3]Bufferpool的使用情況，邏輯讀和物理讀的對比。