SYS_QUERY_DETAIL - Amazon Redshift

SYS_QUERY_DETAIL

Use SYS_QUERY_DETAIL to view details for queries at a step level. Each row represents a step from a particular WLM query with details. This view contains many types of queries such as DDL, DML, and utility commands (for example, copy and unload). Some columns might not be relevant depending on the query type. For example, external_scanned_bytes is not relevant to internal tables.

SYS_QUERY_DETAIL 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
user_id integer The identifier of the user who submitted the query.
query_id bigint The query identifier.
child_query_sequence integer The sequence of the rewritten user query, starting with 1.
stream_id integer The stream identifier of the query stream.
segment_id integer The segment identifier of the query running segment.
step_id integer The step identifier in a segment.
step_name text The step name in a segment. Possible values are aggregate, broadcast, delete, distribute, hash, hashjoin, insert, limit, merge, nestloop, parse, return, save, scan, sort, sortlimit, unique, and window.
table_id integer The table identifier for permanent table scans.
table_name character(136) The table name of the step that is being operated.
is_rrscan character A value that indicates whether a step is a scan step. True (t) indicates that a range-restricted scan was used.
start_time timestamp The time when the query step began.
end_time timestamp The time when the query step completed.
duration bigint The amount of time (microseconds) spent on the step.
alert text The description of the alert event.
input_bytes bigint The input bytes for the current step.
input_rows bigint The input rows for the current step.
output_bytes bigint The output bytes for the current step.
output_rows bigint The output rows for the current step.
blocks_read bigint The number of block the step read.
blocks_write bigint The number of block the step wrote.
local_read_IO bigint The number of blocks read from local disk cache.
remote_read_IO bigint The number of blocks read from remote.
source text The type of database object that was scanned. This column only has a value when the row's step_name value is scan.
data_skewness integer The skewness of output rows distribution among all steps. It is a number in the range of 0% to 100%. The larger the number, the more unbalanced is the distribution.
time_skewness integer The skewness of execution time distribution among all steps. It is a number in the range of 0% to 100%. The larger the number, the more unbalanced is the distribution.
is_active character The state of the query at the step level. Possible values are ‘t’ that shows the step is actively running or ‘f’ that indicates the step completes running.
spilled_block_local_disk bigint The number of blocks spilled to local disk.
spilled_block_remote_disk bigint The number of blocks spilled to Amazon Simple Storage Service.
step_attribute character(64) Contains information about the associated step. Possible values for scan steps: multi-dimensional.

Sample queries

The following example returns the output of SYS_QUERY_DETAIL.

The following query shows the query metadata detail at step level, including step name, input_bytes, output_bytes, input_rows, output_rows.

SELECT query_id, child_query_sequence, stream_id, segment_id, step_id, trim(step_name) AS step_name, duration, input_bytes, output_bytes, input_rows, output_rows FROM sys_query_detail WHERE query_id IN (193929) ORDER BY query_id, stream_id, segment_id, step_id DESC;

Sample output.

query_id | child_query_sequence | stream_id | segment_id | step_id | step_name | duration | input_bytes | output_bytes | input_rows | output_rows ----------+----------------------+-----------+------------+---------+------------+-----------------+-------------+--------------+------------+------------- 193929 | 2 | 0 | 0 | 3 | hash | 37144 | 0 | 9350272 | 0 | 292196 193929 | 5 | 0 | 0 | 3 | hash | 9492 | 0 | 23360 | 0 | 1460 193929 | 1 | 0 | 0 | 3 | hash | 46809 | 0 | 9350272 | 0 | 292196 193929 | 4 | 0 | 0 | 2 | return | 7685 | 0 | 896 | 0 | 112 193929 | 1 | 0 | 0 | 2 | project | 46809 | 0 | 0 | 0 | 292196 193929 | 2 | 0 | 0 | 2 | project | 37144 | 0 | 0 | 0 | 292196 193929 | 5 | 0 | 0 | 2 | project | 9492 | 0 | 0 | 0 | 1460 193929 | 3 | 0 | 0 | 2 | return | 11033 | 0 | 14336 | 0 | 112 193929 | 2 | 0 | 0 | 1 | project | 37144 | 0 | 0 | 0 | 292196 193929 | 1 | 0 | 0 | 1 | project | 46809 | 0 | 0 | 0 | 292196 193929 | 5 | 0 | 0 | 1 | project | 9492 | 0 | 0 | 0 | 1460 193929 | 3 | 0 | 0 | 1 | aggregate | 11033 | 0 | 201488 | 0 | 14 193929 | 4 | 0 | 0 | 1 | aggregate | 7685 | 0 | 28784 | 0 | 14 193929 | 5 | 0 | 0 | 0 | scan | 9492 | 0 | 23360 | 292196 | 1460 193929 | 4 | 0 | 0 | 0 | scan | 7685 | 0 | 1344 | 112 | 112 193929 | 2 | 0 | 0 | 0 | scan | 37144 | 0 | 7304900 | 292196 | 292196 193929 | 3 | 0 | 0 | 0 | scan | 11033 | 0 | 13440 | 112 | 112 193929 | 1 | 0 | 0 | 0 | scan | 46809 | 0 | 7304900 | 292196 | 292196 193929 | 5 | 0 | 0 | -1 | | 9492 | 12288 | 0 | 0 | 0 193929 | 1 | 0 | 0 | -1 | | 46809 | 16384 | 0 | 0 | 0 193929 | 2 | 0 | 0 | -1 | | 37144 | 16384 | 0 | 0 | 0 193929 | 4 | 0 | 0 | -1 | | 7685 | 28672 | 0 | 0 | 0 193929 | 3 | 0 | 0 | -1 | | 11033 | 114688 | 0 | 0 | 0

To view the tables in your database in order from most used to least used, use the following example. Replace sample_data_dev with your own database. Note that this query will count queries starting when your cluster is created, but your system view data is not saved when your data warehouse is lacking space.

SELECT table_name, COUNT (DISTINCT query_id) FROM SYS_QUERY_DETAIL WHERE table_name LIKE 'sample_data_dev%' GROUP BY table_name ORDER BY COUNT(*) DESC; +---------------------------------+-------+ | table_name | count | +---------------------------------+-------+ | sample_data_dev.tickit.venue | 4 | | sample_data_dev.myunload1.venue | 3 | | sample_data_dev.tickit.listing | 1 | | sample_data_dev.tickit.category | 1 | | sample_data_dev.tickit.users | 1 | | sample_data_dev.tickit.date | 1 | | sample_data_dev.tickit.sales | 1 | | sample_data_dev.tickit.event | 1 | +---------------------------------+-------+