High Elapsed Time - with heavy I\O sync

Joel Goldstein

High Elapsed Time - with heavy I\O sync

As an addendum, this data shows a 20 minute elapsed time, and 4 minutes of CPU - and 80% of the 20 minutes elapsed is WAIT time.

Regards,
Joel

Joel Goldstein
Responsive Systems
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works......
Predicts Group Buffer Pool performance too!
www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: Rao A
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Wednesday, January 23, 2008 9:56 AM
Subject: [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



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