GaussDB(DWS)问题定位常用SQL

1、查询本地正在运行中的事务的状态情况

select * from pg_get_running_xacts();

2、查询集群中正在运行的所有事务的情况

select * from pgxc_get_running_xacts();

3、查询当前用户正在执行的查询任务

select * from PG_STAT_ACTIVITY;

4、查询锁的使用情况

select l.locktype,l.relation,l.pid,l.mode,l.granted,p.query_start,p.query,p.state from pg_locks l,pg_stat_activity p where l.locktype='relation' and l.pid=p.pid and query not like '%pg_stat_activity%';

5、查询当前实例上整体内存使用状态和信息

SELECT * FROM pv_total_memory_detail;

6、查询内存上下文级别的内存占用详细信息

SELECT * FROM pv_session_memory_detail ORDER BY totalsize desc LIMIT 10;

7、查询集群每个物理节点内存、每个节点dn个数

SELECT sessid, contextname, level,parent,totalsize,freesize,usedsize,  datname,query_id  FROM pv_session_memory_detail a , pg_stat_activity b  WHERE  split_part(a.sessid,'.',2) = b.pid and b.state='active' ORDER BY usedsize desc limit 20 ;

8、查询监控session total memory size占用最多的TOP20 session

SELECT sessid, sum_total, sum_free,sum_used, query_id, query_start, state, waiting, enqueue,query from (select sessid, sum(totalsize) as sum_total, sum(freesize) as sum_free, sum(usedsize) as sum_used from pv_session_memory_detail group by sessid  ORDER BY sum_total desc limit 20 ) a , pg_stat_activity b  WHERE  split_part(a.sessid,'.',2) = b.pid;

9、监控session中占用内存最多的context TOP20 session

SELECT sessid, contextname, level,parent, pg_size_pretty(totalsize),pg_size_pretty(freesize),pg_size_pretty(usedsize),  datname,query_id, query  from pv_session_memory_detail a , pg_stat_activity b  WHERE  split_part(a.sessid,'.',2) = b.pid  order by totalsize desc limit 20 ;

10、监控当前实例总totalsize memroy大小

SELECT pg_size_pretty(sum(totalsize)) FROM pv_session_memory_detail;

11、监控当前实例总usedsize memroy大小

SELECT pg_size_pretty(sum(usedsize)) FROM pv_session_memory_detail;

12、监控当前实例内存总体使用情况

SELECT * FROM pg_total_memory_detail;

13、监控共享内存实时使用情况

SELECT * FROM pg_shared_memory_detail;