sortheap - Sort heap size configuration parameter

Configuration type

Database

Parameter type

• Configurable online
• Configurable by member in a DB2® pureScale® environment

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 operations requiring sort memory

When freed

When operations requiring sort memory are 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 factors:

• Appropriate indexes can minimize the use of the sort heap.
• The following use sort heap memory. Increase the size of this parameter when any of these techniques are used:
  • hash join buffers
  • block index ANDing
  • merge joins
  • table in memory
  • dynamic bitmaps (used for index ANDing and Star Joins)
  • table queues (when there are multiple database agents processing a query)
  • partial early distinct operations
  • partial early aggregation operations
• 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.