Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

SVL_QUERY_REPORT

Amazon Redshift creates the SVL_QUERY_REPORT view from a UNION of a number of Amazon Redshift STL system tables to provide information about executed query steps.

This view breaks down the information about executed queries by slice and by step, which can help with troubleshooting node and slice issues in the Amazon Redshift cluster.

SVL_QUERY_REPORT 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.
slice integer Data slice where the step executed.
segment integer

Segment number.

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 Query step that executed.
start_time timestamp Exact time when the segment started executing, with 6 digits of precision for fractional seconds. For example: 2012-12-12 11:29:19.131358
end_time timestamp Exact time when the segment finished executing, with 6 digits of precision for fractional seconds. For example: 2012-12-12 11:29:19.131467
elapsed_time bigint Time (in microseconds) that it took the segment to execute.
rows bigint Number of rows produced by the step (per slice). This number represents the number of rows for the slice that result from the execution of the step, not the number of rows received or processed by the step. In other words, this is the number of rows that survive the step and are passed on to the next step.
bytes bigint Number of bytes produced by the step (per slice).
label char(256) Step label, which consists of a query step name and, when applicable, table ID and table name (for example, scan tbl=100448 name =user). Three-digit table IDs usually refer to scans of transient tables. When you see tbl=0, it usually refers to a scan of a constant value.
is_diskbased character(1) Whether this step of the query was executed 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. This value is the query_working_mem threshold allocated for use during execution, not the amount of memory that was actually used
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).
rows_pre_filter bigint For scans of permanent tables, the total number of rows emitted before filtering rows marked for deletion (ghost rows) and before applying user-defined query filters.

Sample Queries

The following query demonstrates the data skew of the returned rows for the query with query ID 279. Use this query to determine if database data is evenly distributed over the slices in the data warehouse cluster:

Copy
select query, segment, step, max(rows), min(rows), case when sum(rows) > 0 then ((cast(max(rows) -min(rows) as float)*count(rows))/sum(rows)) else 0 end from svl_query_report where query = 279 group by query, segment, step order by segment, step;

This query should return data similar to the following sample output:

Copy
query | segment | step | max | min | case ------+---------+------+----------+----------+---------------------- 279 | 0 | 0 | 19721687 | 19721687 | 0 279 | 0 | 1 | 19721687 | 19721687 | 0 279 | 1 | 0 | 986085 | 986084 | 1.01411202804304e-06 279 | 1 | 1 | 986085 | 986084 | 1.01411202804304e-06 279 | 1 | 4 | 986085 | 986084 | 1.01411202804304e-06 279 | 2 | 0 | 1775517 | 788460 | 1.00098637606408 279 | 2 | 2 | 1775517 | 788460 | 1.00098637606408 279 | 3 | 0 | 1775517 | 788460 | 1.00098637606408 279 | 3 | 2 | 1775517 | 788460 | 1.00098637606408 279 | 3 | 3 | 1775517 | 788460 | 1.00098637606408 279 | 4 | 0 | 1775517 | 788460 | 1.00098637606408 279 | 4 | 1 | 1775517 | 788460 | 1.00098637606408 279 | 4 | 2 | 1 | 1 | 0 279 | 5 | 0 | 1 | 1 | 0 279 | 5 | 1 | 1 | 1 | 0 279 | 6 | 0 | 20 | 20 | 0 279 | 6 | 1 | 1 | 1 | 0 279 | 7 | 0 | 1 | 1 | 0 279 | 7 | 1 | 0 | 0 | 0 (19 rows)