sortheap - Sort heap size configuration parameter

This parameter defines the maximum number of private or shared memory pages that an operation that requires sort heap memory allocates.

Configuration type
Database
Parameter type
  • Configurable online (requires a database connection)
  • Configurable by member in a Db2® pureScale® environment and in partitioned database environments.
Propagation class
Immediate
Default [range]
32-bit platforms
AUTOMATIC [16 - 524 288]
The default value is 256.
64-bit platforms
AUTOMATIC [16 - 4 294 967 295]
The default value is 256.
Note: The default value is subject to change by the Db2 Configuration Advisor as part of database creation, which is run by default in a nonpartitioned database environment. The Db2 Configuration Advisor sets the value of the sortheap parameter to AUTOMATIC (minimum value of 256) unless DB2_WORKLOAD=ANALYTICS, in which case it is set to a minimum value of 32768 (not AUTOMATIC).
Unit of measure
Pages (4 KB)
When allocated
As needed to run operations that require sort memory.
When freed
Some memory might be freed dynamically when no longer needed. All remaining memory is freed when the operation that requires sort memory completes.
AUTOMATIC

The AUTOMATIC setting is used to enable self-tuning of sortheap by STMM, and is only effective under the default shared sort memory model. Otherwise, the underlying configured value reflects a fixed value.

The AUTOMATIC setting enables the simulation processing that is required to support STMM tuning, and when STMM is enabled self-tuning of the sortheap setting. When self-tuning of sortheap is enabled, any attempt to manually update the numeric value while you maintain the automatic setting is temporary since the setting continues to be tuned by STMM.

The AUTOMATIC setting cannot be specified on the sortheap configuration parameter if the wlm_admission_ctrl configuration parameter is set to YES. An sqlcode of SQL1668N RC=12 will be returned if this is attempted.

While it is possible to enable self-tuning for the sortheap parameter and keep the sheapthres_shr parameter set to a fixed value, sortheap cannot be set to a fixed value if sheapthres_shr is set to AUTOMATIC. The sortheap value is also updated to the AUTOMATIC setting when the sheapthres_shr setting is updated to AUTOMATIC, and any attempt to update sortheap to a fixed value when sheapthres_shr is AUTOMATIC fails with SQL5147N.

When set to AUTOMATIC, self-tuning of sheapthres_shr by the STMM is enabled, subject to other configuration requirements. The following conditions must be true to allow self-tuning of sheapthres_shr to occur:
  • STMM is enabled (SELF_TUNING_MEM=ON).
  • Shared sort memory model is enabled (sheapthres is set to 0).
  • The sortheap parameter is set to AUTOMATIC.
  • DB2_WORKLOAD=ANALYTICS is not set.
  • For a partitioned database environment, the database is explicitly activated.

Self-tuning of the sort parameter is not supported for workloads that access column-organized tables, and is disabled when DB2_WORKLOAD =ANALYTICS. In such cases, set the value of the sortheap parameter to a fixed value, not AUTOMATIC, otherwise suboptimal performance or out-of-memory conditions might occur. When the value of the DB2_WORKLOAD registry variable is set to ANALYTICS, you cannot set the sortheap parameter to AUTOMATIC, and the Db2 Configuration Advisor automatically configures a fixed value for the sortheap parameter.

Various runtime operations, including those operations that are not true sort operations, allocate memory that is based on the sortheap setting. The following operations allocate working memory that is based on the sortheap setting:
  • Sort
  • Hash join
  • Index ANDing
  • Block index ANDing
  • Table in memory
  • Merge join
  • Scalar aggregation
  • Partial early distinct and early aggregation operations
  • Table queues
  • Hashed GROUP BY
  • column-organized data processing
The sortheap setting guides the maximum memory usage per operation. The allocated or target memory might be less than sortheap setting due to any of the following factors:
  • The operation that is run.
  • The optimizer's plan choice.
  • Whether total sort memory for a member or partition is being throttled (see sheapthres, sheapthres_shr).
