DB2 10.5 for Linux, UNIX, and Windows

sheapthres - Sort heap threshold configuration parameter

This parameter represents a threshold on the total amount of private sort memory reservation available to sort-heap based operations on a member. Any sort memory reservation requests above this threshold might be reduced.

Configuration type

Database manager

Applies to

Database server with local and remote clients
Database server with local clients
Partitioned database server with local and remote clients

Parameter type

Configurable online

Switching between a 0 and non-0 setting is not an online change and requires restarting the instance. All other changes can be done online and requires an instance attachment.

Propagation class

Immediate

Default [Range]

32-bit platforms: 0 [0, 250 - 2097152]
64-bit platforms: 0 [0, 250 - 2147483647]

Unit of measure

Pages (4 KB)

While the sortheap parameter setting guides the maximum memory usage per operation, the sheapthres setting guides the overall private memory available to sort memory consumers per member. The sheapthres parameter guides the overall memory that is available by reducing the amount of sort reservation that is allowed when the total reservation requested by all activity on the member exceeds the sheapthres setting. The sort reservation that is granted is (sheapthres / total private sort requested * sortheap requested). At least 25% of the sortheap setting is always available to a memory consumer. The reservation request is not reduced below this amount.

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 (private sort)
POST_THRESHOLD_PEDS
POST_THRESHOLD_PEAS
POST_SHRTHRESHOLD_HASH_JOINS (private hash joins)
POST_THRESHOLD_HASH_GRPBYS
POST_THRESHOLD_OLAP_FUNCS
SORT_OVERFLOWS
TQ_SORT_HEAP_REJECTIONS
SORT_HEAP_ALLOCATED (private sort reservations)

The SORT_HEAP_ALLOCATED monitoring element reflects 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 private sort reservation levels, use the MON_GET_DATABASE routine. The following example shows a query that can be used to monitor the private sort reservation levels:

select SORT_HEAP_ALLOCATED from table (MON_GET_DATABASE (null))

This query returns the private sort memory reservation levels for the database in 4K units:

SORT_HEAP_ALLOCATED
------------------- 
             128411

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

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

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

EDU_ID               MEMORY_POOL_USED     MEMORY_POOL_USED_HWM
-------------------- -------------------- --------------------
                2058                  128                  128
                2058                  192                  192
                2058                 1280                 1280

  3 record(s) selected.