摘要：提供以作业基本单位的作业统计视图pgxc_session_wlmstat，便于用户观察运行作业和排队作业信息。
本文分享自华为云社区《GaussDB(DWS)如何查看作业运行信息》，作者：幕后小黑爪。
用户反馈，出现连接数告警，作业并发数高，超过资源池限制，与实际配置不符。经过了解，用户使用pgxc_stat_activity视图进行作业并发数判断。
该视图显示用户查询相关信息，是以连接线程为基础来进行统计的，因而使用此视图进行查询会存在几个问题：
为此，我们提供以作业基本单位的作业统计视图pgxc_session_wlmstat。便于用户观察运行作业和排队作业信息，以下给出推荐的查询语句。
注：该视图在GaussDB(DWS)8.1.3以上版本生效，且用户需要关闭guc参数enable_gtm_free，如果该参数开启，会存在统计不准确情况。
pgxc_session_wlmstat中字段说明：
select nodename,usename, enqueue,status,count(1) as query_count,sum(statement_mem) as estimate_mem from pgxc_session_wlmstat where status in ('pending','running') and attribute!='Internal' and usename!='Ruby' and usename <> 'omm' group by 1,2,3,4 limit 100;
查询结果如下：
如果需要其他信息也可从pgxc_session_wlmstat和pgxc_stat_activity中进行获取
select s.nodename, s.resource_pool,s.usename, a.query_start, s.enqueue,s.status, substr(s.query,1,30) as query, statement_mem as estimate_mem,a.query_id from pgxc_session_wlmstat s, pgxc_stat_activity a where s.threadid=a.pid and status in ('pending','running') and s.attribute!='Internal' and s.usename!='Ruby' and s.usename <> 'omm' order by 1,2,3,5,6 limit 100;
查询结果如下：
select resource_pool,nodename, enqueue,status,count(1) as query_count,sum(statement_mem) as estimate_mem from pgxc_session_wlmstat where status in ('pending','running') and attribute!='Internal' and usename!='Ruby' and usename <> 'omm' group by 1,2,3,4 limit 100;
查询结果如下：
select usename, enqueue,status,count(1) from pgxc_session_wlmstat where status in ('pending','running') and attribute!='Internal' and usename!='Ruby' and usename <> 'omm' group by 1,2,3 limit 100;
查询结果如下：
select s.resource_pool,s.usename, a.query_start, s.enqueue,s.status, substr(s.query,1,30) as query, statement_mem as estimate_mem,a.query_id from pgxc_session_wlmstat s, pgxc_stat_activity a where s.threadid=a.pid and status in ('pending','running') and s.attribute!='Internal' and s.usename!='Ruby' and s.usename <> 'omm' order by 1,2,4,5 limit 100;
查询结果如下：
select resource_pool, enqueue,status,count(1) as query_count,sum(statement_mem) as estimate_mem from pgxc_session_wlmstat where status in ('pending','running') and attribute!='Internal' and usename!='Ruby' and usename <> 'omm' group by 1,2,3 limit 100;
查询结果如下：
SELECT s.resource_pool AS rpname, COUNT(1) AS session_cnt,SUM(CASE WHEN a.state = 'active' THEN 1 ELSE 0 END) AS active_cnt,SUM(CASE WHEN s.enqueue ='Global' THEN 1 ELSE 0 END) AS global_wait,SUM(CASE WHEN s.lane = 'fast' and s.status = 'running' THEN 1 ELSE 0 END) AS fast_run,SUM(CASE WHEN s.lane = 'fast' and s.status = 'pending' and s.enqueue not in ('Global','None') THEN 1 ELSE 0 END) AS fast_wait,SUM(CASE WHEN s.lane = 'slow' and s.status = 'running' THEN 1 ELSE 0 END) AS slow_run,SUM(CASE WHEN s.lane = 'slow' and s.status = 'pending' and s.enqueue not in ('Global','None') THEN 1 ELSE 0 END) AS slow_wait,SUM(CASE WHEN s.status = 'running' THEN s.statement_mem ELSE 0 END) AS est_mem FROM pg_catalog.pgxc_session_wlmstat s,pg_catalog.pgxc_stat_activity a WHERE s.threadid=a.pid(+) AND s.attribute != 'Internal' AND s.resource_pool != 'root' GROUP BY 1;
查询结果如下：
7月7日，华为开发者大会2023 ( Cloud )将拉开帷幕，并将在国内30多个城市、海外10多个国家开设分会场，诚邀您参加这场不容错过的年度开发者盛会，让我们一起开启探索之旅！
我们将携手开发者、客户、合作伙伴，为您呈现华为云系列产品服务与丰富的创新实践，并与您探讨AI、大数据、数据库、PaaS、aPaaS、媒体服务、云原生、安全、物联网、区块链、开源等技术话题，展开全面深入的交流。
大会将汇聚全球科学家、行业领袖、技术专家、社区大咖，开设200多场开发者专题活动，为全球开发者提供面对面交流与合作的机会，共同探讨技术创新和业务发展。
大会官网：https://developer.huaweicloud.com/HDC.Cloud2023.html
参会购票：https://www.vmall.com/product/10086352254099.html?cid= 211761
点击参与开发者社区活动，观赏技术大咖秀、玩转技术梦工厂，有机会赢取4000元开发者礼包！
欢迎关注“华为云开发者联盟”公众号，获取大会议程、精彩活动和前沿干货。