IBM Support

IC75151: POSSIBLE NLJN QUERY PERFORMANCE ISSUE FROM UNDER COSTED NLJN DUE TO BAD INNER PAGE ESTIMATE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When examining an Query Access Plan with a Nested Loop Join,
    usually the cost for the Nested Loop Join will be at least the
    sum of the cost of the outer Query Access Plan and the inner
    Query Access Plan. You may be experiencing a poorly performing
    Query Access Plan that is caused by this APAR if the number of
    I/O's associated with the Nested Loop Join is only a few I/Os
    more then the number of I/Os from the outer Query Access Plan,
    but the inner Query Access Plan has more then a few I/Os
    indicated (typically 1 or 2 I/Os, but it could be more).
    
    The db2exfmt plan segment below shows a Nested Loop Join
    (NLJOIN) with an outer I/O of 0 I/Os, an inner I/O of 91,252
    I/Os, but the Nested Loop Join I/Os of only 3. In this case the
    NLJOIN I/Os should have been at least 91,252 I/Os or close to
    91,252 I/Os.
    
                         715402
                         NLJOIN
                         (  23)
                         7367.04
                         3.00784          <<< I/Os too small
                /-----------+----------\
              1                        715402
           BTQ                         FETCH
           (  24)                      (  26)
          0.0678342                    114167
              0                         91252
              |                      /-----+-----\
              1              1.42166e+06      8.52996e+06
           IXSCAN          IXSCAN       TABLE: DW
           (  25)              (  27)            FACT
          0.0174534        19006.1            Q1
              0                 2376
             |                   |
              6              8.52996e+06
       INDEX: DW           INDEX: DW
           IDX1                  XIF1
             Q7                  Q1
    

Local fix

  • One Possible workaround that *may* work is to get detailed stats
    for the columns (frequent value & distribution stats). This will
    cause the join page selectivity code to use a different
    algorithm to compute the filtering.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * POSSIBLE NLJN QUERY PERFORMANCE ISSUE FROM UNDER COSTED NLJN *
    * DUE TO BAD INNER PAGE ESTIMATE                               *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Please upgrade to Version 9.7 Fixpack 4 or later             *
    ****************************************************************
    

Problem conclusion

  • Problem first fixed in Version 9.7 Fixpack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC75151

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-03-21

  • Closed date

    2011-05-05

  • Last modified date

    2011-05-05

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

    IC70492

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC75151

Modified date: 05 May 2011