SQL 955 but snapshot shows memory still available

[sethwai]

Hi,

We received an SQL 955 on a query. Since a snapshot showed that
database wide memory was still available (high water mark was below
max) I figured it was the SORTHEAP parm being too small. Yet the query
was rerun later when system was not as busy and it ran fine. I don't
get it. Can anyone shed light on this?

db2 8.1 fixpak 11
aix 5.3
DPF with 5 partitions

Sort heap threshold (4KB) (SHEAPTHRES) = 81920
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 8192

Node number = 0
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 81920
High water mark (bytes) = 34144256
Configured size (bytes) = 335560704

Node number = 1
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 164773888
High water mark (bytes) = 265895936
Configured size (bytes) = 335560704

Node number = 2
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 141410304
High water mark (bytes) = 267698176
Configured size (bytes) = 335560704

Node number = 3
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 150683648
High water mark (bytes) = 278200320
Configured size (bytes) = 335560704

Node number = 4
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 149864448
High water mark (bytes) = 263651328
Configured size (bytes) = 335560704
Thanks.

Lew

[Phil Sherman]

SHEAPTHRES is a "soft" limit for private sorts and a "hard" limit for
shared sorts. Your query runs with lighter workloads which indicates
that you've hit the "hard" limit for shared sorts.

You have three options available:
1. Decrease the size of SORTHEAP. This will allow more concurrent sorts
to run.

2. Increase the size os SHEAPTHRES. This needs to be in increments of
SORTHEAP and will allow additional sorts ro run concurrently.

3. Run the query only when workloads are lighter.

The statistics indicate that there should be enough storage available
for one more shared sort. It may be that the query needed to allocate
space for two concurrent shared sorts and didn't have enough space to do
that.

If you can't increase the SHEAPTHRES value, try decreasing SORTHEAP to
6144 and monitoring the number of sorts that overflow. If the increase
in overflows is acceptable, that may fix the problem.
Phil Sherman

se*****@yahoo.com wrote:

Hi,

We received an SQL 955 on a query. Since a snapshot showed that
database wide memory was still available (high water mark was below
max) I figured it was the SORTHEAP parm being too small. Yet the query
was rerun later when system was not as busy and it ran fine. I don't
get it. Can anyone shed light on this?

db2 8.1 fixpak 11
aix 5.3
DPF with 5 partitions

Sort heap threshold (4KB) (SHEAPTHRES) = 81920
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 8192

Node number = 0
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 81920
High water mark (bytes) = 34144256
Configured size (bytes) = 335560704

Node number = 1
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 164773888
High water mark (bytes) = 265895936
Configured size (bytes) = 335560704

Node number = 2
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 141410304
High water mark (bytes) = 267698176
Configured size (bytes) = 335560704

Node number = 3
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 150683648
High water mark (bytes) = 278200320
Configured size (bytes) = 335560704

Node number = 4
Memory Pool Type = Shared Sort Heap
Current size (bytes) = 149864448
High water mark (bytes) = 263651328
Configured size (bytes) = 335560704
Thanks.

Lew