原 GreenPlum中如何通过SQL查询历史SQL语句的查询ID、排队时间、运行时间、CPU、磁盘等情况
Tags： 原创GreenPlum系统视图系统表gpmetricsgpmetrics.gpcc_queries_history
👉 本文共约2661个字，系统预计阅读时间或需11分钟。
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 28 29 | -- 某条SQL的排队时间 运行时间 查询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/1024) peak_memory_GB, round(gqh.spill_size/1024/1024/1024) spill_file_size_GB, round(gqh.disk_read_bytes/1024/1024/1024) disk_read_GB, round(gqh.disk_write_bytes/1024/1024/1024) disk_write_GB, -- gqh.lock_seconds, gqh.* from gpmetrics.gpcc_queries_history gqh WHERE 1=1 and ctime >= '2024-03-25 09:00' AND ctime < '2024-03-25 10:00' and gqh.status='done' -- and (tfinish - tsubmit) >= INTERVAL '1 second' -- Queued_Time -- and (tfinish - tstart) >= INTERVAL '5 second' -- run_time -- and tmid='1696846857' and ssid='7400742' and ccnt='3' and tmid||'-'||ssid||'-'||ccnt='1696846857-7325211-156' ORDER BY run_time limit 1000; |
一些指标
Query ID
An identification string for the query. If the column is blank, no query ID has been assigned yet. In the Console, this looks like “1295397846-56415-2”. Command Center generates this ID by combining the query record’s tmid, ssid, and ccnt fields.
tmidis a time identifier for the query.ssidis the session id.ccntis the number of the command within the session.
Status
The status of the query. This can be one of the following:
- Queued: the query has not yet started to execute
- Running: execution has started, but is not yet complete
- Blocked: the query is waiting for one or more other queries to release locks
- Done: completed successfully
- Cancelling: cancel request sent, cancel pending
- Cancelled: terminated, no longer running
- Idle Transaction: the transaction is open, but idle, for example, waiting while a user in an interactive session enters a statement
Submitted
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
