WLM system tables and views - Amazon Redshift
WLM service class IDs

WLM system tables and views

WLM configures query queues according to WLM service classes, which are internally defined. 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.
STL_WLM_RULE_ACTION Records details about actions resulting from WLM query monitoring rules associated with user-defined queues.
STV_WLM_QMR_CONFIG Records the configuration for WLM query monitoring rules (QMR).

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)

WLM service class IDs

The following table lists the IDs assigned to service classes.

ID Service class
1–4 Reserved for system use.
5 Used by the superuser queue.
6–13 Used by manual WLM queues that are defined in the WLM configuration.
14 Used by short query acceleration.
15 Reserved for maintenance activities run by Amazon Redshift.
100–107 Used by automatic WLM queue when auto_wlm is true.