OB 什么情况下CPU高 但在processlist看不到

有分析OB性能问题时，有时某个OBSERVER主机CPU使用率，但通过__all_virtual_processlist查询活动会话并不高。这时可能有以下原因导致。

1、行锁等待过程10秒重试

等待过程中SESSION不是ACTIVE状态，且SQL_ID,INFO为空。

2、并行SESSION或RPC SQL

PROCESSLIST不显示并行SESSION

跑一条并行SQL，查__all_virtual_processlist只有一个活动SESSION

select TRACE_ID,count(*),max(time)  from __all_virtual_processlist where trace_id!='' and state='ACTIVE'
group by TRACE_ID;
+-----------------------------------+----------+-------------+
| TRACE_ID                          | count(*) | max(time)   |
+-----------------------------------+----------+-------------+
| YB420AE6A702-000618B49EFEB662-0-0 |        1 |  272.470363 |<<<

select svr_ip,TRACE_ID,count(*),max(time)   from __all_virtual_processlist where trace_id!='' and state='ACTIVE'
group by svr_ip,TRACE_ID;
+---------------+-----------------------------------+----------+-------------+
| svr_ip        | TRACE_ID                          | count(*) | max(time)   |
+---------------+-----------------------------------+----------+-------------+
| 192.168.56.2  | YB420AE6A702-000618B49EFEB662-0-0 |        1 |  272.535737 |<<<<

TRACE_ID YB420AE6A702-000618B49EFEB662-0-0,这条SQL执行，在PROCESSLIST 中只能看以1条活跃SESSION,但通过SQL MONITOR，可以看以在3台机器上每别分配了600多个线程

select TRACE_ID,SVR_IP,SID,count(*),count(distinct PROCESS_NAME) from gv$sql_plan_monitor where TRACE_ID in (select TRACE_ID  from __all_virtual_processlist where trace_id!='' and state='ACTIVE') group by trace_id,SVR_IP,SID;
+-----------------------------------+---------------+------+----------+------------------------------+
| TRACE_ID                          | SVR_IP        | SID  | count(*) | count(distinct PROCESS_NAME) |
+-----------------------------------+---------------+------+----------+------------------------------+
| YB420AE6A702-000618B49EFEB662-0-0 | 192.168.56.2  | NULL |     9297 |                          666 |<<<<
| YB420AE6A702-000618B49EFEB662-0-0 | 192.168.56.20 | NULL |     9159 |                          687 |<<<<
| YB420AE6A702-000618B49EFEB662-0-0 | 192.168.56.29 | NULL |     9129 |                          684 |<<<<

排查思路

1、通过行锁相关视图排查行锁冲突

2、排查慢SQL，通过gv$sql_plan_monitor,gv$tenant_px_worker_stat 排查并行SQL

3、通过内部视图或observer log检查OB队列