Setting the prefetch size.

[Erik Hendrix]

Hi,

I have a question here regarding setting the prefetch size. So far we took
the rule that for OLTP, prefetchsize = extent size and for DSS prefetchsize
= extent size * number.

However, especially due to the "Skip Scans" for indexes I started to
question this. It looks to me that for reading a index DB2 will or do a
synchronous read or he will have to scan the whole index and thus prefetch
the data correct? If this is so, then it seems to me that it would be better
for tablespaces which contain only indexes to set the prefetchsize higher
then the extent size.

Then for tablespaces just containing tables, again here it seems to me that
setting the prefetchsize higher might be better since the optimizer (again,
if I'm correct here) will limit the number of pages to be prefetched (as
seen in explain plan, MAXPAGES setting) and will not do asynchronous IO if
only a few pages need to be retrieved anyway.
I can understand that for OLTP you might not want to set your prefetchsize
as high as for DSS tablespaces, but if using multiple containers and each
container on a RAID (and PARALLEL_IO is also set) then I'm starting to get
the feeling that even for OLTP databases (in which some DSS type of activity
happens) setting the prefetchsize higher might indeed help performance.

What do other people think, experiences, ...??

[Mark A]

"Erik Hendrix" <he**********@h otmail.com> wrote in message
news:6f******** *************** *******@news.te ranews.com...

Hi,

I have a question here regarding setting the prefetch size. So far we took
the rule that for OLTP, prefetchsize = extent size and for DSS prefetchsize = extent size * number.

However, especially due to the "Skip Scans" for indexes I started to
question this. It looks to me that for reading a index DB2 will or do a
synchronous read or he will have to scan the whole index and thus prefetch
the data correct? If this is so, then it seems to me that it would be better for tablespaces which contain only indexes to set the prefetchsize higher
then the extent size.
I don't believe that there is any evidence that DB2 does skip-scans, but I
would prefer not to discuss that subject in this thread since it has
recently been beat to death.

Usually, the prefetch size should be the extent size times the number of
containers in the tablespace (or some higher multiple of that result).
Containers should be placed on different physical disks for optimum results.
So if the extent size is 8 pages and you have 2 containers, the optimal
prefetch sizes are 16, 32, 64 etc pages.

DB2 does sometimes do scans of the entire index where prefetch is important.
But most indexes are smaller than tables, so I am not sure that one would
use a larger prefetch for indexes than the table. Of course, if you had an
OLTP system and did not want to fill up buffer pools with tablespace scans
for the table, then that might be a different consideration.

Then for tablespaces just containing tables, again here it seems to me that setting the prefetchsize higher might be better since the optimizer (again, if I'm correct here) will limit the number of pages to be prefetched (as
seen in explain plan, MAXPAGES setting) and will not do asynchronous IO if
only a few pages need to be retrieved anyway.
I can understand that for OLTP you might not want to set your prefetchsize
as high as for DSS tablespaces, but if using multiple containers and each
container on a RAID (and PARALLEL_IO is also set) then I'm starting to get
the feeling that even for OLTP databases (in which some DSS type of activity happens) setting the prefetchsize higher might indeed help performance.

What do other people think, experiences, ...??

Prefetch will help if you have tablespaces scans of the entire table, or
start reading large continuous sections of a table where prefetch may kick
in.

If you use RAID-5, then you want one container per array, with the extent
size equal to the stripe size or some multiple of that.

Often it is good in OLTP systems to use the
OPTIMIZE FOR integer ROWS
clause. This may prevent prefetch in cases when the number of rows to be
fetched in a cursor is less than DB2 thinks. This clause does not limit the
number of rows that may be fetched, but does control the optimizer in terms
of whether prefetch may be used by DB2.