[DB2-L] High Elapsed Time - with heavy I\O sync

Rao A

[DB2-L] High Elapsed Time - with heavy I\O sync
Mike,
When you say lack of prefetch, you are expecting high numbers against "Other read i\o" or "asynchronous read i\o".

Rao


----- Original Message ----
From: Mike Bell <[login to unmask email]>
To: [login to unmask email]
Sent: Wednesday, January 23, 2008 11:31:09 AM
Subject: Re: [DB2-L] AW: [DB2-L] High Elapsed Time - with heavy I\O sync

The other interesting number is the lack of prefetch - normally I do not
expect to see only sync IO time - The two highest IO count datasets showed
1.8 sec and 5.8 sec so your device is running through cache fairly
effectively.. You might post the EXPLAIN data for the access path and see if
there is something there?

Mike
HLS Technologies

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: Wednesday, January 23, 2008 9:49 AM
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] High Elapsed Time - with heavy I\O sync

Hi

If you look at the Wait time (16:01 minutes), the biggest part for that is
waiting for synchronous I/O (11:41), the other main contributor is Not
accounted time (4:07), which is waiting for CPU you get almost your
complete elapsed time. So your query is I/O-bound and your CPU is a little
bit stressed by other apllications.

Mit freundlichen Grüßen
Walter Janißen


We are currently on DB2 V 7.. I know that my query is very I\O intensive
because of the selection criteria which qualifies for good chunk of data. I
see a huge difference in the Elapsed and the CPU time by the end of this
run. since my query has a cursor and order by in it, based on my past
experience I assume that it has to resolve the result set during the OPEN
stmt rather than the FETCH and that is where most of the time is being
spent. But I am not very sure why the difference in elapsed and cpu is that
high. My query runs for an hour of elapsed time and consumes only 10 minutes
of cpu time. Based on the simple statistics I gathered from the Omegamon
tool below, I would like to know if there is anything very obvious that I
was missing before I request my performance group to turn on more IFCID
traces like sort statistics etc..etc...


Class 1 Class 2
In Appl In DB2 Outside DB2
Elapsed time . . . . . . : 20: 22..190424 20:20.796821 1.393604
CPU time . . . . . . . . : 4:19.340976 4:19.070237 0.270739
TCB . . . . . . . . . : 4:19.340976 4:19.070237 0.270739
TCB - Stored Proc . . : 0.000000 0.000000
Parallel tasks . . . . : 0.000000 0.000000
Waiting time . . . . . . : N/A 16:01.726584
Suspension time . . . . : N/A 11: 53.790981
TCB . . . . . . . . . : N/A 11:53.790981
Parallel tasks . . . . : N/A 0.000000
Not accounted . . . . . : N/A 4: 07.935603

Time Event
Suspensions (Class 3) . . . . . . . . . : 11:53.790981 254801
Locks and latches . . . . . . . . . . : 0..219919 120




Locks and latches . . . . . . . . . .. : 0.219919 120
Synchronous I/O . . . . . . . . . . . : 11:41.631718 254063
Other read I/O . . . . . . . . . . . : 11.922312 613
Other write I/O . . . . . . . . . . . : 0.008895 3
Services task switch . . . . . . . . : 0.008137 2
Archive log (quiesce) . . . . . . . . : 0.000000 0
Archive log read . . . . . . . . . . : 0.000000 0
Drain lock . . . . . . . . . . . . . : 0.000000 0
Claim release . . . . . . . . . . . . : 0.000000 0
Page latch . . . . . . . . . . . . . : 0.000000 0
Stored procedures . . . . . . . . . . : 0.000000 0
Notify messages . . . . . . . . . . . : 0.000000 0
Global contention . .. . . . . . . . . : 0.000000 0

iio

+ --Synch I/O-- -Asynch
I/O--
+ Dataset Name Delay Count Delay
Count
+ -------------------------------------------- ------ ------ ------
------
+ DBX1.DSNDBC.DBRFP01.SBRF65.I0001.A001 6.9 21 1.3 90

+ DBX1.DSNDBC.DBRFP01.XBRF651.I0001.A001 10.1 27 4.2 28

+ DBX1.DSNDBC.DBRSP01.SBRS30.I0001.A001 1.8 7439 .4 7501

+ DBX1.DSNDBC.DBRSP01.SBRS32.I0001.A001 5.8 2619 .6
5740
+ DBX1.DSNDBC.DBRSP01.XBRS304.I0001.A001 4.0 542 .6 238

+ DBX1.DSNDBC.DBRSP01.XBRS321.I0001.A001 5.3 124 .0 0

+ DBX1.DSNDBC.DBRSP01.XBRS322.I0001.A001 3.6 1389 .0
0
+ DBX1.DSNDBC.DBRSP01.XBRS323.I0001..A001 6.4 115 1.0 64

+ DBX1.DSNDBC.DBRSP01.XBRS324.I0001.A001 4.7 198 1.0 134

+ DSNDBX1.DSNDBC.DSNDB01.DSNSPT01.I0001.A001 10.0 11 .0
0
+ DSNDBX1.DSNDBC.DSNDB01..SPT01.I0001.A001 6.2 24 .0 0

+ DSNDBX1.DSNDBC.DSNDB06.SYSSTR.I0001.A001 6.0 15 .5 481

====
===

====
===
>

Thanks
Rao

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms


____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms