By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,740 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,740 IT Pros & Developers. It's quick & easy.

SQL 955 but snapshot shows memory still available

P: n/a
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

Dec 14 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Dec 16 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.