__all_virtual_ddl_operation视图查询问题

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB or 其他组件 】OB
【 使用版本 】4.2
【问题描述】__all_virtual_ddl_operation视图查询很慢
【复现路径】
集群有15个租户，想统计过去1小时内，每个租户的DDL操作次数，执行一次需耗时50秒左右，添加并行参数parallel(16)执行48秒
SQL语句如下：
select /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) query_timeout(100000000000) /
a.tenant_name, nvl(b.cnt,0) as cnt
from (select tenant_id, tenant_name from oceanbase.__all_tenant) a
left join
(select tenant_id, count() cnt from oceanbase.__all_virtual_ddl_operation
where gmt_create >= date_sub(now(),interval 1 hour)
and gmt_create < now() and ddl_stmt_str <> ‘’) b on a.tenant_id=b.tenant_id

#3

SHOW VARIABLES like ‘version_comment’;版本信息查一下
SELECT /*+ MONITOR_AGENT READ_CONSISTENCY(WEAK) QUERY_TIMEOUT(100000000000) */
t.tenant_name,
IFNULL(COUNT(d.tenant_id), 0) AS cnt
FROM oceanbase.__all_tenant t
LEFT JOIN oceanbase.__all_virtual_ddl_operation d
ON t.tenant_id = d.tenant_id
AND d.gmt_create >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
AND d.gmt_create < NOW()
AND d.ddl_stmt_str IS NOT NULL
AND d.ddl_stmt_str <> ‘’
GROUP BY t.tenant_id, t.tenant_name; 试一下这样查询 是不是会快点

1 个赞