您的位置:首頁技術文章
文章詳情頁

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

瀏覽:36日期:2023-11-09 14:51:58
首先,先建立一個監控器。

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 數據庫
国产综合久久一区二区三区