The AUTOMATIC setting for STMTHEAP currently relies on some
internally calculated memory thresholds to decide when to
discontinue dynamic join enumeration and fall back to greedy
join enumeration. This prevents the allocation of excessive
amounts of memory under dynamic join enumeration.
The current thresholds used for making the decision to
discontinue dynamic join enumeration can be triggered in an
inconsistent and unpredictable manner. This may result in
different access plans for the same query at different times -
sometimes compiled with dynamic join, and sometimes greedy. A
query compiled under greedy may not perform as well as with
dynamic, so the differing access plan could result in
differences in performance.
If a query is experiencing unexpected changes in performance
when STMTHEAP is AUTOMATIC, a secondary indicator that this may
be related to this issue would be unexpected SQL0437W (rc=1
warnings returned to the application.
With this APAR, a consistent upper limit for dynamic join memory
usage will be used when Statement Heap is set to AUTOMATIC. That
upper limit will be the configured value underlying the
AUTOMATIC setting, which previously had not been utilized. There
is no change to the behaviour of allowing unlimited growth
during greedy join enumeration under AUTOMATIC, and there is no
change to the behaviour when Statement Heap is set to a fixed
value.
For example,
db2 update db cfg for sample using 8192 AUTOMATIC
results in the setting:
STMTHEAP = AUTOMATIC (8192)
Under the old behaviour, the value 8192 had no effect. Internal
volatile thresholds were used for dynamic join memory allowance,
and the greedy join memory allowance was unlimited.
Under the new behaviour, the dynamic join allowance is 8192 (4K
pages), and the greedy join allowance remains unlimited.
Note that the statement heap growth can still be limited by
higher-level memory limits such as APPL_MEMORY, INSTANCE_MEMORY,
and system memory.
One further change is that the default Statement Heap setting
for 64-bit DB2 has been doubled from AUTOMATIC (4096) to
AUTOMATIC (8192). This change affects only new databases created
on 64-bit instances. The Statement Heap setting is not altered
on migration or Fix Pack upgrades.
In some cases, the existing underlying value for the AUTOMATIC
setting may reflect a lower memory limit for dynamic join than
the effective limit at a given time under the old behaviour. If
SQL0437 (rc=1) warnings are received after applying this APAR,
it may be necessary to increase the underlying value for
STMTHEAP.
In other cases, the existing underlying value for the AUTOMATIC
setting may reflect a higher memory limit for dynamic join than
the effective limit at a given time under the old behaviour.
After applying this APAR fix, if performance is degraded due to
increased query compilation time, it may be necessary to
decrease the underlying value for STMTHEAP.
Updates can be performed dynamically as follows:
db2 connect to <db>
db2 update db cfg for <db> using STMTHEAP <new value>
AUTOMATIC |
