Time waited for prefetch (ms) = 64345756

rasimd · #1 (**permalink**) 10-01-03, 21:23

Hi,

We tourned on Db2 monitoring for about 10 minutes and tok a snapshot. In the snapshot we have an extreamly high value for "Time waited for prefech (ms )" > 64000000.

The environment is : Db2 7.2. FP 4 on AIX 5.2. Our database is located on a Hitachi SAN server.

Any idea why the value is so high would be highly appreciated.

Rasim

cchattoraj · #2 (**permalink**) 10-01-03, 21:32

There are many things that affect prefetch waits.

a] How are the tablespaces laid out? Do they have multiple containers? Are the disks laid out so that they are container independant and if this is an MPP system, also partition independant?

b] What are the strip and stripe sizes?

c] Are the tablespace parms prefetchsize, transferrate and overhead correctly set? Is it possible that the prefetchsize is wrapping around the strip or stripe?

d] How many prefetch queues is the system configured for?

What is the level of query concurrency? How much data are you dealing with and what portion of that data is concurrently accessed?

rasimd · #3 (**permalink**) 10-02-03, 10:09

Quote:

Originally posted by cchattoraj
There are many things that affect prefetch waits.

a] How are the tablespaces laid out? Do they have multiple containers? Are the disks laid out so that they are container independant and if this is an MPP system, also partition independant?

-A: Its is an SMP system with a single node.The following are setting for the tabalespaces/containers/extentsize/prefetchzie:

CREATE BUFFERPOOL "BP_1" SIZE 40000 PAGESIZE 4096 NOT EXTENDED STORAGE;

CREATE BUFFERPOOL "BP_2" SIZE 50000 PAGESIZE 4096 NOT EXTENDED STORAGE;

CREATE REGULAR TABLESPACE TBS_1 IN NODEGROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED B
Y SYSTEM
USING ('/db/db1/tbs_coars/tbs_coars01',
'/db/db1/tbs_coars/tbs_coars02',
'/db/db1/tbs_coars/tbs_coars03',
'/db/db1/tbs_coars/tbs_coars04')
EXTENTSIZE 16
PREFETCHSIZE 64
BUFFERPOOL BP_1
OVERHEAD 24.100000
TRANSFERRATE 0.900000;

CREATE REGULAR TABLESPACE TBS_2 IN NODEGROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY S
YSTEM
USING ('/db/db1/tbs_dw/tbs_dw01',
'/db/db1/tbs_dw/tbs_dw02',
'/db/db1/tbs_dw/tbs_dw03',
'/db/db1/tbs_dw/tbs_dw04')
EXTENTSIZE 32
PREFETCHSIZE 128
BUFFERPOOL BP_2
OVERHEAD 24.100000
TRANSFERRATE 0.900000;

b] What are the strip and stripe sizes?

-A:The strip size is 64K.

c] Are the tablespace parms prefetchsize, transferrate and overhead correctly set? Is it possible that the prefetchsize is wrapping around the strip or stripe?

-A:Please see the above settings for the extentsize and prefetchsize.

d] How many prefetch queues is the system configured for?

-A:There are 16 prefetchers.

What is the level of query concurrency? How much data are you dealing with and what portion of that data is concurrently accessed?

-A:Query concurency is on the deafult level.

Internet Services

Web Development

Digital Marketing

Consumer Tech