Activity sort memory limits

Use activity sort memory limits to place an upper limit on the amount of shared sort memory an individual activity can consume. The limit is expressed as a percentage of the configured shared sort memory (SHEAPTHRES_SHR), where the percentage ranges from 10 to 100.

When a sort memory limit is applied to an activity the per-operator sortheap value may be scaled back at runtime to keep the overall query memory usage within the memory limit.

You can configure the activity sort memory limit for all activities on the database using the ACT_SORTMEM_LIMIT database configuration parameter. For activities executing in a particular service superclass or subclass, use the ACTIVITY SORTMEM LIMIT clause on CREATE/ALTER SERVICE CLASS DDL statements. When you apply multiple limits to the same activity, the most restrictive limit will be used. The activity sort memory limit used for an activity is determined when the activity is first admitted for execution. The applied sort memory limit will not change if the activity is remapped to a different service subclass at runtime.

Sort memory limits are only operational when the adaptive workload manager is enabled and will not apply to any queries that bypass the adaptive workload manager. Use activity sort memory limits to minimize queuing in the adaptive workload manager that is caused by queries with excessively high sort memory requirements.

Note: Setting an activity sort memory limit too low may result in reduced performance for queries.

Examples

  • Example 1

    A user creates two service classes and gives them 1000 shares each, entitling each service class to 50% of the database resources. The user uses soft resource shares for the HIGHPRI service class so that this class may exceed it's resource entitlement when there is spare capacity, and hard resource shares for the LOWPRI service class so that work in this service class is limited to 50% of the resources.

    CREATE SERVICE CLASS HIGHPRI FOR WORKLOAD TYPE MIXED SOFT RESOURCE SHARES 1000
CREATE SERVICE CLASS LOWPRI FOR WORKLOAD TYPE MIXED HARD RESOURCE SHARES 1000

    The user notices that occasionally a query executes in the LOWPRI service class which needs 50% of the total memory. When this query is submitted other work in the LOWPRI service class queues because this query is using the entire service class entitlement for memory resources. The user decides to place a 20% activity sort memory limit on the LOWPRI service class so that when this query arrives its memory usage will be reduced and other work will be able to execute in the service class concurrently.

    ALTER SERVICE CLASS LOWPRI ACTIVITY SORTMEM LIMIT 20