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 (minimum value of 5000) unless DB2_WORKLOAD=ANALYTICS,
in which case it is set to a minimum value of 1000000 (not AUTOMATIC).
- 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.