原 GreenPlum中如何通过SQL查询历史SQL语句的查询ID、排队时间、运行时间、CPU、磁盘等情况
Tags: 原创GreenPlumgpmetrics.gpcc_queries_history
SQL语句
切换到gpperfmon数据库,做如下查询:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | -- 排队时间 运行时间 查询id SELECT case when EXTRACT(SECOND FROM tstart-tsubmit) > 1 then (tfinish - tsubmit) else '0s' end as Queued_Time , case when EXTRACT(SECOND FROM tstart-tsubmit) < 1 then (tfinish - tstart) else '0s' end as run_time, tmid||'-'||ssid||'-'||ccnt query_id, gqh.tsubmit,gqh.tfinish,gqh.tstart,gqh.rsqname,gqh.rqpriority, case when gqh.plan_gen in ('PLANNER','none' ) then 'Legacy' when gqh.plan_gen='OPTIMIZER' then 'GPORCA' end as OPTIMIZER , db,username, gqh.query_text, gqh.cpu_master_percent, gqh.cpu_segs_percent, TO_CHAR((INTERVAL '1 second' * gqh.cpu_master), 'HH24:MI:SS') AS cpu_time, gqh.skew_cpu, round(gqh.peak_memory/1024) peak_memory_MB, round(gqh.spill_size/1024/1024) spill_file_size_MB, round(gqh.disk_read_bytes/1024/1024) disk_read_MB, round(gqh.disk_write_bytes/1024/1024) disk_write_MB, -- gqh.lock_seconds, gqh.* from gpmetrics.gpcc_queries_history gqh WHERE 1=1 and ctime >= '2023-12-20 09:00' AND ctime < '2023-12-21 17:20' and gqh.status='done' -- and tmid='1696846857' and ssid='7400742' and ccnt='3' and tmid||'-'||ssid||'-'||ccnt='1696846857-7325211-156' ORDER BY run_time limit 1000 |
一些指标
Query ID