STV_EXEC_STATE - Amazon Redshift


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

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.

Some or all of the data in this table can also be found in the SYS monitoring view SYS_QUERY_DETAIL. The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.

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 completed.
segment integer Segment of the query that ran. A query segment is a series of steps.
step integer Step of the query segment that completed. A step is the smallest unit that a query performs.
starttime timestamp Time that the step ran.
currenttime timestamp Current time.
tasknum integer Query task process that is assigned to complete 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 completed 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 completed 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. A positive number in this column does not imply that the hash step ran 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.