DB2中通过操作系统进程找对应的sql
db2的进程很多被封装到了db2sysc成为线程,当db2sysc CPU占用高是怎么判断问题所在呢?
在Oracle中是v$process,v$session,v$sql来根据操作系统进程ID找。
DB2中也挺简单!
db2pd 可以查看到线程一级,并且可以看到cpu的占用
下面描述怎样用db2pd来找到你要找的应用连接。
1)开一个db2 => connect to sample. 并执行了select count(*) from imptest.下面就找出它。
2) db2pd -edu的输出中 USR,SYS为CPU使用率。而EDU ID可以对应到 -applications的EDUID,-applications的AnchID 对应到Dynamic SQL Statements里能找到执行的SQL.
从下面的输出可以看到连接SAMPLE的只有一个agent,找出它的EDUID为32.现实中可能会有很多连接agent,那就要找usr或sys高的EDUID.
[db2inst@linux01 ~]$ db2pd -database sample -edu -applications -dynamic
Option -edu is an instance scope option. The database option has been ignored.
Database Partition 0 -- Active -- Up 0 days 00:16:25 -- Date 02/12/2011 12:34:01
List of all EDUs for database partition 0
db2sysc PID: 3839
db2wdog PID: 3837
db2acd PID: 3869
EDU ID TID Kernel TID EDU Name USR SYS
=========================================================================================================
59 3060788112 4998 db2agent (idle) 0 0.000000 0.020000
58 3061836688 4997 db2agntdp (SAMPLE ) 0 0.000000 0.020000
57 3067079568 4957 db2evmti (TEST_MONITOR) 0 0.000000 0.030000
56 3064982416 4956 db2evmgi (DB2DETAILDEADLOCK) 0 0.000000 0.000000
55 3068128144 4955 db2wlmd (SAMPLE) 0 0.000000 0.000000
54 3062885264 4954 db2taskd (SAMPLE) 0 0.000000 0.010000
53 3066030992 4953 db2stmm (SAMPLE) 0 0.000000 0.000000
52 3063933840 4952 db2pfchr (SAMPLE) 0 0.000000 0.000000
51 3069176720 4951 db2pfchr (SAMPLE) 0 0.000000 0.000000
50 3070225296 4950 db2pfchr (SAMPLE) 0 0.000000 0.000000
49 3071273872 4949 db2pclnr (SAMPLE) 0 0.000000 0.000000
48 3076516752 4948 db2dlock (SAMPLE) 0 0.000000 0.000000
47 3075468176 4947 db2lfr (SAMPLE) 0 0.000000 0.000000
46 3072322448 4946 db2loggw (SAMPLE) 0 0.000000 0.000000
45 3073371024 4945 db2loggr (SAMPLE) 0 0.000000 0.000000
32 3074419600 4059 db2agent (SAMPLE) 0 0.030000 0.450000
19 3077565328 3971 db2agent (instance) 0 0.030000 0.740000
18 3078613904 3970 db2agent (instance) 0 0.000000 0.010000
17 3079662480 3958 db2agent (instance) 0 0.000000 0.000000
16 3080711056 3867 db2resync 0 0.000000 0.000000
15 3081759632 3864 db2tcpcm 0 0.000000 0.000000
14 3082808208 3863 db2ipccm 0 0.000000 0.010000
13 3083856784 3862 db2licc 0 0.000000 0.000000
12 3084905360 3858 db2thcln 0 0.000000 0.000000
11 3085953936 3857 db2alarm 0 0.000000 0.000000
1 3054496656 3850 db2sysc 0 0.040000 0.330000
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:05:57 -- Date 02/12/2011 12:34:01
Applications:
Address AppHandl [nod-index] NumAgents CoorEDUID Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid WorkloadID WorkloadOccID
0x10AB36A0 26 [000-00026] 1 32 UOW-Waiting 0 0 33 1 *LOCAL.db2inst.110212042804 1 1
0x10AEA530 31 [000-00031] 1 57 ConnectCompleted 0 0 0 0 *LOCAL.DB2.110212042809 0 0
0x10AE6E40 30 [000-00030] 1 56 ConnectCompleted 0 0 0 0 *LOCAL.DB2.110212042808 0 0
0x10AE3750 29 [000-00029] 1 55 ConnectCompleted 0 0 0 0 *LOCAL.DB2.110212042807 0 0
0x10AE0060 28 [000-00028] 1 54 ConnectCompleted 0 0 0 0 *LOCAL.DB2.110212042806 0 0
0x10ABC910 27 [000-00027] 1 53 ConnectCompleted 0 0 0 0 *LOCAL.DB2.110212042805 0 0
===删减了一部分=======
Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:05:57 -- Date 02/12/2011 12:34:01
Dynamic Cache:
Current Memory Used 235144
Total Heap Size 1271398
Cache Overflow Flag 0
Number of References 1
Number of Statement Inserts 5
Number of Statement Deletes 4
Number of Variation Inserts 1
Number of Statements 1
Dynamic SQL Statements:
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0xAEC1DA70 33 1 1 1 1 1 select count(*) from imptest
===删减了一部分=====