High memory usage or exhaustion has been occurring in rare
scenarios starting in version 9 due to hash join drastically
overallocation sortheap. One case is when a query contains a
hash join comparing a very large number of similar values.
For this scenario to occur, the sort heap memory usage for a
single sortheap will climb to at least 4GB regardless of the
configured SORTHEAP setting - unless an error occurs first.
Note this sortheap usage will not show up in the standard sort
memory counters, but will show up in the sortheap memory pool
reports (in application snapshot, memory tracker).
On version 9.5, if INSTANCE_MEMORY becomes exhausted, the
following db2diag.log entries will be seen :(note the
excessively large physical heap size. The large configured heap
size is normal as it means "unlimited" - hash join is supposed
to stay within the configured SORTHEAP limit on it's own)
FUNCTION: DB2 UDB, SQO Memory Management,
sqloMemLogPoolConditions, probe:30
DATA #1 : <preformatted>
Out of memory failure for Sort Heap on node 4.
Requested block size : 4000 bytes.
Physical heap size : 1091633152 bytes.
Configured heap size : 281474976645120 bytes.
Unreserved memory used by heap : 0 bytes.
Unreserved memory left in set : 0 bytes.
...
FUNCTION: DB2 UDB, runtime interpreter, sqlri_hsjnGetTupleBlock,
probe:10
MESSAGE : Virtual memory exhausted.
The easiest way to monitor for this condition is by using the
snap_get_agent_memory_pool routine as follows :
db2 "SELECT AGENT_ID, AGENT_PID, DBPARTITIONNUM, POOL_ID,
POOL_CUR_SIZE FROM
TABLE(SNAP_GET_AGENT_MEMORY_POOL('SAMPLE',-2)) A WHERE
POOL_ID='SORT' AND POOL_CUR_SIZE > 20000".
replacing 'SAMPLE' with the database name and '20000' with a
value larger than SORTHEAP, eg. 2 * SORTHEAP. When using STMM,
the value may have to be larger - monitor the db2diag.log for
normal SORTHEAP settings and use a value above that. |
