SYS_QUERY_DETAIL
Use SYS_QUERY_DETAIL to view details for queries at various metric levels, with each row representing details about a particular WLM query at a given metric level. 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.
This field is recorded at the segment level,
regardless of the value in the metrics_level column. |
end_time | timestamp | The time when the query step completed.
This field is recorded at the segment level,
regardless of the value in the metrics_level column. |
duration | bigint | The amount of time (microseconds) spent on the
step. This field is recorded at the segment level,
regardless of the value in the metrics_level column. |
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 . |
metrics_level | character(64) | The metric level of the query. Possible values are as follows:
|
plan_parent_id | integer | The identifier of the plan node's parent node. A parent node can have multiple child nodes. For example, a merge join is the parent node of the scans on the joined tables. |
plan_node_id | integer | The identifier of a plan node that maps to one or more steps in the query. |
Usage notes
SYS_QUERY_DETAIL can contain metrics at the step, steam, segment, and child query level. In addition to referencing the metrics_level column, you can see which metric level a given row is showing by referencing the step_id, segment_id, and stream_id fields according to the following table.
Metric level | stream_id value | segment_id value | step_id value |
---|---|---|---|
child query | -1 | -1 | -1 |
stream | A valid step value | -1 | -1 |
segment | A valid step value | A valid step value | -1 |
step | A valid step value | A valid step value | A valid step value |
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 | +---------------------------------+-------+
The following example shows the various metric levels for a single WLM query.
SELECT query_id, child_query_sequence, stream_id, segment_id, step_id, step_name, start_time, end_time, metrics_level FROM sys_query_detail WHERE query_id = 1553 AND step_id = -1 ORDER BY stream_id, segment_id, step_id; query_id | child_query_sequence | stream_id | segment_id | step_id | step_name | start_time | end_time | metrics_level ----------+----------------------+-----------+------------+---------+-----------+----------------------------+----------------------------+--------------- 1553 | 1 | -1 | -1 | -1 | | 2024-10-17 02:28:49.814721 | 2024-10-17 02:28:49.847838 | child query 1553 | 1 | 0 | -1 | -1 | | 2024-10-17 02:28:49.814721 | 2024-10-17 02:28:49.835609 | stream 1553 | 1 | 0 | 0 | -1 | | 2024-10-17 02:28:49.824677 | 2024-10-17 02:28:49.830372 | segment 1553 | 1 | 1 | -1 | -1 | | 2024-10-17 02:28:49.835624 | 2024-10-17 02:28:49.845773 | stream 1553 | 1 | 1 | 1 | -1 | | 2024-10-17 02:28:49.84088 | 2024-10-17 02:28:49.842388 | segment 1553 | 1 | 1 | 2 | -1 | | 2024-10-17 02:28:49.835926 | 2024-10-17 02:28:49.844396 | segment 1553 | 1 | 2 | -1 | -1 | | 2024-10-17 02:28:49.846949 | 2024-10-17 02:28:49.847838 | stream 1553 | 1 | 2 | 3 | -1 | | 2024-10-17 02:28:49.847013 | 2024-10-17 02:28:49.847485 | segment (8 rows)