Vertica表使用情况分析SQL查询及参考文档求助

前置说明 #

以下查询均基于Vertica内置系统表实现，执行前请确保你有系统表的查询权限。默认统计最近7天的请求数据，你可以按需修改时间范围条件。

1. 被查询命中次数最高的表 #

该统计覆盖所有读、写请求中涉及到的表，统计维度为表被所有类型请求引用的总次数：

SELECT 
    table_name,
    COUNT(DISTINCT request_id) AS hit_count
FROM (
    SELECT 
        request_id,
        SPLIT_PART(SPLIT_PART(tables_accessed, ',', num), '.', 2) AS table_name
    FROM v_monitor.query_requests
    CROSS JOIN (SELECT ROW_NUMBER() OVER () AS num FROM tables LIMIT 100) nums
    WHERE request_start >= CURRENT_DATE - INTERVAL '7 days'
      AND SPLIT_PART(tables_accessed, ',', num) <> ''
) t
WHERE table_name IS NOT NULL
GROUP BY table_name
ORDER BY hit_count DESC
LIMIT 50;

注：如果需要区分schema，可以保留SPLIT_PART取出的schema前缀统计即可，单条查询涉及表数量超过100的话可调整子查询中的LIMIT阈值

2. 写入类查询请求量较高的表 #

统计涉及INSERT/UPDATE/DELETE/COPY/MERGE等写入操作的表的请求次数：

SELECT 
    table_name,
    COUNT(DISTINCT request_id) AS write_request_count
FROM (
    SELECT 
        request_id,
        SPLIT_PART(SPLIT_PART(tables_accessed, ',', num), '.', 2) AS table_name
    FROM v_monitor.query_requests
    CROSS JOIN (SELECT ROW_NUMBER() OVER () AS num FROM tables LIMIT 100) nums
    WHERE request_start >= CURRENT_DATE - INTERVAL '7 days'
      AND request_type IN ('INSERT', 'UPDATE', 'DELETE', 'COPY', 'MERGE')
      AND SPLIT_PART(tables_accessed, ',', num) <> ''
) t
WHERE table_name IS NOT NULL
GROUP BY table_name
ORDER BY write_request_count DESC
LIMIT 50;

3. 读取类查询请求量较高的表 #

统计仅涉及SELECT读操作的表的请求次数：

SELECT 
    table_name,
    COUNT(DISTINCT request_id) AS read_request_count
FROM (
    SELECT 
        request_id,
        SPLIT_PART(SPLIT_PART(tables_accessed, ',', num), '.', 2) AS table_name
    FROM v_monitor.query_requests
    CROSS JOIN (SELECT ROW_NUMBER() OVER () AS num FROM tables LIMIT 100) nums
    WHERE request_start >= CURRENT_DATE - INTERVAL '7 days'
      AND request_type = 'SELECT'
      AND SPLIT_PART(tables_accessed, ',', num) <> ''
) t
WHERE table_name IS NOT NULL
GROUP BY table_name
ORDER BY read_request_count DESC
LIMIT 50;

参考文档获取路径 #

你可以在Vertica官方文档对应版本的「监控与性能优化」分类下查找「系统表参考」、「查询历史统计」相关章节获取更多用法，本地部署的Vertica集群也可以直接访问自带的离线文档站点查看对应内容。

内容的提问来源于stack exchange，提问作者Aniketa Sharma