SYS_QUERY_HISTORY
使用 SYS_QUERY_HISTORY 查看用户查询的详细信息。每行代表一个用户查询,其中包含某些字段的累积统计数据。此视图包含许多类型的查询,例如数据定义语言 (DDL)、数据操作语言 (DML)、复制、卸载和 Amazon Redshift Spectrum。它包含正在运行和已完成的查询。
SYS_QUERY_HISTORY 对所有用户可见。超级用户可以查看所有行;普通用户只能查看其自己的数据。有关更多信息,请参阅 系统表和视图中的数据可见性。
表列
列名称 | 数据类型 | 描述 |
---|---|---|
user_id | integer | 提交查询的用户标识符。 |
query_id | bigint | 查询标识符。 |
query_label | character(320) | 查询的短名称。 |
transaction_id | bigint | 事务标识符。 |
session_id | integer | 运行查询进程的进程标识符。 |
database_name | character(128) | 在发起查询时用户连接到的数据库的名称。 |
query_type | character(32) | 查询类型,例如,SELECT、INSERT、UPDATE、UNLOAD、COPY、COMMAND、DDL、UTILITY、CTAS 和 OTHER。 |
status | character(10) | 查询的状态。有效值:planning、queued、running、returning、failed、canceled 和 success。 |
result_cache_hit | 布尔值 | 指示查询是否匹配结果缓存。 |
start_time | timestamp | 查询开始的时间。 |
end_time | timestamp | 查询完成的时间。 |
elapsed_time | bigint | 在查询上花费的总时间(微秒)。 |
queue_time | bigint | 在服务类查询队列上花费的总时间(微秒)。 |
execution_time | bigint | 在服务类中运行的总时间(微秒)。 |
error_message | character(512) | 查询失败的原因。 |
returned_rows | bigint | 返回到客户端的行数。 |
returned_bytes | bigint | 返回到客户端的字节数。 |
query_text | character(4000) | 查询字符串。此字符串可能会被截断。 |
redshift_version | character(256) | 查询运行时的 Amazon Redshift 版本。 |
usage_limit | character(150) | 查询达到的使用限制 ID 的列表。 |
compute_type | varchar(32) | 指示查询运行在主集群还是并发扩展集群上。可能的值为 primary (查询在主集群上运行)、secondary (查询在辅助集群上运行)或 primary-scale (查询在并发集群上运行)。这仅适用于预调配的集群。 |
compile_time | bigint | 在编译上花费的总时间(微秒)。 |
planning_time | bigint | 在规划查询时花费的总时间(微秒)。 |
lock_wait_time | bigint | 等待关系锁定所花费的总时间(微秒)。 |
service_class_id | 整数 | 服务类的 ID。有关服务类 ID 的列表,请转至 WLM 服务类 ID。 此列仅用于在预置集群上运行的查询。对于在 Redshift Serverless 上运行的查询,此列包含 -1。 |
service_class_name | character(64) | 服务类名称。 此列仅用于在预置集群上运行的查询。对于在 Amazon Redshift Serverless 上运行的查询,此列为空。 |
query_priority | character(20) | 队列中查询运行的优先级。可能值如下所示:
NULL 表示查询不支持查询优先级。 此列仅用于在预置集群上运行的查询。对于在 Redshift Serverless 上运行的查询,此列为空。 |
short_query_accelerated | character(10) | 是否使用了短查询加速(SQA)来加速查询。可能值如下所示:
此列仅用于在预置集群上运行的查询。对于在 Redshift Serverless 上运行的查询,此列为空。 |
示例查询
以下查询返回了正在运行的查询和已排队的查询。
SELECT user_id, query_id, transaction_id, session_id, status, trim(database_name) AS database_name, start_time, end_time, result_cache_hit, elapsed_time, queue_time, execution_time FROM sys_query_history WHERE status IN ('running','queued') ORDER BY start_time;
示例输出。
user_id | query_id | transaction_id | session_id | status | database_name | start_time | end_time | result_cache_hit | elapsed_time | queue_time | execution_time ---------+----------+----------------+------------+---------+---------------+---------------------------+----------------------------+------------------+--------------+------------+---------------- 101 | 760705 | 852337 | 1073832321 | running | tpcds_1t | 2022-02-15 19:03:19.67849 | 2022-02-15 19:03:19.739811 | f | 61321 | 0 | 0
以下查询返回了特定查询的查询开始时间、结束时间、排队时间、已用时间、计划时间和其他元数据。
SELECT user_id, query_id, transaction_id, session_id, status, trim(database_name) AS database_name, start_time, end_time, result_cache_hit, elapsed_time, queue_time, execution_time, planning_time, trim(query_text) as query_text FROM sys_query_history WHERE query_id = 3093;
示例输出。
user_id | query_id | transaction_id | session_id | status | database_name | start_time | end_time | result_cache_hit | elapsed_time | queue_time | execution_time | planning_time | query_text --------+----------+----------------+------------+------------+---------------+----------------------------+----------------------------+------------------+--------------+------------+----------------+---------------+------------------------------------- 106 | 3093 | 11759 | 1073750146 | success | dev | 2023-03-16 16:53:17.840214 | 2023-03-16 16:53:18.106588 | f | 266374 | 0 | 105725 | 136589 | select count(*) from item;
以下示例列出了 10 个最近的 SELECT 查询。
SELECT query_id, transaction_id, session_id, start_time, elapsed_time, queue_time, execution_time, returned_rows, returned_bytes FROM sys_query_history WHERE query_type = 'SELECT' ORDER BY start_time DESC limit 10;
示例输出。
query_id | transaction_id | session_id | start_time | elapsed_time | queue_time | execution_time | returned_rows | returned_bytes ----------+----------------+------------+----------------------------+--------------+------------+----------------+---------------+---------------- 526532 | 61093 | 1073840313 | 2022-02-09 04:43:24.149603 | 520571 | 0 | 481293 | 1 | 3794 526520 | 60850 | 1073840313 | 2022-02-09 04:38:27.24875 | 635957 | 0 | 596601 | 1 | 3679 526508 | 60803 | 1073840313 | 2022-02-09 04:37:51.118835 | 563882 | 0 | 503135 | 5 | 17216 526505 | 60763 | 1073840313 | 2022-02-09 04:36:48.636224 | 649337 | 0 | 589823 | 1 | 652 526478 | 60730 | 1073840313 | 2022-02-09 04:36:11.741471 | 14611321 | 0 | 14544058 | 0 | 0 526467 | 60636 | 1073840313 | 2022-02-09 04:34:11.91463 | 16711367 | 0 | 16633767 | 1 | 575 511617 | 617946 | 1074009948 | 2022-01-20 06:21:54.44481 | 9937090 | 0 | 9899271 | 100 | 12500 511603 | 617941 | 1074259415 | 2022-01-20 06:21:45.71744 | 8065081 | 0 | 7582500 | 100 | 8889 511595 | 617935 | 1074128320 | 2022-01-20 06:21:44.030876 | 1051270 | 0 | 1014879 | 1 | 72 511584 | 617931 | 1074030019 | 2022-01-20 06:21:42.764088 | 609033 | 0 | 485887 | 100 | 8438
以下查询显示了每日 Select 查询的计数和平均查询用时。
SELECT date_trunc('day',start_time) AS exec_day, status, COUNT(*) AS query_cnt, AVG(datediff (microsecond,start_time,end_time)) AS elapsed_avg FROM sys_query_history WHERE query_type = 'SELECT' AND start_time >= '2022-01-14' AND start_time <= '2022-01-18' GROUP BY exec_day, status ORDER BY exec_day, status;
示例输出。
exec_day | status | query_cnt | elapsed_avg ---------------------+---------+-----------+------------ 2022-01-14 00:00:00 | success | 5253 | 56608048 2022-01-15 00:00:00 | success | 7004 | 56995017 2022-01-16 00:00:00 | success | 5253 | 57016363 2022-01-17 00:00:00 | success | 5309 | 55236784 2022-01-18 00:00:00 | success | 8092 | 54355124
以下查询显示了每日查询所用时间性能。
SELECT distinct date_trunc('day',start_time) AS exec_day, query_count.cnt AS query_count, Percentile_cont(0.5) within group(ORDER BY elapsed_time) OVER (PARTITION BY exec_day) AS P50_runtime, Percentile_cont(0.8) within group(ORDER BY elapsed_time) OVER (PARTITION BY exec_day) AS P80_runtime, Percentile_cont(0.9) within group(ORDER BY elapsed_time) OVER (PARTITION BY exec_day) AS P90_runtime, Percentile_cont(0.99) within group(ORDER BY elapsed_time) OVER (PARTITION BY exec_day) AS P99_runtime, Percentile_cont(1.0) within group(ORDER BY elapsed_time) OVER (PARTITION BY exec_day) AS max_runtime FROM sys_query_history LEFT JOIN (SELECT date_trunc('day',start_time) AS day, count(*) cnt FROM sys_query_history WHERE query_type = 'SELECT' GROUP by 1) query_count ON date_trunc('day',start_time) = query_count.day WHERE query_type = 'SELECT' ORDER BY exec_day;
示例输出。
exec_day | query_count | p50_runtime | p80_runtime | p90_runtime | p99_runtime | max_runtime ---------------------+-------------+-------------+-------------+-------------+--------------+-------------- 2022-01-14 00:00:00 | 5253 | 16816922.0 | 69525096.0 | 158524917.8 | 486322477.52 | 1582078873.0 2022-01-15 00:00:00 | 7004 | 15896130.5 | 71058707.0 | 164314568.9 | 500331542.07 | 1696344792.0 2022-01-16 00:00:00 | 5253 | 15750451.0 | 72037082.2 | 159513733.4 | 480372059.24 | 1594793766.0 2022-01-17 00:00:00 | 5309 | 15394513.0 | 68881393.2 | 160254700.0 | 493372245.84 | 1521758640.0 2022-01-18 00:00:00 | 8092 | 15575286.5 | 68485955.4 | 154559572.5 | 463552685.39 | 1542783444.0 2022-01-19 00:00:00 | 5860 | 16648747.0 | 72470482.6 | 166485138.2 | 492038228.67 | 1693483241.0 2022-01-20 00:00:00 | 1751 | 15422072.0 | 69686381.0 | 162315385.0 | 497066615.00 | 1439319739.0 2022-02-09 00:00:00 | 13 | 6382812.0 | 17616161.6 | 21197988.4 | 23021343.84 | 23168439.0
以下查询显示了查询类型分布。
SELECT query_type, COUNT(*) AS query_count FROM sys_query_history GROUP BY query_type ORDER BY query_count DESC;
示例输出。
query_type | query_count ------------+------------- UTILITY | 134486 SELECT | 38537 DDL | 4832 OTHER | 768 LOAD | 768 CTAS | 748 COMMAND | 92