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

Sort Performance

P: n/a
Hello Everyone,

I have a mixed workload DB2 UDB 8.2 FP 14 Windows x64 database. The
database performs as per the expectations. I have just one small
question. The sort related parameters are configured as follows:

Sort heap threshold (4KB) (SHEAPTHRES) = 50000
Sort list heap (4KB) (SORTHEAP) = 4096

According to the snapshots samples (taken over 3 or 4 snapshots over a
period of day):

Total sorts = 503094649
Total sort time (ms) = 506093220
Sort overflows = 4730618
I notice that the sort overflow is less than 1 percent, and in my
opinion it does not require any tuning. Just want to know what your
opinion is, do you think that sort overflow < 1% is still a concern
and necessary tuning at DB2 or application level is still needed?

regards,
dotyet

Sep 4 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
just one more thing, when I said:
I notice that the sort overflow is less than 1 percent, and in my
opinion it does not require any tuning.
I meant to say, that instead of spending the necessary time on this
configuration parameter, I will look at the other ones and continue
therefrom.

regards,
dotyet
On Sep 4, 1:15 pm, dotyet <dot...@yahoo.comwrote:
Hello Everyone,

I have a mixed workload DB2 UDB 8.2 FP 14 Windows x64 database. The
database performs as per the expectations. I have just one small
question. The sort related parameters are configured as follows:

Sort heap threshold (4KB) (SHEAPTHRES) = 50000
Sort list heap (4KB) (SORTHEAP) = 4096

According to the snapshots samples (taken over 3 or 4 snapshots over a
period of day):

Total sorts = 503094649
Total sort time (ms) = 506093220
Sort overflows = 4730618

I notice that the sort overflow is less than 1 percent, and in my
opinion it does not require any tuning. Just want to know what your
opinion is, do you think that sort overflow < 1% is still a concern
and necessary tuning at DB2 or application level is still needed?

regards,
dotyet

Sep 4 '07 #2

P: n/a
"dotyet" <do****@yahoo.comwrote in message
news:11**********************@r29g2000hsg.googlegr oups.com...
Hello Everyone,

I have a mixed workload DB2 UDB 8.2 FP 14 Windows x64 database. The
database performs as per the expectations. I have just one small
question. The sort related parameters are configured as follows:

Sort heap threshold (4KB) (SHEAPTHRES) = 50000
Sort list heap (4KB) (SORTHEAP) = 4096

According to the snapshots samples (taken over 3 or 4 snapshots over a
period of day):

Total sorts = 503094649
Total sort time (ms) = 506093220
Sort overflows = 4730618
I notice that the sort overflow is less than 1 percent, and in my
opinion it does not require any tuning. Just want to know what your
opinion is, do you think that sort overflow < 1% is still a concern
and necessary tuning at DB2 or application level is still needed?

regards,
dotyet
1% sort overflow is pretty good. But it really depends on the size of the
sorts that are overflowing from memory to disk. If you have very large sorts
overflowing, then higher than 1% may also be good. You cannot reasonably
expect to allocate enough resources to have very large sorts done totally in
memory.
Sep 4 '07 #3

P: n/a
Nice.... Thats exactly what I was thinking.... This is not a pure OLTP
environment, and many a times, some sort of reporting jobs are
executing on stipulated time-periods, possibly causing the overflow
due to the sort size.

Many thanks.

regards,
dotyet

On Sep 4, 6:41 pm, "Mark A" <nob...@nowhere.comwrote:
"dotyet" <dot...@yahoo.comwrote in message

news:11**********************@r29g2000hsg.googlegr oups.com...
Hello Everyone,
I have a mixed workload DB2 UDB 8.2 FP 14 Windows x64 database. The
database performs as per the expectations. I have just one small
question. The sort related parameters are configured as follows:
Sort heap threshold (4KB) (SHEAPTHRES) = 50000
Sort list heap (4KB) (SORTHEAP) = 4096
According to the snapshots samples (taken over 3 or 4 snapshots over a
period of day):
Total sorts = 503094649
Total sort time (ms) = 506093220
Sort overflows = 4730618
I notice that the sort overflow is less than 1 percent, and in my
opinion it does not require any tuning. Just want to know what your
opinion is, do you think that sort overflow < 1% is still a concern
and necessary tuning at DB2 or application level is still needed?
regards,
dotyet

1% sort overflow is pretty good. But it really depends on the size of the
sorts that are overflowing from memory to disk. If you have very large sorts
overflowing, then higher than 1% may also be good. You cannot reasonably
expect to allocate enough resources to have very large sorts done totally in
memory.

Sep 5 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.