This parameter defines the maximum number
of private or shared memory pages that an operation that requires
sort heap memory allocates.
- Configuration type
- Database
- Parameter type
- Configurable online (requires a database connection)
- Configurable by member in a DB2® pureScale® environment
and in partitioned database environments.
- Propagation class
- Immediate
- Default [range]
-
- 32-bit platforms
- AUTOMATIC [16 - 524 288]
- The default value is 256.
- 64-bit platforms
- AUTOMATIC [16 - 4 294 967 295]
- The default value is 256.
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 sortheap parameter
to AUTOMATIC (minimum value of 256) unless DB2_WORKLOAD=ANALYTICS,
in which case it is set to a minimum value of 32768 (not AUTOMATIC).
- Unit of measure
- Pages (4 KB)
- When allocated
- As needed to run operations that require sort
memory.
- When freed
- Some memory might be freed dynamically when no
longer needed. All remaining memory is freed when the operation that
requires sort memory completes.
- AUTOMATIC
- The AUTOMATIC setting is used to enable self-tuning of sortheap by
STMM, and is only effective under the default shared sort memory model. Otherwise,
the underlying configured value reflects a fixed value.
The AUTOMATIC setting
enables the simulation processing that is required to support STMM
tuning, and when STMM is enabled self-tuning of the sortheap setting.
When self-tuning of sortheap is enabled, any
attempt to manually update the numeric value while you maintain the
automatic setting is temporary since the setting continues to be tuned
by STMM.
While it is possible to enable self-tuning for the sortheap parameter
and keep the sheapthres_shr parameter set to
a fixed value, sortheap cannot be set to a fixed
value if sheapthres_shr is set to AUTOMATIC.
The sortheap value is also updated to the AUTOMATIC
setting when the sheapthres_shr setting is updated
to AUTOMATIC, and any attempt to update sortheap to
a fixed value when sheapthres_shr is AUTOMATIC
fails with SQL5147N.
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 sortheap 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 sortheap parameter
to AUTOMATIC, and the DB2 Configuration
Advisor automatically configures a fixed value for the sortheap parameter.
Various runtime operations, including those
operations that are not true sort operations, allocate 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
The
sortheap setting guides the maximum
memory usage per operation. The allocated or target memory might
be less than
sortheap setting due to any of the
following factors:
- The operation that is run.
- The optimizer's plan choice.
- Whether total sort memory for a member or partition is being throttled
(see sheapthres, sheapthres_shr).
Multiple operations can be active concurrently during the execution
of a single SQL statement, each with their own
sortheap based
allowance. The concurrency of
sortheap consuming
operations significantly increases when the intra-parallelism or the
data partitioning feature is enabled, as multiple agents are typically
running operations that require separate
sortheap based
memory areas.
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 fail, 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.
- Recommendation
- When you are working with the sortheap setting,
you must consider the following factors:
- Appropriate indexes can minimize the use of the sort heap.
- Increase the size of the sortheap parameter
when frequent large sorts are required.
- When not using STMM to tune the sortheap parameter,
update the sortheap setting to a numeric value,
either by updating to a fixed value or MANUAL. Updating
the sortheap parameter to a numeric value avoids
the small performance cost of automatic sort simulation
- When you increase the value of the sortheap parameter,
you must examine whether the sheapthres and sheapthres_shr parameters
in the database manager configuration file also need to be adjusted.
- Ensure the ratio of sortheap:sheapthres_shr or sortheap:sheapthres is
sufficient to avoid triggering throttling of sortheap allowances,
especially in parallelized environments that use intra-parallelism
or the data partitioning feature.
- The sortheap size is used by the optimizer
in determining access paths. You must consider rebinding applications
by using the REBIND command after you change the
size of the sortheap parameter.
When
the sortheap value is updated dynamically, the
database manager immediately starts to use this new value for any
current or new operations.
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 private sorts only)
- POST_SHRTHRESHOLD_SORTS (for shared sorts only)
- POST_THRESHOLD_PEDS
- POST_THRESHOLD_PEAS
- POST_SHRTHRESHOLD_HASH_JOINS (for shared hash joins only)
- POST_THRESHOLD_HASH_JOINS (for private hash joins only)
- POST_THRESHOLD_HASH_GRPBYS
- POST_THRESHOLD_OLAP_FUNCS
- SORT_OVERFLOWS
- TQ_SORT_HEAP_REJECTIONS
- SORT_HEAP_ALLOCATED (private sort reservations)
- SORT_SHRHEAP_TOP (high water mark for shared sort reservations)
The SORT_HEAP_ALLOCATED, SORT_SHRHEAP_ALLOCATED, SORT_SHRHEAP_TOP
monitoring elements reflect reservation requests by sort heap-based
operators, 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 sort memory usage, use the MON_GET_MEMORY_POOL
routine.
For more information about monitoring sort memory usage,
see sheapthres - Sort heap threshold configuration parameter and sheapthres_shr - Sort heap threshold for shared sorts configuration parameter.