V5 for OS/390 list prefetch

vcei

V5 for OS/390 list prefetch
First, thanks to Micheal Hannan and Isaac Yassin for their answer on our MLT
IX email, some weeks ago. We haven`t had time yet to implement the
"Multi-Column Cardinalities" statistics, trying to get rid of some stupid
multi-index access.

Even with single index access, we have some badly performing IMS
transactions. Though usage is made of the best possible index...

Now, in www.gabrielledb2.com/SM.PDF ("Sorts and List Prefetch" in
http://www.gabrielledb2.com/coursem.htm ) we found:
"List prefetch reads and random (asynchronous) writes are limited to a 150
page range
Avoids long I/ O chains that span more than 10 cylinders for a single I/ O
request
Avoids others having to wait too long for I/ O to the DASD device"

In V5 Admin.Guide, 5.4.1.10 we read something simular, but only for updates:
"The maximum number of pages written per write I/O is 32, subject to a
limiting scope of 150 pages."

Now, what are the consequences of this "150 pages range" in terms of
performance ? Index access gives us a maximum of 1000 RIDs, that are
GROUPed together to about 10 to 15 %. (indicating some bad DB-design :-) )
And that takes much more time than it should... We fear that the
corresponding rows are too widely spread over the data pages.

Thanks,
JP



Venkat (PCA) Pillay

Re: V5 for OS/390 list prefetch
(in response to vcei)
List prefetch is mostly good if it can find atleast 2-3 pages in 150 page
range. List prefetch is especially good for badly clustered index. If you
have doubt about access path then disable list-prefetch (by using optimize
for 1 row etc.) and compare performance with random I/O. Make sure pages are
flushed out of buffers when you do comparison.

> -----Original Message-----
> From: vcei [SMTP:[login to unmask email]
> Sent: Tuesday, December 14, 1999 6:33 AM
> To: [login to unmask email]
> Subject: V5 for OS/390 list prefetch
>
> First, thanks to Micheal Hannan and Isaac Yassin for their answer on our
> MLT
> IX email, some weeks ago. We haven`t had time yet to implement the
> "Multi-Column Cardinalities" statistics, trying to get rid of some stupid
> multi-index access.
>
> Even with single index access, we have some badly performing IMS
> transactions. Though usage is made of the best possible index...
>
> Now, in www.gabrielledb2.com/SM.PDF ("Sorts and List Prefetch" in
> http://www.gabrielledb2.com/coursem.htm ) we found:
> "List prefetch reads and random (asynchronous) writes are limited to a 150
> page range
> Avoids long I/ O chains that span more than 10 cylinders for a single I/
> O
> request
> Avoids others having to wait too long for I/ O to the DASD device"
>
> In V5 Admin.Guide, 5.4.1.10 we read something simular, but only for
> updates:
> "The maximum number of pages written per write I/O is 32, subject to a
> limiting scope of 150 pages."
>
> Now, what are the consequences of this "150 pages range" in terms of
> performance ? Index access gives us a maximum of 1000 RIDs, that are
> GROUPed together to about 10 to 15 %. (indicating some bad DB-design :-)
> )
> And that takes much more time than it should... We fear that the
> corresponding rows are too widely spread over the data pages.
>
> Thanks,
> JP
>
>
>
>
>



Roger Miller

Re: V5 for OS/390 list prefetch
(in response to Venkat (PCA) Pillay)
Two points to consider:

150 CI's is a bit less than 1 cylinder on 3390 or 1 cylinder on 3380.
There are
15 tracks per cylinder on both, 12 records per track on 3390, 10 on
3380.

This limit is most useful if the device is physical, like the old
3390, but the RAID
disks are virtual. Still, there are situations where the disk
controller performance
can take advantage of the smaller size, as in prestaging.

Roger Miller


vcei <[login to unmask email]>@RYCI.COM> on 12/14/99 03:32:49 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: V5 for OS/390 list prefetch



First, thanks to Micheal Hannan and Isaac Yassin for their answer on our
MLT
IX email, some weeks ago. We haven`t had time yet to implement the
"Multi-Column Cardinalities" statistics, trying to get rid of some stupid
multi-index access.

Even with single index access, we have some badly performing IMS
transactions. Though usage is made of the best possible index...

Now, in www.gabrielledb2.com/SM.PDF ("Sorts and List Prefetch" in
http://www.gabrielledb2.com/coursem.htm ) we found:
"List prefetch reads and random (asynchronous) writes are limited to a 150
page range
Avoids long I/ O chains that span more than 10 cylinders for a single I/
O
request
Avoids others having to wait too long for I/ O to the DASD device"

In V5 Admin.Guide, 5.4.1.10 we read something simular, but only for
updates:
"The maximum number of pages written per write I/O is 32, subject to a
limiting scope of 150 pages."

Now, what are the consequences of this "150 pages range" in terms of
performance ? Index access gives us a maximum of 1000 RIDs, that are
GROUPed together to about 10 to 15 %. (indicating some bad DB-design :-) )
And that takes much more time than it should... We fear that the
corresponding rows are too widely spread over the data pages.

Thanks,
JP