IBM Support

PK61277: PROVIDE AN OPTION TO ADJUST THE CPU/IO COST BALANCE FOR USE BY OPTIMIZER

A fix is available

 

APAR status

  • Closed as new function.

Error description

  • Provide an option to adjust the CPU/IO cost balance for use by
    optimizer.
    .
    SQLACCESSPATH SQLPERFORMANCE

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: For all DB2 for z/OS users who are *
    * experiencing slow query performance *
    * because DB2 has overestimated I/O costs *
    * for the query, especially those users *
    * who have faster processor speeds. *
    ****************************************************************
    * PROBLEM DESCRIPTION: Cost estimation for I/O cost of a query *
    * has become inaccurate as processor *
    * speeds have increased. *
    ****************************************************************
    * RECOMMENDATION: *
    ****************************************************************
    DB2 for z/OS has been using a constant unit of time for I/O cost
    estimation since V2. At the same time, the CPU cost estimation
    is based on the CPU MIPS ratio. When DB2 estimates the cost
    of a query, it assumes no data has been cached into the buffer
    pool yet, so the I/O cost is overweighted. Consequently, DB2
    tends to choose a more CPU-intensive access path. The
    I/O and CPU cost imbalance issue has been exposed more with
    the improvement of the System z processor speed.

    Additional Keywords:
    SQLACCESSPATH SQLPERFORMANCE

Problem conclusion

Temporary fix

Comments

  • A new zparm OPTIOWGT is added to change the cost balance in
    DB2 when selecting an access path.

    OPTIOWGT is a switch. When the switch is turned on, DB2 uses
    new weights in cost estimation to better reflect the effects
    of improved I/O response and DASD caching; otherwise, DB2
    keeps the cost balance unchanged.

    See the HOLD text for information on how to set or change
    the OPTIOWGT parameter.

APAR Information

  • APAR number

    PK61277

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-02-19

  • Closed date

    2008-08-21

  • Last modified date

    2008-10-02

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UK39140

Modules/Macros
DSN@ZP DSNDQWPZ DSNDSPRF DSNDSPRM DSNTIDXA DSNTIJUZ
DSNTINST DSNT1RSP DSNWZP DSNXMOPC DSNXOMPS DSN6SPRC
DSN6SPRM          

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R910 PSY UK39140

       UP08/09/06 P F809

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.

Document information

More support for: DB2 for z/OS

Software version: 910

Reference #: PK61277

Modified date: 02 October 2008


Translate this page: