Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

WLM System Tables and Views

WLM configures query queues according to internally-defined WLM service classes. Amazon Redshift creates several internal queues according to these service classes along with the queues defined in the WLM configuration. The terms queue and service class are often used interchangeably in the system tables. The superuser queue uses service class 5. User-defined queues use service class 6 and greater.

You can view the status of queries, queues, and service classes by using WLM-specific system tables. Query the following system tables to do the following:

  • View which queries are being tracked and what resources are allocated by the workload manager.

  • See which queue a query has been assigned to.

  • View the status of a query that is currently being tracked by the workload manager.

Table Name Description
STL_WLM_ERROR Contains a log of WLM-related error events.
STL_WLM_QUERY Lists queries that are being tracked by WLM.
STV_WLM_CLASSIFICATION_CONFIG Shows the current classification rules for WLM.
STV_WLM_QUERY_QUEUE_STATE Records the current state of the query queues.
STV_WLM_QUERY_STATE Provides a snapshot of the current state of queries that are being tracked by WLM.
STV_WLM_QUERY_TASK_STATE Contains the current state of query tasks.
STV_WLM_SERVICE_CLASS_CONFIG Records the service class configurations for WLM.
STV_WLM_SERVICE_CLASS_STATE Contains the current state of the service classes.

You use the task ID to track a query in the system tables. The following example shows how to obtain the task ID of the most recently submitted user query:

select task from stl_wlm_query where exec_start_time =(select max(exec_start_time) from stl_wlm_query); task ------ 137 (1 row)
The following example displays queries that are currently executing or waiting in various service classes (queues). This query is useful in tracking the overall concurrent workload for Amazon Redshift:
select * from stv_wlm_query_state order by query; xid |task|query|service_| wlm_start_ | state |queue_ | exec_ | | |class | time | |time | time ----+----+-----+--------+-------------+---------+-------+-------- 2645| 84 | 98 | 3 | 2010-10-... |Returning| 0 | 3438369 2650| 85 | 100 | 3 | 2010-10-... |Waiting | 0 | 1645879 2660| 87 | 101 | 2 | 2010-10-... |Executing| 0 | 916046 2661| 88 | 102 | 1 | 2010-10-... |Executing| 0 | 13291 (4 rows)