SVV_QUERY_STATE - Amazon Redshift
Table columnsSample queries
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

SVV_QUERY_STATE

Use SVV_QUERY_STATE to view information about the runtime 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.

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.

Note

This view is only available when querying provisioned clusters.

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 running. 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 running. A step is the smallest unit of query runtime. 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 run.
avgtime interval Average time (in microseconds) for this step to run.
rows bigint Number of rows produced by the step that is running.
bytes bigint Number of bytes produced by the step that is running.
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 running 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 performed 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. A positive number in this column does not imply that the hash step runs 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 run and how many data rows Amazon Redshift processed: 

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: 

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: 

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

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

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