【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】5.7.25-OceanBase_CE-v4.0.0.0
【问题描述】如何理解ob_sql_audit视图中的并行调度字段QC_ID、DFO_ID、SQC_ID以及WORKER_ID的含义?
在多节点并行查询的环境中,一条SQL对应多条oceanbase.gv$ob_sql_audit中的记录,但是这些记录和执行计划是什么样的对应关系呢?主要涉及到这几个字段的理解,文档中解释如下:
QC_ID: 并行执行场景下,调度器 ID
DFO_ID:并行执行场景下,当前执行的子计划 ID
SQC_ID:并行执行场景下,本地协调器 ID
WORKER_ID:并行执行场景下,工作线程 ID
是不是可以这么理解:
QC_ID:查询协调线程的ID,在实际中主要区分语句的“发起”线程和其它线程,该ID无实际意义主要区分是否该查询的协调者,实际上可以用IS_EXECUTOR_RPC来区分。
DFO_ID:该ID对应执行计划中的子计划的EXCHANGE OUT的NAME部分,比如DFO_ID为2,则对应:EX10002,也就是DFO_ID和执行计划中的exchange out的name列的最后数字部分一致?
SQC_ID:是否可以理解为子计划所在的observer节点中的执行ID且一个语句的一个子计划在一个observer中唯一标识?该ID和observer节点属性有没有必然联系呢,比如SQC_ID=2,那么如何查找对应的observer信息呢?
WORKER_ID:是否可以理解为该语句的一个子计划中在一个observer节点中的线程ID呢?比如QC_ID=1,DFO_ID=1,SQC_ID=0,WORKER_ID={1,2},那么可以理解为DFO_ID=1的子计划在SQC_ID=0(obsever节点上)的并行度是2(也就是开启两个实际线程来干活),可以这么理解吗?
参考示例信息如下:
select /*+ TPCH_Q2 parallel(4) */
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
from
part,
supplier,
partsupp,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and p_size = 15
and p_type like '%BRASS'
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
and ps_supplycost = (
select
min(ps_supplycost)
from
partsupp,
supplier,
nation,
region
where
p_partkey = ps_partkey
and s_suppkey = ps_suppkey
and s_nationkey = n_nationkey
and n_regionkey = r_regionkey
and r_name = 'EUROPE'
)
order by
s_acctbal desc,
n_name,
s_name,
p_partkey;
--explain树形结构如下:
| ==========================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------------------------------------
|0 |PX COORDINATOR MERGE SORT | |1 |131406|
|1 | EXCHANGE OUT DISTR |:EX10003|1 |131404|
|2 | SORT | |1 |131403|
|3 | SUBPLAN SCAN |VIEW1 |1 |131403|
|4 | WINDOW FUNCTION | |10905 |131372|
|5 | PARTITION SORT | |10905 |127996|
|6 | HASH JOIN | |10905 |126198|
|7 | ├JOIN FILTER CREATE |:BF0000 |13192 |32384 |
|8 | │ PX PARTITION ITERATOR | |13192 |32384 |
|9 | │ TABLE SCAN |part |13192 |32384 |
|10| └SHARED HASH JOIN | |10905 |92797 |
|11| ├EXCHANGE IN DISTR | |20000 |40365 |
|12| │ EXCHANGE OUT DISTR (BC2HOST) |:EX10002|20000 |29621 |
|13| │ SHARED HASH JOIN | |20000 |5448 |
|14| │ ├EXCHANGE IN DISTR | |5 |4 |
|15| │ │ EXCHANGE OUT DISTR (BC2HOST) |:EX10001|5 |4 |
|16| │ │ SHARED HASH JOIN | |5 |3 |
|17| │ │ ├EXCHANGE IN DISTR | |1 |1 |
|18| │ │ │ EXCHANGE OUT DISTR (BC2HOST)|:EX10000|1 |1 |
|19| │ │ │ PX BLOCK ITERATOR | |1 |1 |
|20| │ │ │ TABLE SCAN |region |1 |1 |
|21| │ │ └PX BLOCK ITERATOR | |25 |1 |
|22| │ │ TABLE SCAN |nation |25 |1 |
|23| │ └PX BLOCK ITERATOR | |100000 |3351 |
|24| │ TABLE SCAN |supplier|100000 |3351 |
|25| └JOIN FILTER USE |:BF0000 |54521 |48354 |
|26| PX PARTITION ITERATOR | |54521 |48354 |
|27| TABLE SCAN |partsupp|54521 |48354 |
==========================================================================
--实际查询语句忽略,如下是该语句的执行信息:
obclient [tpch1]> select TRACE_ID,IS_EXECUTOR_RPC,QC_ID,DFO_ID,SQC_ID,WORKER_ID from oceanbase.gv$ob_sql_audit where trace_id in (select last_trace_id() from dual);
+-----------------------------------+-----------------+-------+--------+--------+-----------+
| TRACE_ID | IS_EXECUTOR_RPC | QC_ID | DFO_ID | SQC_ID | WORKER_ID |
+-----------------------------------+-----------------+-------+--------+--------+-----------+
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 0 | 0 | 0 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 0 | 0 | 1 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 0 | 0 | 2 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 1 | 0 | 2 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 1 | 0 | 1 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 1 | 0 | 0 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 2 | 0 | 1 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 2 | 0 | 2 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 2 | 0 | 0 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 3 | 0 | 1 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 3 | 0 | 0 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 1 | 1 | 3 | 0 | 2 |
| YB42C0A81FC9-0005F34BDBE5FE0A-0-0 | 0 | 0 | 0 | 0 | 0 |
+-----------------------------------+-----------------+-------+--------+--------+-----------+
13 rows in set (0.001 sec)