Resources that affect query admission

The adaptive workload manager considers several different resources when admitting queries for execution.

Sort memory
In a query, sort memory is used by plan operators (such as SORT, GRPBY, and HSJN), and to store columnar vectors for queries against column-organized tables. The amount of sort memory that is used depends on the following factors:
  • The number of concurrent plan operators that need it
  • The number of tuples processed
  • The maximum amount of sort memory usage allowed per operation, as defined by the sortheap database configuration parameter
Use the following monitor elements to monitor the sort memory for a query:
sort_shrheap_allocated
The total amount of sort memory used by a query.
estimated_sort_shrheap_top
The estimated peak sort-memory usage for a query.
sort_shrheap_top
The actual peak sort-memory usage for a query.

These monitor elements are reported in the MON_GET_ACTIVITY table function, and the activity event monitor.

Agents
Agents are threads that perform work on behalf of queries. For the adaptive workload manager, the agent requirements for a query are determined by the query degree of parallelism. The total number of agents that the adaptive workload manager allows queries to utilize is determined by the following factors:
  • The setting of the wlm_agent_load_trgt database configuration parameter, which specifies the number of agents per physical CPU core
  • The number of physical CPU cores

Total agent resources = wlm_agent_load_trgt x physical CPU cores

Use the following monitor element to monitor agent requirements for a query:
effective_query_degree
The estimated number of agents required to execute a query.

This monitor element is reported in the MON_GET_ACTIVITY table function, and the activity event monitor.

Utility Heap Memory

Admission of the LOAD utility takes into consideration utility heap memory usage. This behavior is specific to LOAD and does not apply to any other activities controlled by the adaptive workload manager. Utility heap memory is used during construction of dictionaries for column-organized tables, among other operations. The configured utility heap memory is determined by the util_heap_sz database configuration parameter.

Use the following monitor element to monitor utility heap requirements for the LOAD utility:
util_heap_allocated
Utility heap memory used by the LOAD utility. Not applicable to other types of activities.

This monitor element is reported in the MON_GET_ACTIVITY table function, and the activity event monitor.