【openGauss】openGauss如何排查长SQL

---

【openGauss】openGauss如何排查长SQL

一、openGauss查历史的长事务SQL（需要用root账号）

/* openGauss查历史的长事务SQL（需要用root账号） */
select db_name
      ,user_name
      ,t.db_time/1000000 as db_time_second  -- 秒
      -- ,(t.finish_time - t.start_time) as s1
      ,start_time
      ,finish_time
      ,pg_catalog.statement_detail_decode(details,'plaintext',true) as details
  from dbe_perf.statement_history t;

二、openGauss查询活跃的大于5秒的会话（普通账号即可查询）

/* 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查实时会话和等待事件（普通账号即可查询）

-- 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;