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;
