IBM Support

PH23827: MULTI-INDEX ACCESS IS NOT SELECTED BECAUSE THE LIMIT OF STOREAGE USED BY OPTIMIZER IS EXCEEDED

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Query encounters poor perforamnce becauses multi-index access is
    not used, multi-index access is not used because the limit of
    optimizer storage usage is exceeded.
    
    For example, a query contains many OR predicates
    SELECT *
    FROM T1
    WHERE C1 = ?
     AND (  (C2 = ? AND C3 = ?)
         OR (C2 = ? AND C3 = ?)
         OR (C2 = ? AND C3 = ?)
               ......
         OR (C2 = ? AND C3 = ?)
         )
    ;
    
    Table T1 has an index I1 on (C1,C2,C3). The optimal access type
    can be multi-index access using I1 with MATCHCOLS=3. But when
    optimizer does access path selection, the multi-index access
    path is discarded because the limit of optimizer storage usage
    is exceeded. Then single index access with MATCHCOLS=1 will be
    used.
    

Local fix

  • BYPASS/CIRCUMVENTION:
    Increase hiden zparm for optimizer storage:
    MAX_OPT_STOR
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All Db2 12 for z/OS users who have a                         *
    * complex query with many OR predicates and                    *
    * MULTI-ROW FETCH is specified.                                *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Db2 does not build multi-index plan                          *
    * for a complex query including many                           *
    * OR predicates, due to the limitation                         *
    * of optimizer storage usage.                                  *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply corrective PTF when available                          *
    ****************************************************************
    Db2 may select an inefficient single index to access the
    leading table for a complex query which includes many OR
    predicates, when MULTI-ROW FETCH is specified. Multi-index plan
    is expected, but Db2 does not choose it. The reason is that Db2
    does not build a multi-index plan due to the limitation of
    optimizer storage usage.
    Example:
    SELECT *
    FROM T1
    WHERE C1 = ?
     AND (  (C1 =? AND C2 = ? AND C3 = ?)
         OR (C1 =? AND C2 = ? AND C3 = ?)
         OR (C1 =? AND C2 = ? AND C3 = ?)
               ......
         OR (C1 =? AND C2 = ? AND C3 = ?)
         )
    ;
    Table T1 has an index IX1 on (C1,C2,C3). If MULTI-ROW FETCH is
    specified, range-list access type is disabled. The optimal plan
    can be multi-index access using IX1 with MATCHCOLS=3. But Db2
    does not build the multi-index plan because the estimated
    optimizer storage exceeds the maximum optimizer storage
    allowed. It results in an single index access with MATCHCOLS=1.
    Additional Keywords:
    SQLPERFORMANCE SQLACCESSPATH MIDX SQLOR
    

Problem conclusion

  • Code is updated to select a multi-index plan.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PH23827

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    C10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2020-03-30

  • Closed date

    2020-04-20

  • Last modified date

    2020-05-02

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

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

    UI69114

Modules/Macros

  • DSNXOGCM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RC10 PSY UI69114

       UP20/04/29 P F004

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.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
04 May 2020