On May 26, 11:01 am, "Jan M. Nelken" <Unknown.U...@Invalid.Domain>
wrote:
Setting extend size = prefetch size for that case you described above
would turn off prefetching, wouldn't it?
Jan M. Nelken
Prefetch takes place when DB2 decides to put pages into the bufferpool
asynchronously before the pages are actually needed by DB2. In that
sense itt is based on an artificial intellignece algorithm. This
typically happens in a table scan of a large table where DB2 knows
that all the pages of the table will eventually need to be in the
bufferpool. But DB2 needs to make sure that prefetching is not too
fast if the bufferpool does not have room for all the pages, otherwise
previously prefetched pages could be flushed out before they are
actually used for the query.
Prefetching can certainly happen if prefetch size equals extent size.
DB2 will just prefetch one extent at a time (assuming that prefetch is
turned on and DB2 thinks it is a good idea for a specific query). The
reason for making the prefetch size equal to the extent size times
number of containers is that otherwise you will have disk contention
if you are prefetchng multiple extents from the same disk or array at
the same time. That is why you should generally only use multiple
containers if they can be placed on different disks or different
arrays.