Hallo,
i have read "System Monitor Guide and Reference" but i can't understand the correlation between the time columns in the view sysibmadm.snapappl. All monitoring switches are on, database ist restarted after switching on.
Code:
db2 => get monitor switches@
Monitor Recording Switches
Switch list for db partition number 0
Buffer Pool Activity Information (BUFFERPOOL) = ON 2009-02-16 16.38.58.522301
Lock Information (LOCK) = ON 2009-02-16 16.38.58.522301
Sorting Information (SORT) = ON 2009-02-16 16.38.58.522301
SQL Statement Information (STATEMENT) = ON 2009-02-16 16.38.58.522301
Table Activity Information (TABLE) = ON 2009-02-16 16.38.58.522301
Take Timestamp Information (TIMESTAMP) = ON 2009-02-16 16.38.58.522301
Unit of Work Information (UOW) = ON 2009-02-16 16.38.58.522301
db2 =>
The database has been running 3 days.
I start this select
Code:
select
substr( sysibmadm.snapappl_info.appl_name, 1, 15 ) as appl_name,
( elapsed_exec_time_s + elapsed_exec_time_ms / 1000000.0 ) as elapsed_exec_time,
( ( agent_usr_cpu_time_s + agent_usr_cpu_time_ms / 1000000.0 + agent_sys_cpu_time_s + agent_sys_cpu_time_ms / 1000000.0 ) +
pool_read_time/1000.0 +
pool_write_time/1000.0 +
direct_read_time/1000.0 +
direct_write_time/1000.0 +
lock_wait_time/1000.0 +
total_sort_time/1000.0
) as total,
uow_elapsed_time_s + uow_elapsed_time_ms / 1000000.0 as last_uow_elapsed_time
from
sysibmadm.snapappl,
sysibmadm.snapappl_info
where
sysibmadm.snapappl.agent_id = sysibmadm.snapappl_info.agent_id
and sysibmadm.snapappl.dbpartitionnum = sysibmadm.snapappl_info.dbpartitionnum
@
and i get this output
Code:
ROWN APPL_NAME ELAPSED_EXEC_TIME TOTAL LAST_UOW_ELAPSED_TIME
----------- --------------- --------------------------------- --------------------------------- ---------------------------------
1 javaw.exe 1,39153500000 1,13506000000 76676,53878800000
2 db2bp.exe 0,00629800000 0,00733300000 0,00095600000
3 db2bp.exe 0,00065800000 0,00100400000 0,00169800000
4 javaw.exe 0,64181300000 0,34780500000 10,41601500000
5 db2bp.exe 1,94841400000 1,70693100000 5,05410000000
6 db2jccWebContai 2,46097700000 1,98464300000 0,01423100000
7 db2evmg_DB2DETA 0,00000000000 12,01010400000 0,00000000000
8 db2bp.exe 0,05954400000 0,03525500000 0,00882700000
9 DB2Manager.exe 89,85162300000 153,22321200000 0,00000000000
10 javaw.exe 0,10528600000 0,05672900000 33,93336600000
11 db2wlmd 0,00000000000 12,27401800000 0,00000000000
12 javaw.exe 149427,43188700000 0,07803200000 18282,64729000000
13 db2bp.exe 0,00000000000 0,00009700000 0,00000000000
14 db2taskd 0,00000000000 0,11893900000 0,00000000000
15 db2bp.exe 0,01345600000 4,45568500000 212,12985100000
16 javaw.exe 1,31335200000 0,33464600000 8,89302700000
17 db2bp.exe 0,00000000000 0,00009400000 0,00000000000
18 javaw.exe 120,45466600000 5,03707600000 164,94235600000
19 db2stmm 0,00000000000 63,14989100000 127496,73911200000
20 javaw.exe 0,36900700000 0,51107900000 64,44424700000
21 db2bp.exe 0,00327100000 0,00385700000 0,00178900000
22 db2bp.exe 0,00561000000 0,00692500000 0,00199600000
23 db2bp.exe 0,01898300000 0,02208600000 0,00546000000
24 javaw.exe 3,21625700000 3,22177700000 19061,94530600000
25 db2bp 0,12257200000 0,06780300000 0,01097200000
26 db2bp.exe 7,73104800000 5,91964600000 17,51592600000
26 record(s) selected.
db2 =>
as you can see some columns have large value in "elapsed_exec_time" that doesn't correlate with total time of all waits + cpu.
For example elapsed_time for javaw.exe is 149427 seconds and total is only 0,07 seconds (line 12).
some records have large total and small elapsed_time (line 11, 19).
the uow_elapsed_time doesn't correlate with elapsed_time for the whole session (line 1, 24). According to docs it is "The elapsed execution time of the most recently completed unit of work." How can it be higher as the session elapsed time?
My questions are:
1. What does elapsed_exec_time mean?
2. Why sum of all waits and cpu doesn't correlate with elapsed_exec_time?
3. What does uow_elapsed_time mean?
4. Does sysibmadm.snapappl works properly?
Thank you in advance
dBforums
>
correlation between time column in sysibmadm.snapappl
02-19-09, 14:40
Linear Mode
