【 使用环境 】生产环境 or 测试环境
【 OB or 其他组件 】OB
【 使用版本 】5.7.25-OceanBase_CE-v4.2.0.0
【问题描述】
SQL1=select /*+ parallel(2) */ count(*) from customer a join nation b on a.c_nationkey < b.n_nationkey;
比
SQL2=select /*+ parallel(1) */ count(*) from customer a join nation b on a.c_nationkey < b.n_nationkey;
快100倍！
【复现路径】
TPCH10
表结构：
obclient [tpch10]> show create table nation\G
*************************** 1. row ***************************
Table: nation
Create Table: CREATE TABLE `nation` (
`N_NATIONKEY` bigint(20) NOT NULL,
`N_NAME` char(25) NOT NULL,
`N_REGIONKEY` bigint(20) NOT NULL,
`N_COMMENT` varchar(152) DEFAULT NULL,
PRIMARY KEY (`N_NATIONKEY`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.004 sec)
obclient [tpch10]> show create table customer \G
*************************** 1. row ***************************
Table: customer
Create Table: CREATE TABLE `customer` (
`C_CUSTKEY` bigint(20) NOT NULL,
`C_NAME` varchar(25) NOT NULL,
`C_ADDRESS` varchar(40) NOT NULL,
`C_NATIONKEY` bigint(20) NOT NULL,
`C_PHONE` char(15) NOT NULL,
`C_ACCTBAL` decimal(15,2) NOT NULL,
`C_MKTSEGMENT` char(10) NOT NULL,
`C_COMMENT` varchar(117) NOT NULL,
PRIMARY KEY (`C_CUSTKEY`),
KEY `idx1` (`C_NAME`) BLOCK_SIZE 16384 LOCAL,
KEY `idx2` (`C_ACCTBAL`) BLOCK_SIZE 16384 LOCAL,
KEY `idx3` (`C_NATIONKEY`) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set (0.003 sec)
在测试过程中不开并行SQL1的执行时间为3秒（多次重复执行），开并行SQL2的执行时间为0.04秒，应该如何理解呢？
SQL1的执行计划如下：
obclient [tpch10]> explain extended select /*+ parallel(1) */ count(*) from customer a join nation b on a.c_nationkey < b.n_nationkey;
+-----------------------------------------------------------------------------------------------------+
| Query Plan |
+-----------------------------------------------------------------------------------------------------+
| =================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |978495 | |
| |1 |└─NESTED-LOOP JOIN | |12500000|751945 | |
| |2 | ├─TABLE FULL SCAN |b |25 |5 | |
| |3 | └─DISTRIBUTED TABLE RANGE SCAN|a(idx3)|500000 |17608 | |
| =================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_FUN_COUNT(*)(0x7f1a2e040760)]), filter(nil), rowset=256 |
| group(nil), agg_func([T_FUN_COUNT(*)(0x7f1a2e040760)]) |
| 1 - output(nil), filter(nil), rowset=256 |
| conds(nil), nl_params_([b.N_NATIONKEY(0x7f1a2e040200)(:1)]), use_batch=true |
| 2 - output([b.N_NATIONKEY(0x7f1a2e040200)]), filter(nil), rowset=256 |
| access([b.N_NATIONKEY(0x7f1a2e040200)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([b.N_NATIONKEY(0x7f1a2e040200)]), range(MIN ; MAX)always true |
| 3 - output(nil), filter(nil), rowset=256 |
| access([GROUP_ID(0x7f1a2e0f86d0)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([a.C_NATIONKEY(0x7f1a2e03ff20)], [a.C_CUSTKEY(0x7f1a2e040d80)]), range(MIN ; MAX), |
| range_cond([a.C_NATIONKEY(0x7f1a2e03ff20) < :1(0x7f1a2e0cb960)]) |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| PARALLEL(1) |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 > SEL$C6D21C0F |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| LEADING(@"SEL$C6D21C0F" ("tpch10"."b"@"SEL$1" "tpch10"."a"@"SEL$1")) |
| USE_NL(@"SEL$C6D21C0F" "tpch10"."a"@"SEL$1") |
| FULL(@"SEL$C6D21C0F" "b"@"SEL$1") |
| INDEX(@"SEL$C6D21C0F" "a"@"SEL$1" "idx3") |
| USE_DAS(@"SEL$C6D21C0F" "a"@"SEL$1") |
| OUTER_TO_INNER(@"SEL$1") |
| PARALLEL(1) |
| OPTIMIZER_FEATURES_ENABLE('4.0.0.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| b: |
| table_rows:25 |
| physical_range_rows:25 |
| logical_range_rows:25 |
| index_back_rows:0 |
| output_rows:25 |
| table_dop:1 |
| dop_method:Global DOP |
| avaiable_index_name:[nation] |
| stats version:1694254794515646 |
| dynamic sampling level:0 |
| a: |
| table_rows:1500000 |
| physical_range_rows:500000 |
| logical_range_rows:500000 |
| index_back_rows:0 |
| output_rows:500000 |
| table_dop:1 |
| dop_method:DAS DOP |
| avaiable_index_name:[idx1, idx2, idx3, customer] |
| pruned_index_name:[idx1, idx2, customer] |
| stats version:1694254801441111 |
| dynamic sampling level:0 |
| Plan Type: |
| LOCAL |
| Note: |
| Degree of Parallelism is 1 because of hint |
+-----------------------------------------------------------------------------------------------------+
76 rows in set (0.004 sec)
SQL1的执行时间为：
obclient [tpch10]> select /*+ parallel(1) */ count(*) from customer a join nation b on a.c_nationkey < b.n_nationkey;
+----------+
| count(*) |
+----------+
| 18002417 |
+----------+
1 row in set (3.413 sec)
SQL1的SQL_AUDIT信息为：
SVR_IP: 192.168.31.201
SVR_PORT: 2882
REQUEST_ID: 431213
SQL_EXEC_ID: 4261706
TRACE_ID: YB42C0A81FC9-0006059EDF40460A-0-0
SID: 3221509816
CLIENT_IP: 192.168.31.201
CLIENT_PORT: 54870
TENANT_ID: 1004
TENANT_NAME: tpch
EFFECTIVE_TENANT_ID: 1004
USER_ID: 200001
USER_NAME: root
USER_GROUP: 0
USER_CLIENT_IP: 192.168.31.200
DB_ID: 500523
DB_NAME: tpch10
SQL_ID: 7C6D34D0C862BF99E674CE42A638C2FF
QUERY_SQL: select /*+ parallel(1) */ count(*) from customer a join nation b on a.c_nationkey < b.n_nationkey
PLAN_ID: 419
AFFECTED_ROWS: 0
RETURN_ROWS: 1
PARTITION_CNT: 2
RET_CODE: 0
QC_ID: 0
DFO_ID: 0
SQC_ID: 0
WORKER_ID: 0
EVENT:
P1TEXT:
P1: 0
P2TEXT:
P2: 0
P3TEXT:
P3: 0
LEVEL: 0
WAIT_CLASS_ID: 100
WAIT_CLASS#: 0
WAIT_CLASS: OTHER
STATE: MAX_WAIT TIME ZERO
WAIT_TIME_MICRO: 0
TOTAL_WAIT_TIME_MICRO: 0
TOTAL_WAITS: 0
RPC_COUNT: 0
PLAN_TYPE: 1
IS_INNER_SQL: 0
IS_EXECUTOR_RPC: 0
IS_HIT_PLAN: 0
REQUEST_TIME: 1695189590729929
ELAPSED_TIME: 3412135
NET_TIME: 0
NET_WAIT_TIME: 2
QUEUE_TIME: 44
DECODE_TIME: 1
GET_PLAN_TIME: 2174
EXECUTE_TIME: 3409895
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
USER_IO_WAIT_TIME: 0
SCHEDULE_TIME: 0
ROW_CACHE_HIT: 0
BLOOM_FILTER_CACHE_HIT: 0
BLOCK_CACHE_HIT: 2343
DISK_READS: 0
RETRY_CNT: 0
TABLE_SCAN: 1
CONSISTENCY_LEVEL: 3
MEMSTORE_READ_ROW_COUNT: 0
SSSTORE_READ_ROW_COUNT: 18002442
DATA_BLOCK_READ_CNT: 2563
DATA_BLOCK_CACHE_HIT: 2563
INDEX_BLOCK_READ_CNT: 62
INDEX_BLOCK_CACHE_HIT: 36
BLOCKSCAN_BLOCK_CNT: 0
BLOCKSCAN_ROW_CNT: 0
PUSHDOWN_STORAGE_FILTER_ROW_CNT: 0
REQUEST_MEMORY_USED: 654232
EXPECTED_WORKER_COUNT: 0
USED_WORKER_COUNT: 0
SCHED_INFO: NULL
FUSE_ROW_CACHE_HIT: 0
PS_CLIENT_STMT_ID: -1
PS_INNER_STMT_ID: -1
TX_ID: 0
SNAPSHOT_VERSION: 1695189590732174706
REQUEST_TYPE: 2
IS_BATCHED_MULTI_STMT: 0
OB_TRACE_INFO: NULL
PLAN_HASH: 10268919862084901393
LOCK_FOR_READ_TIME: 0
PARAMS_VALUE:
RULE_NAME:
PARTITION_HIT: 1
TX_INTERNAL_ROUTING: 0
TX_STATE_VERSION: 0
SQL2的执行计划如下：
obclient [tpch10]> explain extended select /*+ parallel(2) */ count(*) from customer a join nation b on a.c_nationkey < b.n_nationkey;
+----------------------------------------------------------------------------------------------------------+
| Query Plan |
+----------------------------------------------------------------------------------------------------------+
| ============================================================================ |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------------------- |
| |0 |SCALAR GROUP BY | |1 |301288 | |
| |1 |└─PX COORDINATOR | |2 |301288 | |
| |2 | └─EXCHANGE OUT DISTR |:EX10001|2 |301287 | |
| |3 | └─MERGE GROUP BY | |2 |301287 | |
| |4 | └─NESTED-LOOP JOIN | |12500000|188012 | |
| |5 | ├─EXCHANGE IN DISTR | |25 |11 | |
| |6 | │ └─EXCHANGE OUT DISTR (BC2HOST)|:EX10000|25 |8 | |
| |7 | │ └─PX BLOCK ITERATOR | |25 |3 | |
| |8 | │ └─TABLE FULL SCAN |b |25 |3 | |
| |9 | └─PX PARTITION ITERATOR | |500000 |8805 | |
| |10| └─TABLE RANGE SCAN |a(idx3) |500000 |8805 | |
| ============================================================================ |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f1a4be40760))(0x7f1a4bf00300)]), filter(nil), rowset=256 |
| group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f1a4be40760))(0x7f1a4bf00300)]) |
| 1 - output([T_FUN_COUNT(*)(0x7f1a4be40760)]), filter(nil), rowset=256 |
| 2 - output([T_FUN_COUNT(*)(0x7f1a4be40760)]), filter(nil), rowset=256 |
| dop=2 |
| 3 - output([T_FUN_COUNT(*)(0x7f1a4be40760)]), filter(nil), rowset=256 |
| group(nil), agg_func([T_FUN_COUNT(*)(0x7f1a4be40760)]) |
| 4 - output(nil), filter(nil), rowset=256 |
| conds(nil), nl_params_([b.N_NATIONKEY(0x7f1a4be40200)(:1)]), use_batch=false |
| 5 - output([b.N_NATIONKEY(0x7f1a4be40200)]), filter(nil), rowset=256 |
| 6 - output([b.N_NATIONKEY(0x7f1a4be40200)]), filter(nil), rowset=256 |
| dop=2 |
| 7 - output([b.N_NATIONKEY(0x7f1a4be40200)]), filter(nil), rowset=256 |
| 8 - output([b.N_NATIONKEY(0x7f1a4be40200)]), filter(nil), rowset=256 |
| access([b.N_NATIONKEY(0x7f1a4be40200)]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([b.N_NATIONKEY(0x7f1a4be40200)]), range(MIN ; MAX)always true |
| 9 - output(nil), filter(nil), rowset=256 |
| access all |
| 10 - output(nil), filter(nil), rowset=256 |
| access(nil), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([a.C_NATIONKEY(0x7f1a4be3ff20)], [a.C_CUSTKEY(0x7f1a4be40d80)]), range(MIN ; MAX), |
| range_cond([a.C_NATIONKEY(0x7f1a4be3ff20) < :1(0x7f1a4beca720)]) |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| PARALLEL(2) |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 > SEL$C6D21C0F |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| GBY_PUSHDOWN(@"SEL$C6D21C0F") |
| LEADING(@"SEL$C6D21C0F" ("tpch10"."b"@"SEL$1" "tpch10"."a"@"SEL$1")) |
| USE_NL(@"SEL$C6D21C0F" "tpch10"."a"@"SEL$1") |
| PQ_DISTRIBUTE(@"SEL$C6D21C0F" "tpch10"."a"@"SEL$1" BC2HOST NONE) |
| PARALLEL(@"SEL$C6D21C0F" "b"@"SEL$1" 2) |
| FULL(@"SEL$C6D21C0F" "b"@"SEL$1") |
| PARALLEL(@"SEL$C6D21C0F" "a"@"SEL$1" 2) |
| INDEX(@"SEL$C6D21C0F" "a"@"SEL$1" "idx3") |
| OUTER_TO_INNER(@"SEL$1") |
| PARALLEL(2) |
| OPTIMIZER_FEATURES_ENABLE('4.0.0.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| b: |
| table_rows:25 |
| physical_range_rows:25 |
| logical_range_rows:25 |
| index_back_rows:0 |
| output_rows:25 |
| table_dop:2 |
| dop_method:Global DOP |
| avaiable_index_name:[nation] |
| stats version:1694254794515646 |
| dynamic sampling level:0 |
| a: |
| table_rows:1500000 |
| physical_range_rows:500000 |
| logical_range_rows:500000 |
| index_back_rows:0 |
| output_rows:500000 |
| table_dop:2 |
| dop_method:Global DOP |
| avaiable_index_name:[idx1, idx2, idx3, customer] |
| pruned_index_name:[idx1, idx2, customer] |
| stats version:1694254801441111 |
| dynamic sampling level:0 |
| Plan Type: |
| DISTRIBUTED |
| Note: |
| Degree of Parallelism is 2 because of hint |
+----------------------------------------------------------------------------------------------------------+
97 rows in set (0.004 sec)
SQL2的执行时间为：
obclient [tpch10]> select /*+ parallel(2) */ count(*) from customer a join nation b on a.c_nationkey < b.n_nationkey;
+----------+
| count(*) |
+----------+
| 18002417 |
+----------+
1 row in set (0.046 sec)
SQL2的SQL_AUDIT信息为：
SVR_IP: 192.168.31.201
SVR_PORT: 2882
REQUEST_ID: 431023
SQL_EXEC_ID: 4259502
TRACE_ID: YB42C0A81FC9-0006059EDF404608-0-0
SID: 3221509816
CLIENT_IP: 192.168.31.201
CLIENT_PORT: 54870
TENANT_ID: 1004
TENANT_NAME: tpch
EFFECTIVE_TENANT_ID: 1004
USER_ID: 200001
USER_NAME: root
USER_GROUP: 0
USER_CLIENT_IP: 192.168.31.200
DB_ID: 500523
DB_NAME: tpch10
SQL_ID: BBAC7887A3247003B0456DFF9CCD54E1
QUERY_SQL: select /*+ parallel(2) */ count(*) from customer a join nation b on a.c_nationkey < b.n_nationkey
PLAN_ID: 417
AFFECTED_ROWS: 0
RETURN_ROWS: 1
PARTITION_CNT: 2
RET_CODE: 0
QC_ID: 0
DFO_ID: 0
SQC_ID: 0
WORKER_ID: 0
EVENT: px loop condition wait
P1TEXT: address
P1: 139750419651280
P2TEXT:
P2: 0
P3TEXT:
P3: 0
LEVEL: 0
WAIT_CLASS_ID: 104
WAIT_CLASS#: 4
WAIT_CLASS: CONCURRENCY
STATE: WAITED SHORT TIME
WAIT_TIME_MICRO: 1589
TOTAL_WAIT_TIME_MICRO: 41615
TOTAL_WAITS: 38
RPC_COUNT: 0
PLAN_TYPE: 3
IS_INNER_SQL: 0
IS_EXECUTOR_RPC: 0
IS_HIT_PLAN: 0
REQUEST_TIME: 1695189577202586
ELAPSED_TIME: 45293
NET_TIME: 0
NET_WAIT_TIME: 3
QUEUE_TIME: 45
DECODE_TIME: 1
GET_PLAN_TIME: 2271
EXECUTE_TIME: 42955
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
USER_IO_WAIT_TIME: 0
SCHEDULE_TIME: 0
ROW_CACHE_HIT: 0
BLOOM_FILTER_CACHE_HIT: 0
BLOCK_CACHE_HIT: 0
DISK_READS: 0
RETRY_CNT: 0
TABLE_SCAN: 1
CONSISTENCY_LEVEL: 3
MEMSTORE_READ_ROW_COUNT: 0
SSSTORE_READ_ROW_COUNT: 0
DATA_BLOCK_READ_CNT: 0
DATA_BLOCK_CACHE_HIT: 0
INDEX_BLOCK_READ_CNT: 0
INDEX_BLOCK_CACHE_HIT: 0
BLOCKSCAN_BLOCK_CNT: 0
BLOCKSCAN_ROW_CNT: 0
PUSHDOWN_STORAGE_FILTER_ROW_CNT: 0
REQUEST_MEMORY_USED: 82578688
EXPECTED_WORKER_COUNT: 4
USED_WORKER_COUNT: 4
SCHED_INFO: NULL
FUSE_ROW_CACHE_HIT: 0
PS_CLIENT_STMT_ID: -1
PS_INNER_STMT_ID: -1
TX_ID: 0
SNAPSHOT_VERSION: 1695189577204943341
REQUEST_TYPE: 2
IS_BATCHED_MULTI_STMT: 0
OB_TRACE_INFO: NULL
PLAN_HASH: 2276062942640480755
LOCK_FOR_READ_TIME: 0
PARAMS_VALUE:
RULE_NAME:
PARTITION_HIT: 1
TX_INTERNAL_ROUTING: 0
TX_STATE_VERSION: 0
从执行计划上没有看出来开启并发后有什么特别之处，只是多了一个开启并行正常的exchange（BC2HOST）算子，但是看SQL_AUDIT parallel=2的情况下SSSTORE_READ_ROW_COUNT竟然为0,（parallel=1的情况下是18002442），也就是开启并行后都不需要进行行读了，这个如何理解？