Performance Tuning for Content Manager

230 Performance Tuning for Content Manager

When tuning this parameter, you should consider:

 Appropriate indexes can minimize the use of the sort heap.

 Hash join buffers and dynamic bitmaps use sort heap memory. Increase the

size of this parameter when these techniques are used.

 You might increase the size of this parameter when frequent large sorts are

required.

 When increasing the value of this parameter, examine whether the

SHEAPTHRES parameter in the database manager configuration file also

should be adjusted.

 The sort heap size is used by the optimizer in determining access paths.

Consider performing a rebind of the applications after changing this parameter.

8.6.23 Sort heap threshold (SHEAPTHRES)

Impact

DBM - High

Description

The SHEAPTHRES parameter is used differently for private and shared sorts:

 For private sorts, this parameter is an instance-wide

soft limit on the total

amount of memory that can be consumed by private sorts at any given time.

The size of the private sort memory is unrestricted. When the total

private-sort memory consumption for an instance reaches this limit, the

memory allocated for additional incoming private-sort requests will be

reduced considerably.

 For shared sorts, this parameter is a database-wide

hard limit on the total

amount of memory consumed by shared sorts at any given time. The size of

the shared sort memory area is statically predetermined at the time of the first

connection to a database based on SHEAPTHRES. When this limit is

reached, no further shared-sort memory requests will be allowed (until the

total shared-sort memory consumption falls below the limit specified by

SHEAPTHRES).

Examples of those operations that use the sort heap include: sorts, hash joins,

dynamic bitmaps, and operations where the table is in memory.

Explicit definition of the threshold prevents the database manager from using

excessive amounts of memory for large numbers of sorts.

Generally, you do not need to change the value of this parameter when moving

from a non-partitioned to a partitioned database environment. After you have

Chapter 8. Tuning DB2 for Content Manager 231

tuned the database and database manager configuration parameters on a single

database partition environment, in most cases the same values will work well in a

partitioned database environment.

The SHEAPTHRES value applies across the entire DB2 instance. To update this

parameter to different values on different nodes or partitions, create more than

one DB2 instance.

Default values (range)

20,000 (250 - 2,097,152) 4-KB pages for UNIX 32-bit platforms

10,000 (250 - 2,097,152) 4-KB pages for Windows platforms

20,000 (250 - 2,147,483,647) 4-KB pages for 64-bit platforms

To update

Run the following DB2 command:

db2 update dbm cfg using SHEAPTHRES <parameter value>

Our recommendation

The Content Manager installation program changes this value to 10,000 during

Library Server installation.

Use this value initially. Be sure that it is a multiple of the largest sortheap

parameters in your database instance. This parameter should be at least two

times the largest SORTHEAP defined for any database within the instance.

If you are doing private sorts and your system is not memory constrained, you

may consider setting the parameter value as follows:

1. Calculate the typical sort heap usage for each database:

TypicalConcurrentAgents * SORTHEAP

2. Add the typical sort heap for all the databases to get the total sort heap.

Monitor your system using snapshot to verify that your system is healthy. Try to

find the proper balance between sort performance and memory usage. You can

use the database manager system monitor to track the sort activity, using the

post threshold sorts (POST_THRESHOLD_SORTS) monitor element, which

indicates the number of sorts that have requested heaps after the sort heap

threshold has been exceeded. If this element value is a double digit, increase the

sort heap threshold.