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.