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


Use the STV_EXEC_STATE table to find out information about queries and query steps that are actively running on Amazon Redshift.

This information is usually used only to troubleshoot engineering issues. The views SVV_QUERY_STATE and SVL_QUERY_SUMMARY extract their information from STV_EXEC_STATE.

STV_EXEC_STATE is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of Data in System Tables and Views.

Table Columns

Column Name Data Type Description
userid integer ID of user who generated entry.
query integer Query ID. Can be used to join various other system tables and views.
slice integer Node slice where the step executed.
segment integer Segment of the query that executed. A query segment is a series of steps.
step integer Step of the query segment that executed. A step is the smallest unit of query execution.
starttime timestamp Time that the step executed.
currenttime timestamp Current time.
tasknum integer Query task process that is assigned to the execute the step.
rows bigint Number of rows processed.
bytes bigint Number of bytes processed.
label char(256) Step label, which consists of a query step name and, when applicable, table ID and table name (for example, scan tbl=100448 name =user). Three-digit table IDs usually refer to scans of transient tables. When you see tbl=0, it usually refers to a scan of a constant value.
is_diskbased char(1) Whether this step of the query was executed as a disk-based operation: true (t) or false (f). Only certain steps, such as hash, sort, and aggregate steps, can go to disk. Many types of steps are always executed in memory.
workmem bigint Number of bytes of working memory assigned to the step.
num_parts integer Number of partitions a hash table is divided into during a hash step. The hash table is partitioned when it is estimated that the entire hash table might not fit into memory. A positive number in this column does not imply that the hash step executed as a disk-based operation. Check the value in the IS_DISKBASED column to see if the hash step was disk-based.
is_rrscan char(1) If true (t), indicates that range-restricted scan was used on the step. Default is false (f).
is_delayed_scan char(1) If true (t), indicates that delayed scan was used on the step. Default is false (f).

Sample Queries

Rather than querying STV_EXEC_STATE directly, Amazon Redshift recommends querying SVL_QUERY_SUMMARY or SVV_QUERY_STATE to obtain the information in STV_EXEC_STATE in a more user-friendly format. See the SVL_QUERY_SUMMARY or SVV_QUERY_STATE table documentation for more details.