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.
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)