Skip to Content
author's profile photo Former Member
Former Member

Unexpected minimum value for SHEAPTHRES_SHR

Hi Folks - We keep getting the following message every 30 seconds on our BI QA server.

2009-08-24-07.26.17.263989-240 E78429A607 LEVEL: Warning

PID : 930014 TID : 15425 PROC : db2sysc 0

INSTANCE: db2bq1 NODE : 000 DB : BQ1

APPHDL : 0-28423 APPID: *LOCAL.DB2.090808073504

AUTHID : DB2BQ1

EDUID : 15425 EDUNAME: db2stmm (BQ1) 0

FUNCTION: DB2 UDB, Self tuning memory manager, stmmComputeMinSHEAPTHRES, probe:836

MESSAGE : Unexpected minimum value for SHEAPTHRES_SHR - value automatically

corrected - 122423 - 2448 - 338887 - 9180 - 3 - 0 - 3.000000 -

1 - 31 - 271110 - 630

I am pretty sure this will have performance impact on the database as the database manager has to keep changing the SHEAPTHRES_SHR value every 30 seconds back to the correct value. Has any one seen this before? Turning off STMM is not an option. We do not want to hard code SHEAPTHRES_SHR value as the loads on BI are not-predictable in the environment.

Pl advise.

Thanks.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Aug 26, 2009 at 06:16 AM

    If the parameter value is changed that often, then most probably there is some contention of the memory resources that are available to your database. In other words, the requirements for this memory area are high, so due to shortage of memory other memory-related parameters are continuously competing with SHEAPTHRES_SHR .

    If that is the case, then you wither need to provide enough memory to the database (e.g. increase DB memory / instance memory), or set the parameters that are affected to fixed values (not too high to avoid paging) and live with resulting performance impact (due to lack of resources).

    It is hard to be more precise as information provided by you is not too much.

    Malte

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 31, 2009 at 09:25 AM

    Hello,

    What is the DB version of your databse?

    Please have look at Note : 899322.

    Update us on the issue if this is not helping much.

    Regards,

    Abhay

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 08, 2009 at 01:15 PM

    Hello,

    What version of DB2 are you running on? Assuming your are running version 9.5 (or V9.1) with stmm, please check to see if you are not exhausting the memory allocated to the INSTANCE_MEMORY configuration. This can be done by connecting to the database and running:

    db2 "select * from table(admin_get_dbp_mem_usage()) as a"

    You should see a "buffer" between the peak/current partition memory and the max_partition memory. There is a bug in DB2 9.5 versions prior to FP4 where it overestimates the amount of available memory and exhausts all the available memory which causes DB2 to run into several memory problems. This can be fixed (temporarily) by setting aside some reserved memory by setting the following registry parameter:

    db2set DB2_MEM_TUNING_RANGE=10,20

    You can remove this registry variable after upgrading to 9.5 FP4.

    Thanks,

    Sameer

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      The output of the SQL statement shows:

      MAX_PARTITION_MEM: Maximum allowed "instance_memory" (this will correspond with the current setting of instance_memory in bytes)

      CURRENT_PARTITION_MEM: Current total memory allocation (in bytes)

      PEAK_PARTITION_MEM: HWM total memory allocation (in bytes)

      The bug with the STMM memory calculation was first fixed with version 9.5 FP4 so you might have to upgrade your instances to FP4. In fact, if I am not mistaken, SAP withdrew FP3 due to a hiper apar which can potentially impact SAP systems.

      Also as far as the values in the output of the query and db2pd are concerned, they are not different. The output of the query is expressed in bytes whereas the output of pd is expressed in KB.

      $$ db2pd -dbptnmem

      Memory Limit: 36000000 KB (which is 36864000000 bytes)

      Current usage: 31746944 KB (which is 32508870656 bytes)

      HWM usage: 35704320 KB (which is 36561223680 bytes)

      Regards,

      Sameer

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.