When running with query parallelism, (requires shared sort) and
private sort enabled, the hash join operator may incorrectly
adjust the total reserved shared sort memory when it is intended
to modify reserved private sort memory. Note this is affecting
the sort reservation accounting and not the actual memory
allocations.
Query parallelism is typically enabled by setting INTRA_PARALLEL
= YES and DFT_DEGREE > 1. Private sort is enabled by having a
non-0 SHEAPTHRES setting. This results in a hybrid model of
using both private and shared sort memory, which is a legacy
setting and not recommended - it is preferable to use only
shared sort memory (SHEAPTHRES = 0) in an environment running
with query parallelism.
It is possible for the private and shared sort counters to get
very large or underwrap (decrease below 0 resulting in a very
large unsigned value). In this condition, sort reservation
requests may be heavily throttled - a much smaller amount of
memory may be allowed than normal for an operation requiring
sort memory. Symptoms include performance degradation and
SQL0955 errors.
Usually, but not always, an underwrap is prevented, resulting in
db2diag.log entries similar to the following :
2013-12-07-11.41.35.616872-300 I24106A1648 LEVEL:
Warning
PID : 38732070 TID : 41084 PROC :
db2sysc 0
INSTANCE: XXX NODE : 000 DB : SAMPLE
APPHDL : 0-27693 APPID: XXX
AUTHID : CLRPCOGP HOSTNAME: XXX
EDUID : 41084 EDUNAME: db2agntp (XXX) 0
FUNCTION: DB2 UDB, sort/list services,
sqlsReleaseSharedSortSpace, probe:10
DATA #1 : String, 46 bytes
Sort shared memory counter underflow detected
DATA #2 : unsigned integer, 4 bytes
2414
DATA #3 : unsigned integer, 4 bytes
39
CALLSTCK: (Static functions may not be resolved correctly, as
they are resolved to the nearest symbol)
[0] 0x090000002658CEF4
sqlsReleaseSharedSortSpace__FP8sqeAgent16SQLS_MEMCONSUMERUiT3PP1
9STMM_OpAllocMonitor + 0xD0
[1] 0x0900000029E31E40
sqlri_hsjnLeftoversReturnMatch__FP8sqlrr_cbP11sqlri_hsjno +
0x29C4
[2] 0x0900000029E2F238 sqlrihsjn__FP8sqlrr_cb + 0x34
[3] 0x0000000000000000 ?unknown + 0x0
[4] 0x09000000260EC05C
sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 0x78
...
Sort monitor elements may also include unusually high values
such as the shared sort high water mark below, and leftover
amounts (no activity but there is still private or sort space
showing as allocated. The following is from a database
snapshot:
Total Private Sort heap allocated = 142132
Total Shared Sort heap allocated = 61850
Shared Sort heap high water mark = 4294967274
Post threshold sorts (shared memory) = 318031470 |
