IBM Support

IC83616: OPTIMIZER UNDERESTIMATES THE NUMBER OF EXPECTED ROWS IN A SORT /GROUP BY OPERATOR, WHICH LEADS TO A BAD PERFORMING ACCESS PLAN

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • A sample statement having a WHERE clause as shown below will
    generate a plan where the expected row count in SORT / GROUP BY
    operator is drastically less compared to actual number of rows.
    
    statement:
    
    SELECT...
    WHERE tab1.col1 = 'xyz'
       GROUP BY tab2.col2 ,
    tab1.col1 ,....
    
    Note: col1 is used in the GROUP BY clause
    
    Plan generated will show estimated row count in SORT/GROUP BY
    as 3 where the actual count is 13923.
    
             0,000176497
                  1
               NLJOIN
               (  16)
               1221,82
                 NA
             /---+----\
       0,000530024   0,332997
            1            1
         TBSCAN       IXSCAN
         (  17)       (  23)
         1214,24      15,1398
           NA           NA
           |            |
            3           -1
          5469          NA
         TEMP     INDEX: SCHEMA1
         (  18)    IDX1
         1214,23        Q42
           NA
           |
            3
          5469
         GRPBY
         (  19)
         1214,23
           NA
           |
            3
          13923
         TBSCAN
         (  20)
         1214,23
           NA
           |
            3
          13923
         SORT
         (  21)
         1214,23
           NA
           |
          19109
          19109
         TBSCAN
         (  22)
         1209,64
           NA
           |
          19109
           NA
     TABLE: SCHEMA1
       TABX
           Q5
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 Fix Pack 7.                       *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 Version 9.7 Fix Pack 7.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC83616

  • 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

    2012-05-21

  • Closed date

    2012-12-14

  • Last modified date

    2012-12-14

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

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

    IC86792

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC83616

Modified date: 14 December 2012