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

Joel Goldstein

[DB2-L] High Elapsed Time - with heavy I\O sync
I think we would like to see the actual Getpage activity by type, and bufferpool activity.
Coming at Mike's statement from a different angle - the implication is that there is little sequential access of pages
but we don't know exactly what is taking place without bufferpool statistics.

The 1.8 Ms IO is good cache hit, since a cache hit in 1 Ms or a tick less. 5.8 Ms is not a good cache hit, it's a rather high
cache miss rate.

Other read IO in an accounting report is typically waiting for your own prefetch, same as asynchronous read IO.

Based upon your original description of the job, it's highly unusual to see all Synch IO, and almost no prefetch.
Of course, we also don't see any sorting information in the provided data.....

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 11:39 AM
Subject: Re: [DB2-L] AW: [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


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