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
- 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.
- 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.
Monitoring
There are numerous monitoring elements available.
- ACTIVE_SORTS
- TOTAL_SECTION_SORT_TIME
- TOTAL_SECTION_SORT_PROC_TIME
- TOTAL_SECTION_SORTS
- TOTAL_SORTS
- 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.
select SORT_HEAP_ALLOCATED from table (MON_GET_DATABASE (null))
SORT_HEAP_ALLOCATED
-------------------
128411
select edu_id, memory_pool_used, memory_pool_used_hwm
from table (mon_get_memory_pool(null,null,null))
where memory_pool_type='SORT'
EDU_ID MEMORY_POOL_USED MEMORY_POOL_USED_HWM
-------------------- -------------------- --------------------
2058 128 128
2058 192 192
2058 1280 1280
3 record(s) selected.