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

Walter Janißen

[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.

Bitte richten Sie, falls Sie es nicht schon tun, in Zukunft alle E-Mails nicht mehr an Einzelpersonen, sondern
immer an das Postfach "L-DBA-Gruppenpostfach".
Ansonsten kann sich die Bearbeitung Ihrer E-Mail bedingt durch Urlaub, Gleittag, Krankheit, Projektarbeit in Köln,
ZEITplus o.ä. verzögern.
Vielen Dank!

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
D-40477 Düsseldorf
Tel.: +49 211 477-2928
Fax: +49 211 477-2615
mailto:[login to unmask email]
http:// < http:/// > www.itergo.com

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf | Handelsregister: Amtsgericht Düsseldorf, HRB 37996





_____

Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Rao A
Gesendet: Mittwoch, 23. Januar 2008 15:57
An: [login to unmask email]
Betreff: [DB2-L] High Elapsed Time - with heavy I\O sync


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

_____

Looking for last minute shopping deals? Find <http://us.rd.yahoo.com/evt=51734/* http://tools.search.yahoo.com/newsearch/category.php?category=shopping > them fast with Yahoo! Search.
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org <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 < http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member < http://www.idug.org/lsms > Services

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> www.idug.org 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 < http://www.idug.org/lsconf > IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on < http://www.idug.org/lsms > Member Services

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