先建立一個(gè)監(jiān)控器
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)控器，否則。。。
跑你的測(cè)試后，在你的/home/db2inst1目錄下會(huì)生成一些evm文件
用下面指令獲取診斷信息：
db2evmon -db eos51 -evm SQLCOST>sqlcost1.txt
完成之后刪除你的監(jiān)控器
db2 "drop event monitor SQLCOST"
生成的采樣例子，從下面的例子中，你可以清除的看到SQL執(zhí)行的時(shí)間，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í)行時(shí)間和CPU消耗情況
[licl2]SQL的排序情況，可以看到這個(gè)SQL沒有排序，當(dāng)然也沒有排序溢出
[licl3]Bufferpool的使用情況，邏輯讀和物理讀的對(duì)比
del.icio.us Tags:
database ,
db2 ,
tuning