Performance Tuning for Content Manager

Chapter 8. Tuning DB2 for Content Manager 229

8.6.22 Sort heap size (SORTHEAP)

Impact

DB - High

Description

This parameter defines the maximum number of private memory pages to be

used for private sorts, or the maximum number of shared memory pages to be

used for shared sorts. If the sort is a private sort, then this parameter affects

agent private memory. If the sort is a shared sort, then this parameter affects the

database shared memory. Each sort has a separate sort heap that is allocated

as needed by the database manager. This sort heap is the area where data is

sorted. If directed by the optimizer, a smaller sort heap than the one specified by

this parameter is allocated using information provided by the optimizer.

Default values (range)

256 (16 - 524,288) 4-KB pages for 32-bit platforms

256 (16 - 1,048,575) 4-KB pages for 64-bit platforms

To update

Run the following DB2 command:

db2 update db cfg for <database name> using SORTHEAP <parameter value>

Our recommendation

The default values for both the Library Server and the Resource Manager will not

be sufficient and will have to be increased in conjunction with database

monitoring. Increasing this value unnecessarily can cause memory problems.

The database snapshot monitor elements of importance are:

 Total sorts (total_sorts): the total number of sorts that have been

performed.

 Sort overflows (sort_overflows): the total number of sorts that ran out of sort

heap and might have required disk space for temporary storage.

 Number of hash join overflows (hash_join_overflows): the number of times

that hash join data exceeded the available sort heap space.

Sort overflows divided by total sorts will provide the percentage of sorts that had

to overflow to disk. If this percentage is high, you should increase the value of

SORTHEAP.

If the number of hash join overflows is not zero, increase the value of

SORTHEAP by increments of 256 until it is.