Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Unanswered: Package cache overflow when set to AUTOMATIC

    DB2 9.5 FP 2a on Redhat Linux 64 bit

    I keep getting these messages in the db2diag.log file and the parameter is already set to AUTOMATIC. Anyone have an idea of how to make them go away?




    2009-03-01-20.27.09.065682-300 E1453E1143 LEVEL: Event
    PID : 12671 TID : 46915739707712PROC : db2sysc 0
    INSTANCE: db2instn NODE : 000 DB : LCD_NOR
    APPHDL : 0-6960 APPID: GA02016F.MD0E.02B8C2010459
    AUTHID : LCADUSER
    EDUID : 187 EDUNAME: db2agent (LCD_NOR) 0
    FUNCTION: DB2 UDB, access plan manager, sqlra_cache_mem_please, probe:100
    MESSAGE : ADM4500W A package cache overflow condition has occurred. There is
    no error but this indicates that the package cache has exceeded the
    configured maximum size. If this condition persists, you should
    perform additional monitoring to determine if you need to change the
    PCKCACHESZ DB configuration parameter. You could also set it to
    AUTOMATIC.
    REPORT : APM : Package Cache : info
    IMPACT : Unlikely
    DATA #1 : String, 232 bytes
    Package Cache Overflow
    memory needed : 2020
    current used size (OSS) : 65172947
    maximum cache size (APM) : 65095598
    maximum used size (OSS) : 186056704
    owned size (OSS) : 103088128
    number of overflows : 321

    2009-03-01-20.27.09.076044-300 E2597E718 LEVEL: Event
    PID : 12671 TID : 46915844565312PROC : db2sysc 0
    INSTANCE: db2instn NODE : 000 DB : LCD_NOR
    APPHDL : 0-6919 APPID: GA0201B3.JB05.00D402003002
    AUTHID : LCADUSER
    EDUID : 162 EDUNAME: db2agent (LCD_NOR) 0
    FUNCTION: DB2 UDB, access plan manager, sqlra_cache_mem_please, probe:200
    REPORT : APM : Package Cache : info
    IMPACT : Unlikely
    DATA #1 : String, 216 bytes
    Package Cache Overflow #321 Resolved.
    memory needed : 1512
    current used size (OSS) : 64904463
    maximum cache size (APM) : 65095598
    maximum used size (OSS) : 186056704
    owned size (OSS) : 103088128

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    is STMM turned on?

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, STMM is on.

    Andy

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I think (not 100% sure) this overflow message is written when STMM is not tuning the package cache.

    From the manual (pckcachesz):
    When this parameter is set to AUTOMATIC, it is enabled for self tuning. When self_tuning_mem is set to ON, the memory tuner will dynamically size the memory area controlled by pckcachesz as the workload requirements change. Because the memory tuner trades memory resources between different memory consumers, there must be at least two memory consumers enabled for self tuning in order for self tuning to be active.

    Do you have any other parameters enabled for self-tuning

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Just about all of them are set to automatic, including database_memory, locklist, sheapthres_shr, sortheap, dbheap, stmtheap, applheapsz, appl_memory, and stat_heap_sz.

    Andy

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I verified that overflow messages should not be written to the db2diag.log with STMM enabled and package cache set to automatic. So, please verify that package cache was actually being tuned at the time (STMM logs will show if pckcachesz is being tuned). Perhaps tuning was disabled due to eg. starting up with tiny bufferpools.

    If package cache is actually being tuned, then this may be some timing issue... If this is the case, I'd suggest to monitor package cache changes over some period of time using db2pd -mempools and compare this data with STMM logs and/or db2diag.log. You may consider opening a pmr for this...

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It appears that STMM is turned off. I though it was on.

    Thanks for your help.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •