Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    138

    Unanswered: question on prefetching

    UDB 8.1 fp6a, AIX 5.2 ML4

    One of our queries is doing list prefetch and it's just hanging there at the fetch operation holding onto locks..The table always goes out of clustering because of the deletes, updates happening on that..I scheduled weekly reorgs and still I face the problem of fetch operation hanging couple of days after the reorg. .the table has only 1000 rows and I placed it in seperate sms tablespace with extent size 32, prefetch size 32..

    This is an oltp env..

    what can I do differently to avoid this issue?

  2. #2
    Join Date
    Aug 2004
    Posts
    138
    there is an apar on diabling list prefetch..but this is for siebel pipelined plans
    Support & downloads >

    IY72977: INTRODUCE OPTION OF DISABLING LIST PREFETCH FOR PIPELINED PLANS

    APAR status
    Closed as program error.

    Error description
    When pipelined plans is enabled for Siebel environments, we
    introduce the option of disabling list prefetch accesses for
    performance reasons.
    A list prefetch access can be seen in the access plan of an SQL
    as (FETCH->RIDSCAN->SORT->IXSCAN) or if you gather a db2exfmt
    output for a query, you will see:
    PREFETCH: (Type of Prefetch)
    LIST
    Note, list prefetch is a good access in general. But in
    specific cases of pipelined plans, avoiding this access can
    result in better perforance. To enable this fix, please contact
    Siebel or IBM to set the registry to a specific value.
    Local fix
    Problem summary
    USERS AFFECTED:
    Siebel specific environments or instances where the pipelined
    plans registry is set.

    PROBLEM DESCRIPTION:
    Provide additional option to registry variable to avoid list
    prefetch accesses in certain cases.

    PROBLEM SUMMARY:
    For environments where the pipelined plans registry is set,
    avoiding list prefetch through a special setting of the registry
    can help performance.
    Problem conclusion
    Problem was first fixed in DB2 UDB Version 8, FixPak 11
    Temporary fix
    Comments
    APAR information
    APAR number IY72977
    Reported component name DB2 UDB ESE AIX
    Reported component ID 5765F4100
    Reported release 810
    Status CLOSED PER
    PE NoPE
    HIPER NoHIPER
    Submitted date 2005-06-17
    Closed date 2006-02-06
    Last modified date 2006-02-06

    APAR is sysrouted FROM one or more of the following:

    APAR is sysrouted TO one or more of the following:

    Modules/Macros
    ENG_SQNO


    Fix information
    Fixed component name DB2 UDB ESE AIX
    Fixed component ID 5765F4100

    Applicable component levels
    R810 PSN UP
    R820 PSN UP
    Last edited by chavadb2; 05-05-06 at 12:37.

Posting Permissions

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