STV_INFLIGHT - Amazon Redshift

STV_INFLIGHT

Use the STV_INFLIGHT table to determine what queries are currently running on the cluster. If you're troubleshooting, it's helpful for checking the status of long-running queries.

STV_INFLIGHT does not show leader-node only queries. For more information, see Leader node–only functions. STV_INFLIGHT 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_HISTORY. 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.

Troubleshooting with STV_INFLIGHT

If you use STV_INFLIGHT to troubleshoot performance for a query, or a collection of queries, note the following:

  • Long-running open transactions generally increase load. These open transactions can result in longer running times for other queries.

  • Long-running COPY and ETL jobs can affect other queries running on the cluster, if they're taking a lot of compute resources. In most cases, moving these long-running jobs to times of low use increases performance for reporting or analytics workloads.

  • There are views that provide related information to STV_INFLIGHT. These include STL_QUERYTEXT, which captures the query text for SQL commands, and SVV_QUERY_INFLIGHT, which joins STV_INFLIGHT to STL_QUERYTEXT. You can also use STV_RECENTS with STV_INFLIGHT for troubleshooting. For example, STV_RECENTS can indicate if specific queries are in a Running or Done state. Combining this information with results from STV_INFLIGHT can give you more information about a query's properties and compute-resource impact.

You can also monitor running queries using the Amazon Redshift console.

Table columns

Column name Data type Description
userid integer ID of user who generated entry.
slice integer Slice where the query is running.
query integer Query ID. Can be used to join various other system tables and views.
label character(320) Either the name of the file used to run the query or a label defined with a SET QUERY_GROUP command. If the query is not file-based or the QUERY_GROUP parameter is not set, this field is blank.
xid bigint Transaction ID.
pid integer Process ID. All of the queries in a session are run in the same process, so this value remains constant if you run a series of queries in the same session. You can use this column to join to the STL_ERROR table.
starttime timestamp Time that the query started.
text character(100) Query text, truncated to 100 characters if the statement exceeds that limit.
suspended integer Whether the query is suspended or not. 0 = false; 1 = true.
insert_pristine integer Whether write queries are/were able to run while the current query is/was running. 1 = no write queries allowed. 0 = write queries allowed. This column is intended for use in debugging.
concurrency_scaling_status integer

Indicates whether the query ran on the main cluster or on a concurrency scaling cluster, Possible values are as follows:

0 - Ran on the main cluster

1 - Ran on a concurrency scaling cluster

Sample queries

To view all active queries currently running on the database, type the following query:

select * from stv_inflight;

The sample output below shows two queries currently running, including the STV_INFLIGHT query itself and a query that was run from a script called avgwait.sql:

select slice, query, trim(label) querylabel, pid, starttime, substring(text,1,20) querytext from stv_inflight; slice|query|querylabel | pid | starttime | querytext -----+-----+-----------+-----+--------------------------+-------------------- 1011 | 21 | | 646 |2012-01-26 13:23:15.645503|select slice, query, 1011 | 20 |avgwait.sql| 499 |2012-01-26 13:23:14.159912|select avg(datediff( (2 rows)

The following query selects several columns, including concurrency_scaling_status. This column indicates whether queries are being sent to the concurrency-scaling cluster. If the value is 1 for some results, it's an indication that concurrency-scaling compute resources are being used. For more information, see Working with concurrency scaling.

select userid, query, pid, starttime, text, suspended, concurrency_scaling_status from STV_INFLIGHT;

The sample output shows one query being sent to the concurrency scaling cluster.

query | pid | starttime | text | suspended | concurrency_scaling_status --------+---------+----------------------------|------------------------|---------------|------------------------------- 1234567 | 123456 | 2012-01-26 13:23:15.645503 | select userid, query... 0 1 2345678 | 234567 | 2012-01-26 13:23:14.159912 | select avg(datediff(... 0 0 (2 rows)

For more tips on troubleshooting query performance, see Troubleshooting queries.