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 completed query steps.
This view breaks down the information about completed 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.
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.
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 ran. |
| 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 completed. |
| start_time | timestamp | Exact time in UTC 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 in UTC 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 run. |
| 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 performed 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. 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. |
| is_delayed_scan | character(1) | If true (t), indicates that
delayed scan was used on the step. |
| 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:
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:
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)