sortheap - Sort heap size configuration parameter

Configuration type

Database

Applies to

OLAP functions

Parameter type

Configurable Online

Propagation class

Immediate

Default [range]

32-bit platforms

Automatic [16 - 524 288]

64-bit platforms

Automatic [16 - 4 194 303]

Note: The default value is subject to change by the DB2® Configuration Advisor after initial database creation.

Unit of measure

Pages (4 KB)

When allocated

As needed to perform sorts

When freed

When sorting is complete

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.

When this parameter is set to AUTOMATIC, it is enabled for self tuning. This allows the memory tuner to dynamically size the memory area controlled by this parameter as the workload requirements change.

The value of sortheap is tuned together with the sheapthres_shr parameter, therefore disabling self tuning of the sortheap parameter can not be done without disabling self tuning of the sheapthres_shr parameter. Enabling self tuning of the sheapthres_shr parameter automatically enables self tuning of the sortheap parameter. The sortheap parameter can, however, be enabled for self tuning without the sheapthres_shr parameter being AUTOMATIC.

Automatic tuning of sortheap is allowed only when the database manager configuration parameter sheapthres is set to 0.

Automatic tuning of this configuration parameter will only occur when self tuning memory is enabled for the database (the self_tuning_mem configuration parameter is set to ON.)

Recommendation: When working with the sort heap, you should consider the following:

• Appropriate indexes can minimize the use of the sort heap.
• Hash join buffers, block index ANDing, merge join, table in memory and dynamic bitmaps (used for index ANDing and Star Joins) use sort heap memory. Increase the size of this parameter when these techniques are used.
• Increase the size of this parameter when frequent large sorts are required.
• When increasing the value of this parameter, you should examine whether the sheapthres and sheapthres_shr parameters in the database manager configuration file also need to be adjusted.
• The sort heap size is used by the optimizer in determining access paths. You should consider rebinding applications (using the REBIND command) after changing this parameter.

When the sortheap value is updated, the database manager will immediately start using this new value for any current or new sorts.