OceanBase中OCP平台显示的TOPSQL模块是如何查询出来的

说明：oba脚本通过变量的方式直接实现以下三个查询，如手工查询需要分别执行SQL获取对应的变量信息

1.server_id检查方式
select a.id server_id,a.svr_ip,a.zone,b.primary_zone,b.tenant_name from __all_server a,__all_tenant b where tenant_id=1001 order by 3;

2.计算总cpu
--这里计算范围时间段内总cpu，将这里计算的带入下面的SQL，也可以整个SQL带入，oba通过脚本变量实现
--ob_server_id代表要查询的节点信息，根据实际情况填写具体的****ob_server_id in(3);***

select
sum(a.cpu_time)
from ob_hist_sql_audit_stat_0 a
where a.ob_cluster_id = 1632654686
and a.begin_interval_time > time_to_usec('2024-06-18 08:32:10')
and a.begin_interval_time < time_to_usec('2024-06-18 09:02:10')
and a.ob_tenant_id = 1001 and a.cluster_name='obcrm1' and ob_server_id in(3);

3.将步骤2cpu带入sum(cpu_time)后面，将数值替换
--order by可以根据不同的指标进行排序，以下提供的SQL按照范围时间段内的SQL总耗时进行排序，可根据实际情况进行排序
select /*+parallel(32)*/
a.sql_id,
sum(a.executions) execs,
round(sum(a.elapsed_time / 1000),2) "total(ms)",
round(max(a.max_elapsed_time / 1000),2) "max_elapsed(ms)",
round(sum(a.elapsed_time) / sum(a.executions) / 1000,2) "avg_elapsed",
round(sum(a.queue_time)/sum(a.executions)/1000,2) "queue(ms)",
round(sum(a.get_plan_time)/sum(a.executions)/1000,2) "get_plan(ms)",
sum(a.rpc_count)/sum(a.executions) rpc_count,
sum(a.return_rows)/sum(a.executions) return_rows,
sum(cpu_time)/12695566676*100 "cpu%",
sum(a.memstore_read_row_count)/sum(a.executions) mem_row_count,
sum(a.ssstore_read_row_count)/sum(a.executions) sst_row_count,
sum(a.retry_cnt) retry_cnt,
sum(a.fail_count) fail_count
from ob_hist_sql_audit_stat_0 a
where a.ob_cluster_id = 1632654686
and a.begin_interval_time > time_to_usec('2024-06-18 08:32:10')
and a.begin_interval_time < time_to_usec('2024-06-18 09:02:10')
and a.ob_tenant_id = 1001 and a.cluster_name='obcrm1' and ob_server_id in(3)
group by a.sql_id
order by 3 desc limit 20;