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

SVV_QUERY_STATE

Use SVV_QUERY_STATE to view information about the execution of currently running queries.

The SVV_QUERY_STATE view contains a data subset of the STV_EXEC_STATE table.

SVV_QUERY_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.
seg integer Number of the query segment that is executing. A query consists of multiple segments, and each segment consists of one or more steps. Query segments can run in parallel. Each segment runs in a single process.
step integer Number of the query step that is executing. A step is the smallest unit of query execution. Each step represents a discrete unit of work, such as scanning a table, returning results, or sorting data.
maxtime interval Maximum amount of time (in microseconds) for this step to execute.
avgtime interval Average time (in microseconds) for this step to execute.
rows bigint Number of rows produced by the step that is executing.
bytes bigint Number of bytes produced by the step that is executing.
cpu bigint For internal use.
memory bigint For internal use.
rate_row double precision Rows-per-second rate since the query started, computed by summing the rows and dividing by the number of seconds from when the query started to the current time.
rate_byte double precision Bytes-per-second rate since the query started, computed by summing the bytes and dividing by the number of seconds from when the query started to the current time.
label character(25) Query label: a name for the step, such as scan or sort.
is_diskbased character(1) Whether this step of the query is executing 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 Amount of working memory (in bytes) assigned to the query 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 character(1) If true (t), indicates that range-restricted scan was used on the step. Default is false (f).
is_delayed_scan character(1) If true (t), indicates that delayed scan was used on the step. Default is false (f).

Sample Queries

Determining the Processing Time of a Query by Step

The following query shows how long each step of the query with query ID 279 took to execute and how many data rows Amazon Redshift processed:

Copy
select query, seg, step, maxtime, avgtime, rows, label from svv_query_state where query = 279 order by query, seg, step;

This query retrieves the processing information about query 279, as shown in the following sample output:

Copy
query | seg | step | maxtime | avgtime | rows | label ------+---------+------+---------+---------+---------+------------------- 279 | 3 | 0 | 1658054 | 1645711 | 1405360 | scan 279 | 3 | 1 | 1658072 | 1645809 | 0 | project 279 | 3 | 2 | 1658074 | 1645812 | 1405434 | insert 279 | 3 | 3 | 1658080 | 1645816 | 1405437 | distribute 279 | 4 | 0 | 1677443 | 1666189 | 1268431 | scan 279 | 4 | 1 | 1677446 | 1666192 | 1268434 | insert 279 | 4 | 2 | 1677451 | 1666195 | 0 | aggr (7 rows)

Determining If Any Active Queries Are Currently Running on Disk

The following query shows if any active queries are currently running on disk:

Copy
select query, label, is_diskbased from svv_query_state where is_diskbased = 't';

This sample output shows any active queries currently running on disk:

Copy
query | label | is_diskbased -------+--------------+-------------- 1025 | hash tbl=142 | t (1 row)