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)