sys.wlm_active_pool

This view returns the currently active WLM resource pools and identifies their profile and other attributes, including the number of pending queries for each prioritized queue.

Column Name Data Type Description
name text Name of the resource pool.
profile_name text Name of the profile where the resource pool was created.
memory_requested text Percentage or amount of available memory requested for the pool. This column returns one of three character strings:
  • A percentage, such as 22%
  • A value in MB, such as 32768MB
  • remainder, which means the resource pool is requesting whatever memory is left over (equivalent to Any in the SMC).
memory_per_query_bytes bigint Actual amount of memory available per query, in bytes.
temp_space_requested text Percentage or amount of temporary space requested for spilling, as a percentage. This column returns one of three character strings:
  • A percentage, such as 22%
  • A value in MB, such as 32768MB
  • remainder, which means the resource pool is requesting whatever temporary space is left over (equivalent to Any in the SMC).
temp_space_per_query_bytes bigint Actual amount of temporary space for spilling available per query, in bytes.
max_concurrency bigint Maximum number of concurrent queries that can run in this pool.
min_concurrency bigint Minimum number of concurrent queries that can run in this pool.
queue_size bigint Number of queries that can wait in the queue for this pool.
running bigint Number of queries running on workers assigned to the pool.
low_pending, normal_pending, high_pending, critical_pending bigint Number of queries waiting in each prioritized queue (low, normal, high, critical).

Example

For example:
yellowbrick=# select * from sys.wlm_active_pool;
-[ RECORD 1 ]--------------+---------------
name                       | system
profile_name               | shortquerybias
memory_requested           | 11264MB
memory_per_query_bytes     | 3936354304
temp_space_requested       | 163840MB
temp_space_per_query_bytes | 57265881088
max_concurrency            | 3
min_concurrency            | 2
queue_size                 | 100
running                    | 0
low_pending                | 0
normal_pending             | 0
high_pending               | 0
critical_pending           | 0
-[ RECORD 2 ]--------------+---------------
name                       | shortquerypool
profile_name               | shortquerybias
memory_requested           | remainder
memory_per_query_bytes     | 5722079232
temp_space_requested       | remainder
temp_space_per_query_bytes | 73345794048
max_concurrency            | 20
min_concurrency            | 1
queue_size                 | 100
running                    | 0
low_pending                | 0
normal_pending             | 0
high_pending               | 0
critical_pending           | 0