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