Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: Time waited for prefetch (ms) = 64345756

    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

  2. #2
    Join Date
    Mar 2003
    Posts
    343
    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?

  3. #3
    Join Date
    Oct 2003
    Posts
    2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •