High Elapsed Time - with heavy I\O sync

Larry Kintisch

High Elapsed Time - with heavy I\O sync
Hi Rao,

You have a classic bad index issue. Synchronous I/O takes from
100-times to 1000-times longer elapsed time and 10-times as much CPU
as sequential access in the correct index. If you are in the USA
please call me at home 845-353-0885 for some guidance.

In any case, part of the "Cost-Saving Index Design" 2-day class
that I teach [see: www.DBIndexDesign.com ] is also covered by IBM
course CV960 [was CF961] and by the Redbook "SG24-7134-00 DB2 UDB for
z/OS: Design Guidelines for High Performance and Availability" that
you may download from:

http://www.redbooks.ibm.com/abstracts/sg247134.html?Open

Hope this helps! Larry Kintisch ABLE Information Services

At 09:56 AM 1/23/2008, you wrote:
>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?
> <http://us.rd.yahoo.com/evt=51734/* http://tools.search.yahoo.com/newsearch/category.php?category=shopping > Find
>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
> < 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 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

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