兩隻小虎,一隻豬

有時候在DB運行時會突然變得效能很差
透過抓取snapshot來看看到底是哪一支程式去影響到DB的效能

db2 update monitor switches using lock on uow on bufferpool on table on sort on statement off timestamp on

db2 connect to minerdb

today=$(date +"%Y-%m-%d-%H%M")

db2 get snapshot for all on minerdb >snap.log.$today

db2 connect reset

db2 update monitor switches using lock off statement off uow off bufferpool off table off sort off timestamp off

Snapshot 產生後可以透過幾個地方來看

1. 在Application Snapshot 看看是否有Idle 過久的Process

Application handle                         = 63
Application status                         = UOW Executing
Status change time                         =
Application code page                      = 1208
Application country/region code            = 88
DUOW correlation token                     = 10.92.40.24.33035.101122084055
Application name                           = w3wp.exe
Application ID                             = 10.92.40.24.33035.101122084055


Application idle time                      = 15 hours 20 minutes 14 seconds
CONNECT Authorization ID                   = USRMINER
Client login ID                            = NETWORK SERVICE
Configuration NNAME of client              = WINXP
Client database manager product ID         = SQL09019
Process ID of client application           = 4544
Platform of client application             = NT
Communication protocol of client           = TCP/IP

2. IO的情況是否有異常,像下面的例子表示Rows read數目很高,可是selected的數目卻是0,表示這隻Process的SQL 可能有問題

Rows deleted                               = 0
Rows inserted                              = 0
Rows updated                               = 0
Rows
Rows read                                  = 20437747817
Rows written                               = 258

3. CPU Total Time是否過高

Total User CPU Time used by agent (s)      = 8477.795026
Total System CPU Time used by agent (s)    = 941.824296

4. 在Database Lock Snapshot查看該Process 使用哪一些Table, 以這個例子來看,可以用Application handle = 63 來查看該Process 用了哪些Table