Multiple operations can be active concurrently during the execution of a single SQL statement, each with their own sortheap based allowance. The concurrency of sortheap consuming operations significantly increases when the intra-parallelism or the data partitioning feature is enabled, as multiple agents are typically running operations that require separate sortheap based memory areas.
There are three sort memory models possible. The model in use depends upon a number of elements in the configuration.
Shared sort memory model
The shared sort memory model is the default model and is in effect whenever sheapthres = 0. The sheapthres setting guides throttling for the private sort model, and a setting of 0 disables private sort memory. Under the shared sort model, all sortheap allocations are from the shared sort heap (sheapthres_shr), which is part of database shared memory (database_memory). The shared sort memory model is the only model where STMM tuning of sortheap and sheapthres_shr can occur.
Private sort memory model
The private sort memory model is active whenever sheapthres is not equal to zero and the configuration also does not enable shared sort memory. Under the private sort memory model, sortheap allocations are only allocated from private memory. Operations specifically requiring shared sort memory are not valid and return errors. No STMM sort-tuning takes place under this model.
Hybrid sort memory model
The hybrid sort memory model is active whenever sheapthres is not equal to zero, but the configuration dictates that shared sort memory is made available for certain operations. Operations not requiring shared sort memory are allocated from private memory. No STMM sort-tuning takes place under this model.
Any one of the following configuration settings can enable shared sort memory:
  • Intra-parallelism is enabled (INTRA_PARALLEL = YES)
  • Connection Concentrator is enabled (MAX_CONNECTIONS > MAX_COORDAGENTS)
  • DB2_WORKLOAD=ANALYTICS
If shared sort memory is not enabled, the shared sort memory model and the hybrid sort memory models are not active and the following operations fail:
  • Loading data into an XML table
  • Column-organized query processing
  • Applications requesting intra-parallel processing
If any of the operations in the preceding list fail, enable the shared sort memory model by completing all of the following steps:
  • Set the sheapthres configuration parameter to 0.
  • Recycle the Db2 instance. To recycle the Db2 instance, run the db2stop command followed by the db2start command.
  • Configure the sheapthres_shr parameter appropriately.
Recommendation
When you are working with the sortheap setting, you must consider the following factors:
  • Appropriate indexes can minimize the use of the sort heap.
  • Increase the size of the sortheap parameter when frequent large sorts are required.
  • When not using STMM to tune the sortheap parameter, update the sortheap setting to a numeric value, either by updating to a fixed value or MANUAL. Updating the sortheap parameter to a numeric value avoids the small performance cost of automatic sort simulation
  • When you increase the value of the sortheap parameter, you must examine whether the sheapthres and sheapthres_shr parameters in the database manager configuration file also need to be adjusted.
  • Ensure the ratio of sortheap:sheapthres_shr or sortheap:sheapthres is sufficient to avoid triggering throttling of sortheap allowances, especially in parallelized environments that use intra-parallelism or the data partitioning feature.
  • The sortheap size is used by the optimizer in determining access paths. You must consider rebinding applications by using the REBIND command after you change the size of the sortheap parameter.

When the sortheap value is updated dynamically, the database manager immediately starts to use this new value for any current or new operations.

Monitoring

There are numerous monitoring elements available.

For general monitoring, you can use the following monitoring elements:
  • ACTIVE_SORTS
  • TOTAL_SECTION_SORT_TIME
  • TOTAL_SECTION_SORT_PROC_TIME
  • TOTAL_SECTION_SORTS
  • TOTAL_SORTS
For monitoring of constrained total sort memory configuration, you can use the following monitoring elements:
  • POST_THRESHOLD_SORTS (for private sorts only)
  • POST_SHRTHRESHOLD_SORTS (for shared sorts only)
  • POST_THRESHOLD_PEDS
  • POST_THRESHOLD_PEAS
  • POST_SHRTHRESHOLD_HASH_JOINS (for shared hash joins only)
  • POST_THRESHOLD_HASH_JOINS (for private hash joins only)
  • POST_THRESHOLD_HASH_GRPBYS
  • POST_THRESHOLD_OLAP_FUNCS
  • SORT_OVERFLOWS
  • TQ_SORT_HEAP_REJECTIONS
  • SORT_HEAP_ALLOCATED (private sort reservations)
  • SORT_SHRHEAP_TOP (high water mark for shared sort reservations)

The SORT_HEAP_ALLOCATED, SORT_SHRHEAP_ALLOCATED, SORT_SHRHEAP_TOP monitoring elements reflect reservation requests by sort heap-based operators, not the actual amount of memory that is allocated. It is normal for operations to not fully allocate all of the requested reservation amounts.

For monitoring of sort memory usage, use the MON_GET_MEMORY_POOL routine.

For more information about monitoring sort memory usage, see sheapthres - Sort heap threshold configuration parameter and sheapthres_shr - Sort heap threshold for shared sorts configuration parameter.