With STMM tuning of SORT parameters enabled (SORTHEAP,
SHEAPTHRES set to AUTOMATIC, SELF_TUNING_MEM ON), a system may
experience degraded performance over time along with db2diag.log
errors such as :
2017-10-05-06.58.01.086620-180 I21833545A697 LEVEL: Error
FUNCTION: DB2 UDB, Self tuning memory manager,
stmmStartHJMonitorIfNecessary, probe:306
MESSAGE : ZRC=0x82120001=-2112749567=SQLR_NONSEVERE_PGM_ERROR
"Non-severe programming error."
DIA8532C An internal processing error has occurred.
DATA #1 : String, 32 bytes
ERROR starting hash join monitor
In DB2 version 10.5, the throttling of shared sort reservations
was made more aggressive in order to prevent memory
overcommitment. For the most severe case, minimum requirements
needed to satisfy an SQL request may be denied with SQL0955 if
the shared sort reservation level reaches 125% of the configured
amount. STMM simulates what-if configuration scenarios as part
of automatic sort tuning, and these scenarios-- which determine
performance results under different configurations--may also
simulate similar aggressive throttling. Aggressive throttling
should only occur in constrained tuning situations, which are
typically temporary in STMM-tuned environments (due to volatile
workload).
Two problems may occur as a result of aggressive throttling
during the running of STMM simulation
1. the memory used to support the simulation may be leaked.
this memory comes from the shared sort pool, and is part of the
shared sort reservation level.
2. the simulation may fail, which prevents STMM from sensing the
need to increase the sort configuration.
The problem can be identified by the symptoms mentioned above,
and the fact that a higher-than-expected shared sort reservation
level and memory usage can be observed when there is little
activity on the system. |
