【openGauss】openGauss如何排查长SQL
【openGauss】openGauss如何排查长SQL
一、openGauss查历史的长事务SQL(需要用root账号)
select db_name
,user_name
,t.db_time/1000000 as db_time_second
,start_time
,finish_time
,pg_catalog.statement_detail_decode(details,'plaintext',true) as details
from dbe_perf.statement_history t;
二、openGauss查询活跃的大于5秒的会话(普通账号即可查询)
SELECT pid,
(now() - t.query_start) AS duration,
query,
state,
t.*
FROM pg_stat_activity t
WHERE (now() - t.query_start) > interval '5 seconds'
AND t.state = 'active'
and t.datname not in ('postgres')
order by duration desc;
三、openGauss查实时会话和等待事件(普通账号即可查询)
SELECT (now() - a.xact_start) ::text AS xact_runtime
,a.pid
,a.sessionid
,w.block_sessionid
,a.datname
,a.usename
,a.application_name
,a.client_addr
,a.xact_start
,a.query_start
,a.waiting
,w.wait_event
,a.state
,a.query
FROM pg_stat_activity a
JOIN pg_thread_wait_status w
ON (a.sessionid = w.sessionid)
WHERE a.pid <> pg_backend_pid()
AND a.state <> 'idle'
and a.datname not in ('postgres')
ORDER BY a.xact_start desc nulls last;