AGGREGATE_QUERY_HISTORY 视图¶

列¶

OBJECT 数据类型包含以下字段：

SELECT * FROM table1 WHERE table1.name = 'TIM'

SELECT * FROM table1 WHERE table1.name = 'AIHUA'

使用说明¶

视图的延迟时间最长可达 180 分钟（3 小时）。

示例¶

您可以使用 AGGREGATE_QUERY_HISTORY 视图来监控可能存在的错误、排队、锁定阻塞或混合表限制等问题。您通常希望这些指标始终处于较低水平。如果您看到这些指标中的任何一个出现峰值，则可能表明存在问题：

SET (START_DATE, END_DATE) = ('2023-11-01', '2023-11-08');

WITH time_issues AS
(
    SELECT
        interval_start_time
        , SUM(transaction_blocked_time:"sum") AS transaction_blocked_time
        , SUM(queued_provisioning_time:"sum") AS queued_provisioning_time
        , SUM(queued_repair_time:"sum") AS queued_repair_time
        , SUM(queued_overload_time:"sum") AS queued_overload_time
        , SUM(hybrid_table_requests_throttled_count) AS hybrid_table_requests_throttled_count
    FROM snowflake.account_usage.aggregate_query_history
    WHERE TRUE
        AND interval_start_time > $START_DATE
        AND interval_start_time < $END_DATE
    GROUP BY ALL
),
errors AS
(
    SELECT
        interval_start_time
        , SUM(value:"count") as error_count
    FROM
    (
        SELECT
            a.interval_start_time
            , e.*
        FROM
            snowflake.account_usage.aggregate_query_history a,
            TABLE(FLATTEN(input => errors)) e
        WHERE TRUE
            AND interval_start_time > $START_DATE
            AND interval_start_time < $END_DATE
    )
    GROUP BY ALL
)
SELECT
    time_issues.interval_start_time
    , error_count
    , transaction_blocked_time
    , queued_provisioning_time
    , queued_repair_time
    , queued_overload_time
    , hybrid_table_requests_throttled_count
FROM
    time_issues FULL JOIN errors ON errors.interval_start_time = time_issues.interval_start_time
;

Copy

您可以查询视图以监控整体工作负载吞吐量和并发性。许多工作负载具有规律的周期性模式。任何意外的峰值或下降都可能值得调查。

例如，监控仓库 my_warehouse 在 11 月第 1 周的吞吐量和并发性：

SELECT
    interval_start_time
    , SUM(calls) AS execution_count
    , SUM(calls) / 60 AS queries_per_second
    , COUNT(DISTINCT session_id) AS unique_sessions
    , COUNT(user_name) AS unique_users
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND warehouse_name = 'MY_WAREHOUSE'
    AND interval_start_time > '2023-11-01'
    AND interval_start_time < '2023-11-08'
GROUP BY
    interval_start_time
;

对于最常用且重复次数最多的查询，最值得集中精力进行优化或提高工作负载效率。您可以查询视图，按执行计数确定一个工作负载的热门查询。

例如，按仓库 my_warehouse 的执行计数确定其热门查询：

SELECT
    query_parameterized_hash
    , ANY_VALUE(query_text)
    , SUM(calls) AS execution_count
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND warehouse_name = 'MY_WAREHOUSE'
    AND interval_start_time > '2023-11-01'
    AND interval_start_time < '2023-11-08'
GROUP BY
    query_parameterized_hash
ORDER BY execution_count DESC
;

要按平均总延迟确定速度最慢的查询，请执行以下操作：

SELECT
    query_parameterized_hash
    , any_value(query_text)
    , SUM(total_elapsed_time:"sum"::NUMBER) / SUM (calls) as avg_latency
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND warehouse_name = 'MY_WAREHOUSE'
    AND interval_start_time > '2023-07-01'
    AND interval_start_time < '2023-07-08'
GROUP BY
    query_parameterized_hash
ORDER BY avg_latency DESC
;

要分析感兴趣的特定查询随时间推移的性能，请执行以下操作：

SELECT
    interval_start_time
    , total_elapsed_time:"avg"::number avg_elapsed_time
    , total_elapsed_time:"min"::number min_elapsed_time
    , total_elapsed_time:"p90"::number p90_elapsed_time
    , total_elapsed_time:"p99"::number p99_elapsed_time
    , total_elapsed_time:"max"::number max_elapsed_time
FROM snowflake.account_usage.aggregate_query_history
WHERE TRUE
    AND query_parameterized_hash = '<123456>'
    AND interval_start_time > '2023-07-01'
    AND interval_start_time < '2023-07-08'
ORDER BY interval_start_time DESC
;