IT27216: PERFORMANCE REGRESSION IN BLU QUERIES CONTAINING JOIN ON VARCHARCOLUMNS ON AIX

Db2 Version 11.1 Mod4 Fix Pack4 iFix001 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 6 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 5 for Linux, UNIX, and Windows
Db2 Version 11.1 Mod 4 Fix Pack 7 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• A performance degradation in Db2 V11.1 M4FP4 can be observed
  when joining VARCHAR columns in BLU queries on AIX.
  Compared Db2 V11.1 M4FP4 to Db2 V11.1 M3FP3, the degradation
  reduced query performance up to 20-30%.
  
  If you analysis such queries using tprof, you can find
  ibm_cde::services::ManagedStringBase::assign very close to the
  top.
  
  A sample tprof output of a query affected by this issue:
  
  ...
    Total Ticks For All Processes
  (/db2/m3_1109/lib64/libdb2blu.a[shr_64.o]) = 18097
  
  Subroutine                 Ticks    %   Source
  Address  Bytes
  ==========                 ===== ====== ======
  =======  =====
  .ibm_cde::query::ConcatEvaluator::computeListOfNotCodeableInputs
  (bool&,ibm_cde::types::BitString&,ibm_cde::query::WorkUnit&)
  2561   9.82 ConcatEvaluator.cpp   4c80848   3520
  .ibm_cde::services::ManagedStringBase::assign(const unsigned
  char*,const unsigned long)   1791   6.87
  ColumnDictionaryVersionBase.cpp  1125638    200
  .ibm_cde::types::VarLenString::initCompareFunc()    941   3.61
  VarLenString.cpp       bbceb8    2b0
  .ibm_cde::types::VarLenString::formString(const
  char*,ibm_cde::types::DataType,unsigned short)    923   3.54
  ColumnDictionaryVersionBase.cpp  110a4a8    2d0
  .ibm_cde::data::ColumnDictionaryVersion<ibm_cde::types::VarLenSt
  ring>::decode(ibm_cde::data::QCode,ibm_cde::types::ValUncoded&)
  const    895   3.43 ColumnDictionaryVersionBase.cpp  1168908
  360
  ...
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Db2 11.1 Mod 4 Fixpack 5 or higher                *
  ****************************************************************
  

Problem conclusion

• First fixed in Db2 11.1 Mod 4 Fixpack 5
  

Temporary fix

Comments

APAR Information

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

• 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

• RB10 PSN

     UP