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 | +---------------------------------+-------+