The amount of memory used by sort memory consumers
(MEMORY_POOL_USED for the SHARED_SORT memory pool) should not
exceed the sort reservation (SORT_SHRHEAP_ALLOCATED).
When issuing queries against column-based tables, this condition
may be violated. In extreme cases, shared sort memory
consumption may greatly exceed the shared throttling point
(SHEAPTHRES_SHR), causing over-allocation of database_memory.
This can result in exhausting instance or system memory, with
the accompanying symptoms of out-of-memory, paging, and system
outages.
In order to confirm this problem, check whether shared sort
heap memory usage exceeds the reserved amount (comparing current
or high water marks). The SORT_SHRHEAP element is in 4K pages
while the MEMORY_POOL value is in 1KB units, so the SORT_SHRHEAP
columns are multiplied by 4 for a correct comparison.
db2 "select SORT_SHRHEAP_ALLOCATED * 4 as SORT_RESERVED,
SORT_SHRHEAP_TOP * 4 as SORT_RESERVED_HWM, MEMORY_POOL_USED,
MEMORY_POOL_USED_HWM
from table(MON_GET_DATABASE(NULL)),
table(MON_GET_MEMORY_POOL('DATABASE',CURRENT_SERVER,-1))
where memory_pool_type='SHARED_SORT'"
SORT_RESERVED SORT_RESERVED_TOP MEMORY_POOL_USED
MEMORY_POOL_USED_HWM
------------- ----------------- ----------------
--------------------
262180 262180 728320
728320
In this case, the actual "used" shared sort memory pool amount
(MEMORY_POOL_USED) is nearly 3x higher than the reserved amount
(SORT_SHRHEAP_ALLOCATED). |
Systems with a high SORTHEAP:SHEAPTHRES_SHR ratio are more
vulnerable to extreme symptoms. Ensure that the
SORTHEAP:SHEAPTHRES_SHR ratio is no more than 1:20. For
example, for a SHEAPTHRES_SHR setting of 10 million (10000000),
ensure SORTHEAP is no greater than 500000. Lower SORTHEAP
settings may be necessary depending on concurrency. |
