Analyzing Package Cache Size

Note: updated 7/21 to reflect location of the package cache high water mark in the MON_GET* table functions

I have long been a fan of a smaller package cache size, particularly for transaction processing databases. I have seen STMM choose a very large size for the package cache, and this presents several problems:

  • Memory used for the package cache might be better used elsewhere
  • A large package cache makes statement analysis difficult
  • A large package cache may be masking statement issues – the proper use of parameter markers

Parameter Markers

Parameter markers involve telling DB2 that the same query may be executed many times with slightly different values, and that DB2 should use the same access plan, no matter what the values supplied are.… Read the rest

Continue reading »