sheapthres_shr - Sort heap threshold for shared sorts configuration parameter

This parameter represents a soft limit on the total amount of shared sort memory reservation available to sort heap-based operations.

Configuration type

Database

Parameter type

Configurable online (requires a database connection)
Configurable by member in a Db2® pureScale® environments and in partitioned database environments.

Propagation class

Immediate

Default [range]

32-bit platforms: AUTOMATIC [250 - 524 288]; The default setting is 5000.
64-bit platforms: AUTOMATIC [250 - 2 147 483 647]; The default setting is 5000.

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 sheapthres_shr parameter to AUTOMATIC unless DB2_WORKLOAD=ANALYTICS, is set.

Unit of measure

Pages (4 KB)

AUTOMATIC

The AUTOMATIC setting is used to enable self-tuning of the sheapthres_shr parameter, allowing STMM to dynamically size the total shared sort memory available as workload requirements change. It is only effective under the default shared sort memory model and when

SELF_TUNING_MEM =
ON

. Otherwise, the underlying configured value reflects a fixed value.

When self-tuning of the sheapthres_shr parameter is enabled, any attempt to manually update the underlying configured value is temporary, since the setting continues to be tuned by STMM.

While it is possible to enable self-tuning for the sortheap configuration parameter when the sheapthres_shr parameter set to a fixed value, sortheap cannot be set to a fixed value if the sheapthres_shr parameter is set to AUTOMATIC. The sortheap parameter is also updated to the AUTOMATIC setting when sheapthres_shr is updated to AUTOMATIC. Any attempt to update the sortheap parameter to a fixed value when the sheapthres_shr parameter is set to AUTOMATIC fails with an error message.

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 sheapthres_shr 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 sheapthres_shr parameter to AUTOMATIC, and the Db2 Configuration Advisor automatically configures a fixed value for the sheapthres_shr parameter.

Various runtime operations, including those runtime operations that are not technically sort operations, allocate working 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

While the sortheap parameter setting guides the maximum memory usage per operation, the sheapthres_shr parameter setting guides the overall memory available to sort memory consumers per database per member. The sheapthres_shr parameter guides the overall memory that is available by reducing the amount of sort reservation that is allowed as the total reservation requested by all activities on the database approaches the sheapthres_shr value. When the total reservation requested reaches the sheapthres_shr value, only minimum reservations are allowed, and performance might degrade. When the total reservation reaches 1.25 times the sheapthres_shr value, requests for sort memory might be denied and an error (SQL0955C) is returned to the application.

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 fails, 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.

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 shared sorts)
POST_THRESHOLD_PEDS
POST_THRESHOLD_PEAS
POST_SHRTHRESHOLD_HASH_JOINS (for shared sorts)
POST_THRESHOLD_HASH_GRPBYS
POST_THRESHOLD_OLAP_FUNCS
SORT_OVERFLOWS
TQ_SORT_HEAP_REJECTIONS
SORT_HEAP_ALLOCATED (shared sort reservations)
SORT_SHRHEAP_TOP (high water mark for shared sort reservations)

The SORT_HEAP_ALLOCATED and SORT_SHRHEAP_ALLOCATED monitoring elements reflect reservation requests, 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 shared sort reservation levels, use the MON_GET_DATABASE routine. The following example shows a query that can be used to monitor the shared sort reservation levels:

select SORT_SHRHEAP_ALLOCATED, SORT_SHRHEAP_TOP from table (MON_GET_DATABASE (null))

This query returns the memory reservation levels in 4K units:

SORT_SHRHEAP_ALLOCATED SORT_SHRHEAP_TOP   
---------------------- -------------------- 
                128411               396405

For monitoring of shared sort memory usage, use the MON_GET_MEMORY_POOL routine. The following example shows a query that can be used to monitor the shared sort memory usage:

select memory_pool_used, memory_pool_used_hwm 
from table (mon_get_memory_pool(null,null,null)) 
where memory_pool_type='SHARED_SORT'

This query returns the memory allocation levels for shared sort in 1K units:

MEMORY_POOL_USED  MEMORY_POOL_USED_HWM
----------------- --------------------
           140574               140574

There is only one shared sort memory pool for all applications that run on a single database.