When SORTHEAP is constrained, sort reservation may be leaked
during the dynamic acquisition of sort memory (known as
incremental sort). This occurs when the additional available
reservation amount is small enough that it cannot be effectively
utilized by the sort operator. The reservation is acquired then
abandoned, the result being that the reservation is never
released while the database remains active.
Over time the leaked reservation amount builds to the point
where the total sort reservation exceeds the configured
SHEAPTHRES_SHR size. At this point, sort reservation requests
start being heavily throttled (post-threshold sorts), resulting
in queries acquiring less memory for execution, which leads to
performance degradation. When the total sort reservation
reaches 1.25 x SHEAPTHRES_SHR, queries with larger requirements
will start failing with SQL0955.
Notes:
- constrained sortheap can be a result of many factors,
including STMM tuning, overall memory available or configured
for the database, and activity. For example, if there is a
sudden spike in activity requiring sort memory and sort is
configured conservatively by STMM, it may take some time for
STMM to adjust the configuration, before which the sort
configuration may be temporarily constrained.
- the leaked reservation is not real memory, it is only an
accounting value used to guide sort memory utilization.
- use of intra-parallelism may increase the likelihood of
encountering the problem due to apportioning the SORTHEAP
maximum across multiple agents
The problem can be detected by observing unexpectedly high sort
reservation levels. For example :
1. during very low activity levels, the sort reservation appears
very high, potentially exceeding SHEAPTHRES_SHR
2. sort reservation amounts far exceed the actual sort memory
usage levels.
To determine the sort memory reservation levels, check the
SORT_SHRHEAP_ALLOCATED in the MON_GET_DATABASE routine, or the
"Total Shared Sort heap allocated" amount in a database
snapshot. Both values are in 4K pages. Compare this to the
actual shared sort memory usage - MEMORY_POOL_USAGE for the
SHARED_SORT pool type in the MON_GET_MEMORY_POOL routine (value
is in 1K units) :
eg.
db2 select SORT_SHRHEAP_ALLOCATED from
table"(MON_GET_DATABASE(null))"
db2 select MEMORY_POOL_TYPE, MEMORY_POOL_USED from
table"(MON_GET_MEMORY_POOL('DATABASE',null,null))" where
MEMORY_POOL_TYPE = "'SHARED_SORT'" |
