SORTSHRHEAPUTIL threshold

The SORTSHRHEAPUTIL threshold specifies the maximum shared sort memory that may be requested by a query.

Attention: This feature is available in Db2 Version 11.5 Mod Pack 2 and later versions.

The threshold allows users to place a limit on the sort memory required (both estimated and actually used memory) by a query, and conditionally enforce the limit when the high sort memory usage is blocking the adaptive workload manager (AWLM) admission control queue. Similar to other WLM thresholds, this threshold can be used to terminate queries with high sort memory usage, or collect information on them so that they may be tuned for subsequent execution. This threshold can be used to automatically resolve resource-based queuing that occurs due to queries with excessive memory requirements.

Type: Activity
Definition domain: Database, service superclass, service subclass, work action, workload and statement
Enforcement scope: Database partition
Tracked work: Recognized coordinator and nested activities (see Activities)
Queuing: No
Unit: Percentage of the total configured shared sort memory for the database (sheapthres_shr)
Predictive or reactive: Both

The threshold will be evaluated reactively at runtime to check if the actual sort memory usage exceeds the threshold value and whether or not the query is blocking the adaptive workload manager admission control queue. When the adaptive workload manager is enabled, the threshold will also be checked predictively at query admission time to determine if the estimated/required memory for the query exceeds the threshold value.

Example 1

Terminate any query that uses more than 75% of the database shared sort memory.

CREATE THRESHOLD TH1 FOR DATABASE WHEN SORTSHRHEAPUTIL > 75 PERCENT STOP EXECUTION

Example 2

Collect information for any query that uses more than 50% of the database shared sort memory, but allow the query to complete execution.

CREATE THRESHOLD TH2 FOR DATABASE WHEN SORTSHRHEAPUTIL > 50 PERCENT COLLECT ACTIVITY DATA WITH DETAILS CONTINUE

Example 3

Terminate any query that uses more than 60% of the database shared sort memory if the query is blocking the adaptive workload manager admission queue for more than 5 minutes.

CREATE THRESHOLD TH3 FOR DATABASE WHEN SORTSHRHEAPUTIL > 60 PERCENT AND BLOCKING ADMISSION FOR MORE THAN 5 MINUTES STOP EXECUTION

Important: Fix pack fallback to 11.5 GA without first dropping all created SORTSHRHEAPUTIL thresholds will cause a failure in the previous level and prevent the database from activating.