| 導購 | 订阅 | 在线投稿
分享
 
 
 

講解捕獲IBM DB2 SQL的執行快照的方法

來源:互聯網  2008-07-09 05:37:12  評論

首先,先建立一個監控器。

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的使用情況,邏輯讀和物理讀的對比。

首先,先建立一個監控器。 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的使用情況,邏輯讀和物理讀的對比。
󰈣󰈤
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有