GaussDB(DWS)查看后台活跃SQL和执行状态

背景：使用数据库过程中，执行一条查询语句很慢，想知道后台语句的执行情况。可通过以下方式查看数据库后台当前执行的所有语句和语句的执行情况。

1. PGXC_STAT_ACTIVITY视图介绍

PGXC_STAT_ACTIVITY视图显示当前集群下所有CN的查询相关的信息，只有系统管理员才有权限执行。该视图的coorname表示执行该语句的CN，query_id字段表示该query的唯一ID，同一条语句在不同节点的query_id相同，不同语句的query_id不同。pid表示该语句在对应节点上的线程ID，usename表示执行该语句的用户，query_start表示该语句开始执行的时间，enqueue表示语句是否正在排队。该字段为空表示未处于排队状态，state字段表示对应的语句执行状态，常见状态如下：

• active：后端正在执行一个查询。
• idle：后端正在等待一个新的客户端命令。
• idle in transaction：后端在事务中，但事务中没有语句在执行。
• idle in transaction (aborted)：后端在事务中，但事务中有语句执行失败。

利用此视图对相关字段进行过滤，即可查询得到当前的后台所有CN上的活跃语句：

select coorname, usename, client_addr, sysdate-query_start as dur, enqueue, query_id, substr(query,1,60)
from pgxc_stat_activity
where usename != 'Ruby' and state != 'idle' order by dur desc;

其中，Ruby用户为数据库的初始用户，一般情况下我们不关心初始用户相关的语句。执行上述查询即可得到当前后台所有活跃的sql情况和已经执行的时长。接下来，可以根据查到的query_id利用等待视图PGXC_THREAD_WAIT_STATUS对执行的慢sql进行分析，查看语句的执行状态。

2. PGXC_THREAD_WAIT_STATUS视图介绍

通过CN节点查看PGXC_THREAD_WAIT_STATUS视图，可以查看集群全局各个节点上所有SQL语句产生的线程之间的调用层次关系，以及各个线程的阻塞等待状态，从而更容易定位进程停止响应问题以及类似现象的原因。该视图中我们需重点关注wait_status字段和wait_event字段，其中，wait_status字段表示当前线程的等待状态，wait_event表示等待事件，一般为acquire lock、acquire lwlock、wait io三种类型。根据上一步查询得到的query_id查询等待视图，即可得到该语句的等待时间状态，分析出慢sql的瓶颈点：

select * from pgxc_thread_wait_status where query_id = 20971544;

例如：

select * from pgxc_thread_wait_status where query_id=20971544;
  node_name   | db_name  | thread_name  | query_id |       tid       | lwtid | ptid  | tlevel | smpid |     wait_status   |  wait_event   
--------------+----------+--------------+----------+-----------------+-------+-------+--------+-------+----------------------
 datanode1    | postgres | coordinator1 | 20971544 | 139902867994384 | 22735 |       |      0 |     0 | wait node: datanode3 |
 datanode1    | postgres | coordinator1 | 20971544 | 139902838634256 | 22970 | 22735 |      5 |     0 | synchronize quit     |
 datanode1    | postgres | coordinator1 | 20971544 | 139902607947536 | 22972 | 22735 |      5 |     1 | synchronize quit     |
 datanode2    | postgres | coordinator1 | 20971544 | 140632156796688 | 22736 |       |      0 |     0 | wait node: datanode3 |
 datanode2    | postgres | coordinator1 | 20971544 | 140632030967568 | 22974 | 22736 |      5 |     0 | synchronize quit     |
 datanode2    | postgres | coordinator1 | 20971544 | 140632081299216 | 22975 | 22736 |      5 |     1 | synchronize quit     |
 datanode3    | postgres | coordinator1 | 20971544 | 140323627988752 | 22737 |       |      0 |     0 | wait node: datanode3 |
 datanode3    | postgres | coordinator1 | 20971544 | 140323523131152 | 22976 | 22737 |      5 |     0 | net flush data       |
 datanode3    | postgres | coordinator1 | 20971544 | 140323548296976 | 22978 | 22737 |      5 |     1 | net flush data
 datanode4    | postgres | coordinator1 | 20971544 | 140103024375568 | 22738 |       |      0 |     0 | wait node: datanode3
 datanode4    | postgres | coordinator1 | 20971544 | 140102919517968 | 22979 | 22738 |      5 |     0 | synchronize quit     |
 datanode4    | postgres | coordinator1 | 20971544 | 140102969849616 | 22980 | 22738 |      5 |     1 | synchronize quit     |
 coordinator1 | postgres | gsql         | 20971544 | 140274089064208 | 22579 |       |      0 |     0 | wait node: datanode4  |
(13 rows)

可以看到，该语句在CN1执行，coordinator1在等datanode4，datanode4在等datanode3，datanode3在的等待状态为net flush data，表示该节点正在向网络中发送数据，说明整个查询的瓶颈点在datanode3的网络传输，该节点可能存在网络瓶颈。

等待视图中各等待状态详情可以通过以下文档查看：

https://support.huaweicloud.com/devg-dws/dws_04_0565.html

3. 二者结合使用

对于一些有明显特征的SQL，比如表名/别名/注释等，能根据该特征标志出唯一sql，可以执行以下SQL将PGXC_STAT_ACTIVITY和PGXC_THREAD_WAIT_STATUS进行关联查询：

select w.* from pgxc_thread_wait_status w
left join pgxc_stat_activity a 
on w.query_id=a.query_id 
where a.query_id != 0
and a.query like '%explain performance%'
and a.query not like '%pgxc_stat_activity%';

本例中，explain performance能够标识唯一SQL，即可使用该sql直接查询得到等待视图情况。