问题描述：4.2版本actiondb SQL长时间执行不出结果 另外发现SQL在加并行hint的时候执行不出来，但是在去掉hint的时候执行时间1分钟左右 SELECT /*+ ENABLE_PARALLEL_DML PARALLEL(32) */ count(1) from (select AX.OC_CARR ,AX.OC_FLT_NBR ,BX.RTE_IND_NEW,BX.LEG_SEQ ,BX.DPT_AIRPT ,BX.ARRV_AIRPT ,AX.RTE FROM ( SELECT A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW ,A.LEG_SEQ ,A.DPT_AIRPT ,A.ARRV_AIRPT ,GROUP_CONCAT( A.V ORDER BY A.LEG_SEQ SEPARATOR '-') AS RTE FROM DEV_TMP.RTE_TEMP_2 A GROUP BY A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW )AX LEFT JOIN (SELECT * ,MAX(LEG_SEQ) OVER(PARTITION BY OC_CARR ,OC_FLT_NBR ,RTE_IND_NEW ORDER BY LEG_SEQ DESC) IDMAX FROM DEV_TMP.RTE_TEMP_2 )BX ON AX.OC_CARR=BX.OC_CARR AND AX.OC_FLT_NBR=BX.OC_FLT_NBR AND AX.RTE_IND_NEW=BX.RTE_IND_NEW WHERE BX.LEG_SEQ=BX.IDMAX ) ; 主要涉及表为 DEV_TMP.RTE_TEMP_2 row_count=6018477 统计信息进行过收集 ------explain 执行计划 +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================================================================= | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | --------------------------------------------------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |158845 | | | |1 |└─NESTED-LOOP JOIN | |1 |158845 | | | |2 | ├─PX COORDINATOR | |1 |158526 | | | |3 | │ └─EXCHANGE OUT DISTR |:EX10000 |1 |158526 | | | |4 | │ └─SUBPLAN SCAN |BX |1 |158526 | | | |5 | │ └─PX PARTITION ITERATOR | |6015950 |156434 | | | |6 | │ └─WINDOW FUNCTION | |6015950 |156434 | | | |7 | │ └─TABLE FULL SCAN |rte_temp_2(index_name,Reverse)|6015950 |11293 | | | |8 | └─SUBPLAN SCAN |AX |1 |270 | | | |9 | └─LIMIT | |1 |270 | | | |10| └─PX COORDINATOR | |1 |270 | | | |11| └─EXCHANGE OUT DISTR |:EX20000 |1 |269 | | | |12| └─LIMIT | |1 |269 | | | |13| └─PX PARTITION ITERATOR| |1 |269 | | | |14| └─TABLE RANGE SCAN |A(index_name) |1 |269 | | | ============================================================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)(0x7f3ec0ac7910)]), filter(nil), rowset=256 | | group(nil), agg_func([T_FUN_COUNT(*)(0x7f3ec0ac7910)]) | | 1 - output(nil), filter(nil), rowset=256 | | conds(nil), nl_params_([BX.RTE_IND_NEW(0x7f3ec0ac3940)(:3)], [BX.OC_CARR(0x7f3ec0ac0ee0)(:4)], [BX.OC_FLT_NBR(0x7f3ec0ac2410)(:5)]), use_batch=false | | 2 - output([BX.RTE_IND_NEW(0x7f3ec0ac3940)], [BX.OC_CARR(0x7f3ec0ac0ee0)], [BX.OC_FLT_NBR(0x7f3ec0ac2410)]), filter(nil), rowset=256 | | 3 - output([BX.RTE_IND_NEW(0x7f3ec0ac3940)], [BX.OC_CARR(0x7f3ec0ac0ee0)], [BX.OC_FLT_NBR(0x7f3ec0ac2410)]), filter(nil), rowset=256 | | dop=32 | | 4 - output([BX.OC_CARR(0x7f3ec0ac0ee0)], [BX.OC_FLT_NBR(0x7f3ec0ac2410)], [BX.RTE_IND_NEW(0x7f3ec0ac3940)]), filter([BX.LEG_SEQ(0x7f3ec0ac4b90) = BX.IDMAX(0x7f3ec0ac4e70)(0x7f3ec0ac4490)]), rowset=256 | | access([BX.OC_CARR(0x7f3ec0ac0ee0)], [BX.OC_FLT_NBR(0x7f3ec0ac2410)], [BX.RTE_IND_NEW(0x7f3ec0ac3940)], [BX.LEG_SEQ(0x7f3ec0ac4b90)], [BX.IDMAX(0x7f3ec0ac4e70)]) | | 5 - output([rte_temp_2.OC_CARR(0x7f3ec0ab89c0)], [rte_temp_2.OC_FLT_NBR(0x7f3ec0ab8c90)], [rte_temp_2.RTE_IND_NEW(0x7f3ec0ab9270)], [rte_temp_2.LEG_SEQ(0x7f3ec0ab9540)], | | [T_FUN_MAX(rte_temp_2.LEG_SEQ(0x7f3ec0ab9540))(0x7f3ec0aba780)(0x7f3ec0aba360)]), filter(nil), rowset=256 | | partition wise, force partition granule | | 6 - output([rte_temp_2.OC_CARR(0x7f3ec0ab89c0)], [rte_temp_2.OC_FLT_NBR(0x7f3ec0ab8c90)], [rte_temp_2.RTE_IND_NEW(0x7f3ec0ab9270)], [rte_temp_2.LEG_SEQ(0x7f3ec0ab9540)], | | [T_FUN_MAX(rte_temp_2.LEG_SEQ(0x7f3ec0ab9540))(0x7f3ec0aba780)(0x7f3ec0aba360)]), filter(nil), rowset=256 | | win_expr(T_FUN_MAX(rte_temp_2.LEG_SEQ(0x7f3ec0ab9540))(0x7f3ec0aba780)(0x7f3ec0aba360)), partition_by([rte_temp_2.OC_CARR(0x7f3ec0ab89c0)], [rte_temp_2.OC_FLT_NBR(0x7f3ec0ab8c90)], | | [rte_temp_2.RTE_IND_NEW(0x7f3ec0ab9270)]), order_by([rte_temp_2.LEG_SEQ(0x7f3ec0ab9540), DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(CURRENT | | ROW) | | 7 - output([rte_temp_2.OC_CARR(0x7f3ec0ab89c0)], [rte_temp_2.OC_FLT_NBR(0x7f3ec0ab8c90)], [rte_temp_2.RTE_IND_NEW(0x7f3ec0ab9270)], [rte_temp_2.LEG_SEQ(0x7f3ec0ab9540)]), filter(nil), rowset=256 | | access([rte_temp_2.OC_CARR(0x7f3ec0ab89c0)], [rte_temp_2.OC_FLT_NBR(0x7f3ec0ab8c90)], [rte_temp_2.RTE_IND_NEW(0x7f3ec0ab9270)], [rte_temp_2.LEG_SEQ(0x7f3ec0ab9540)]), partitions(p[0-479]) | | is_index_back=false, is_global_index=false, | | range_key([rte_temp_2.OC_CARR(0x7f3ec0ab89c0)], [rte_temp_2.OC_FLT_NBR(0x7f3ec0ab8c90)], [rte_temp_2.RTE_IND_NEW(0x7f3ec0ab9270)], [rte_temp_2.LEG_SEQ(0x7f3ec0ab9540)], | | [rte_temp_2.__pk_increment(0x7f3ec0ac8cf0)]), range(MIN,MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX,MAX)always true | | 8 - output(nil), filter(nil), rowset=256 | | access(nil) | | 9 - output([A.OC_CARR(0x7f3ec0a8e6a0)], [A.OC_FLT_NBR(0x7f3ec0a8e970)], [A.RTE_IND_NEW(0x7f3ec0a8f5b0)]), filter(nil), rowset=256 | | limit(1), offset(nil) | | 10 - output([A.OC_CARR(0x7f3ec0a8e6a0)], [A.OC_FLT_NBR(0x7f3ec0a8e970)], [A.RTE_IND_NEW(0x7f3ec0a8f5b0)]), filter(nil), rowset=256 | | 11 - output([A.OC_CARR(0x7f3ec0a8e6a0)], [A.OC_FLT_NBR(0x7f3ec0a8e970)], [A.RTE_IND_NEW(0x7f3ec0a8f5b0)]), filter(nil), rowset=256 | | dop=32 | | 12 - output([A.OC_CARR(0x7f3ec0a8e6a0)], [A.OC_FLT_NBR(0x7f3ec0a8e970)], [A.RTE_IND_NEW(0x7f3ec0a8f5b0)]), filter(nil), rowset=256 | | limit(1), offset(nil) | | 13 - output([A.OC_CARR(0x7f3ec0a8e6a0)], [A.OC_FLT_NBR(0x7f3ec0a8e970)], [A.RTE_IND_NEW(0x7f3ec0a8f5b0)]), filter(nil), rowset=256 | | force partition granule | | 14 - output([A.OC_CARR(0x7f3ec0a8e6a0)], [A.OC_FLT_NBR(0x7f3ec0a8e970)], [A.RTE_IND_NEW(0x7f3ec0a8f5b0)]), filter(nil), rowset=256 | | access([A.OC_CARR(0x7f3ec0a8e6a0)], [A.OC_FLT_NBR(0x7f3ec0a8e970)], [A.RTE_IND_NEW(0x7f3ec0a8f5b0)]), partitions(p[0-479]) | | limit(1), offset(nil), is_index_back=false, is_global_index=false, | | range_key([A.OC_CARR(0x7f3ec0a8e6a0)], [A.OC_FLT_NBR(0x7f3ec0a8e970)], [A.RTE_IND_NEW(0x7f3ec0a8f5b0)], [A.LEG_SEQ(0x7f3e98f5d0d0)], [A.__pk_increment(0x7f3ec0ac88a0)]), | | range(MIN,MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX,MAX)always true, | | range_cond([A.RTE_IND_NEW(0x7f3ec0a8f5b0) = :3(0x7f3e98f22780)], [A.OC_CARR(0x7f3ec0a8e6a0) = :4(0x7f3e98f22fb0)], [A.OC_FLT_NBR(0x7f3ec0a8e970) = | | :5(0x7f3e98f237e0)]) | | Used Hint: | | ------------------------------------- | | /*+ | | | | PARALLEL(32) | | ENABLE_PARALLEL_DML | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 > SEL$EA493D33 > SEL$0EE93CA6 > SEL$D3A5388B | | stmt_id:2, SEL$2 > SEL$89B3808A | | stmt_id:3, SEL$3 > SEL$3E84DA65 > SEL$50E3642C | | stmt_id:4, SEL$4 > SEL$3656C1FA | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | LEADING(@"SEL$D3A5388B" ("BX"@"SEL$2" "AX"@"SEL$2")) | | USE_NL(@"SEL$D3A5388B" "AX"@"SEL$2") | | PQ_DISTRIBUTE(@"SEL$D3A5388B" "AX"@"SEL$2" LOCAL LOCAL) | | PQ_DISIRIBUTE_WINDOW(@"SEL$3656C1FA" HASH) | | PARALLEL(@"SEL$3656C1FA" "dev_tmp"."rte_temp_2"@"SEL$4" 32) | | INDEX(@"SEL$3656C1FA" "dev_tmp"."rte_temp_2"@"SEL$4" "index_name") | | PARALLEL(@"SEL$50E3642C" "A"@"SEL$3" 32) | | INDEX(@"SEL$50E3642C" "A"@"SEL$3" "index_name") | | SIMPLIFY_GROUP_BY(@"SEL$1") | | PROJECT_PRUNE(@"SEL$2") | | PROJECT_PRUNE(@"SEL$3") | | PROJECT_PRUNE(@"SEL$4") | | MERGE(@"SEL$89B3808A" > "SEL$EA493D33") | | OUTER_TO_INNER(@"SEL$0EE93CA6") | | SIMPLIFY_GROUP_BY(@"SEL$3E84DA65") | | PARALLEL(32) | | ENABLE_PARALLEL_DML | | OPTIMIZER_FEATURES_ENABLE('4.0.0.0') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | rte_temp_2: | | table_rows:6015950 | | physical_range_rows:6015950 | | logical_range_rows:6015950 | | index_back_rows:0 | | output_rows:6015950 | | table_dop:32 | | dop_method:Global DOP | | avaiable_index_name:[index_name, rte_temp_2] | | pruned_index_name:[rte_temp_2] | | stats version:1696753583623658 | | dynamic sampling level:0 | | A: | | table_rows:6015950 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:32 | | dop_method:Global DOP | | avaiable_index_name:[index_name, rte_temp_2] | | pruned_index_name:[rte_temp_2] | | stats version:1696753583623658 | | dynamic sampling level:0 | | Plan Type: | | DISTRIBUTED | | Note: | | Degree of Parallelism is 32 because of hint | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 129 rows in set (0.02 sec) -----定位trace_id出observer日志信息如下 observer.log 10.160.34.36：grep YB420AA02224-0006075830CFA032-0-0 mysql> select * from gv$ob_processlist where state='active' and info like '%temp_2%'; +--------------+----------+----------+------------+------+--------------------+-----------+--------+---------+------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+-----------+-----------+------------+----------------------------------+----------+-----------+------------+-----------------------------------+-------------+--------+--------+-------------+ | SVR_IP | SVR_PORT | SQL_PORT | ID | USER | HOST | DB | TENANT | COMMAND | TIME | TOTAL_TIME | STATE | INFO | PROXY_SESSID | MASTER_SESSID | USER_CLIENT_IP | USER_HOST | RETRY_CNT | RETRY_INFO | SQL_ID | TRANS_ID | THREAD_ID | SSL_CIPHER | TRACE_ID | TRANS_STATE | ACTION | MODULE | CLIENT_INFO | +--------------+----------+----------+------------+------+--------------------+-----------+--------+---------+------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+-----------+-----------+------------+----------------------------------+----------+-----------+------------+-----------------------------------+-------------+--------+--------+-------------+ | 10.160.34.36 | 2882 | 2881 | 3221531001 | root | 10.160.34.36:50096 | oceanbase | sys | Query | 0 | 0 | ACTIVE | select * from gv$ob_processlist where state='active' and info like '%temp_2%' | 765649474856092245 | NULL | 10.160.34.35 | % | 0 | 0 | 60B91B9512EE276EC0560CF36F3E9550 | 0 | 54613 | NULL | YB420AA02224-000607585AEFA577-0-0 | | | | | | 10.160.34.36 | 2882 | 2881 | 3221666838 | root | 10.160.34.36:21494 | oceanbase | tnt_nh | Query | 118 | 118 | ACTIVE | SELECT /*+ ENABLE_PARALLEL_DML PARALLEL(32) */ count(1) from(select AX.OC_CARR ,AX.OC_FLT_NBR ,BX.RTE_IND_NEW,BX.LEG_SEQ ,BX.DPT_AIRPT ,BX.ARRV_AIRPT ,AX.RTEFROM (SELECT A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW ,A.LEG_SEQ ,A.DPT_AIRPT ,A.ARRV_AIRPT,GROUP_CONCAT( A.V ORDER BY A.LEG_SEQ SEPARATOR '-') AS RTEFROM DEV_TMP.RTE_TEMP_2 AGROUP BY A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW)AXLEFT JOIN(SELECT * ,MAX(LEG_SEQ) OVER(PARTITION BY OC_CARR ,OC_FLT_NBR ,RTE_IND_NEW ORDER BY LEG_SEQ DESC) IDMAXFROM DEV_TMP.RTE_TEMP_2)BXON AX.OC_CARR=BX.OC_CARRAND AX.OC_FLT_NBR=BX.OC_FLT_NBRAND AX.RTE_IND_NEW=BX.RTE_IND_NEWWHERE BX.LEG_SEQ=BX.IDMAX ) | 765649474856092372 | NULL | 10.160.34.35 | % | 0 | 0 | 0EC3CE8ACE5FFBE216A3608B33B6E805 | 0 | 112273 | NULL | YB420AA02224-0006075830CFA032-0-0 | | | | | +--------------+----------+----------+------------+------+--------------------+-----------+--------+---------+------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+-----------+-----------+------------+----------------------------------+----------+-----------+------------+-----------------------------------+-------------+--------+--------+-------------+ 2 rows in set (0.01 sec) mysql> select * from oceanbase.gv$ob_sql_audit where trace_id='YB420AA02224-0006075830CFA032-0-0' and SVR_IP='10.160.34.36' and svr_port=2882 and sql_id='0EC3CE8ACE5FFBE216A3608B33B6E805' and ELAPSED_TIME >10000000; +--------------+----------+------------+-------------+-----------------------------------+------------+--------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------------------+---------+-----------------+--------+------+--------+------+-------+---------------+-------------+-------------+-------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+---------------------+--------------+-----------------------+---------------+---------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+ | SVR_IP | SVR_PORT | REQUEST_ID | SQL_EXEC_ID | TRACE_ID | SID | CLIENT_IP | CLIENT_PORT | TENANT_ID | TENANT_NAME | EFFECTIVE_TENANT_ID | USER_ID | USER_NAME | USER_GROUP | USER_CLIENT_IP | DB_ID | DB_NAME | SQL_ID | QUERY_SQL | PLAN_ID | AFFECTED_ROWS | RETURN_ROWS | PARTITION_CNT | RET_CODE | QC_ID | DFO_ID | SQC_ID | WORKER_ID | EVENT | P1TEXT | P1 | P2TEXT | P2 | P3TEXT | P3 | LEVEL | WAIT_CLASS_ID | WAIT_CLASS# | WAIT_CLASS | STATE | WAIT_TIME_MICRO | TOTAL_WAIT_TIME_MICRO | TOTAL_WAITS | RPC_COUNT | PLAN_TYPE | IS_INNER_SQL | IS_EXECUTOR_RPC | IS_HIT_PLAN | REQUEST_TIME | ELAPSED_TIME | NET_TIME | NET_WAIT_TIME | QUEUE_TIME | DECODE_TIME | GET_PLAN_TIME | EXECUTE_TIME | APPLICATION_WAIT_TIME | CONCURRENCY_WAIT_TIME | USER_IO_WAIT_TIME | SCHEDULE_TIME | ROW_CACHE_HIT | BLOOM_FILTER_CACHE_HIT | BLOCK_CACHE_HIT | DISK_READS | RETRY_CNT | TABLE_SCAN | CONSISTENCY_LEVEL | MEMSTORE_READ_ROW_COUNT | SSSTORE_READ_ROW_COUNT | DATA_BLOCK_READ_CNT | DATA_BLOCK_CACHE_HIT | INDEX_BLOCK_READ_CNT | INDEX_BLOCK_CACHE_HIT | BLOCKSCAN_BLOCK_CNT | BLOCKSCAN_ROW_CNT | PUSHDOWN_STORAGE_FILTER_ROW_CNT | REQUEST_MEMORY_USED | EXPECTED_WORKER_COUNT | USED_WORKER_COUNT | SCHED_INFO | FUSE_ROW_CACHE_HIT | PS_CLIENT_STMT_ID | PS_INNER_STMT_ID | TX_ID | SNAPSHOT_VERSION | REQUEST_TYPE | IS_BATCHED_MULTI_STMT | OB_TRACE_INFO | PLAN_HASH | LOCK_FOR_READ_TIME | PARAMS_VALUE | RULE_NAME | PARTITION_HIT | TX_INTERNAL_ROUTING | TX_STATE_VERSION | +--------------+----------+------------+-------------+-----------------------------------+------------+--------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------------------+---------+-----------------+--------+------+--------+------+-------+---------------+-------------+-------------+-------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+---------------------+--------------+-----------------------+---------------+---------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+ | 10.160.34.36 | 2882 | 75169878 | 95620487 | YB420AA02224-0006075830CFA032-0-0 | 3221642700 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 12 | px loop condition wait | address | 139938040361048 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 9560 | 1084104165 | 1033206 | 0 | 3 | 0 | 1 | 1 | 1697000513853482 | 1085973239 | 0 | 0 | 0 | 0 | 0 | 1085973239 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 0 | -1 | 0 | 12558 | 4 | 4 | 2 | 1 | 1 | 5739 | 0 | 473128 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169879 | 95620488 | YB420AA02224-0006075830CFA032-0-0 | 3221642929 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 14 | px loop condition wait | address | 140142715097176 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 6418 | 1084081264 | 1033031 | 0 | 3 | 0 | 1 | 1 | 1697000513860324 | 1085966468 | 0 | 0 | 0 | 0 | 0 | 1085966468 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 0 | -1 | 0 | 6763 | 4 | 4 | 2 | 1 | 1 | 6082 | 0 | 464736 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169880 | 95620489 | YB420AA02224-0006075830CFA032-0-0 | 3221637358 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 8 | px loop condition wait | address | 140012662214744 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 6478 | 1084124627 | 1032735 | 0 | 3 | 0 | 1 | 1 | 1697000513853317 | 1085973489 | 0 | 0 | 0 | 0 | 0 | 1085973489 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | -1 | 0 | 12631 | 8 | 8 | 4 | 2 | 1 | 5843 | 0 | 489384 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169881 | 95620490 | YB420AA02224-0006075830CFA032-0-0 | 3221522892 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 9 | px loop condition wait | address | 140140851392600 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 8193 | 1084081851 | 1033341 | 0 | 3 | 0 | 1 | 1 | 1697000513853589 | 1085973556 | 0 | 0 | 0 | 0 | 0 | 1085973556 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 0 | -1 | 0 | 12291 | 4 | 4 | 2 | 1 | 1 | 5586 | 0 | 472928 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169882 | 95620491 | YB420AA02224-0006075830CFA032-0-0 | 3221642760 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 0 | px loop condition wait | address | 140134276157528 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED KNOWN TIME | 14718 | 1083850717 | 1032936 | 0 | 3 | 0 | 1 | 1 | 1697000513808117 | 1086019303 | 0 | 0 | 0 | 0 | 0 | 1086019303 | 0 | 0 | 0 | 0 | 0 | 0 | 75 | 0 | 0 | 0 | -1 | 0 | 181460 | 60 | 60 | 30 | 15 | 15 | 92034 | 0 | 489512 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169883 | 95620492 | YB420AA02224-0006075830CFA032-0-0 | 3221642921 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 2 | px loop condition wait | address | 140128625332312 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 7601 | 1084100895 | 1032961 | 0 | 3 | 0 | 1 | 1 | 1697000513847967 | 1085979461 | 0 | 0 | 0 | 0 | 0 | 1085979461 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | -1 | 0 | 15833 | 8 | 8 | 4 | 2 | 2 | 11822 | 0 | 472800 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169884 | 95620493 | YB420AA02224-0006075830CFA032-0-0 | 3221642227 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 13 | px loop condition wait | address | 140141035475032 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 6470 | 1084107456 | 1032973 | 0 | 3 | 0 | 1 | 1 | 1697000513854536 | 1085973066 | 0 | 0 | 0 | 0 | 0 | 1085973066 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 0 | -1 | 0 | 11726 | 4 | 4 | 2 | 1 | 1 | 6311 | 0 | 464608 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169885 | 95620494 | YB420AA02224-0006075830CFA032-0-0 | 3221642855 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 7 | px loop condition wait | address | 139950030377048 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED KNOWN TIME | 10735 | 1084099336 | 1033223 | 0 | 3 | 0 | 1 | 1 | 1697000513853343 | 1085974868 | 0 | 0 | 0 | 0 | 0 | 1085974868 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 0 | -1 | 0 | 11120 | 4 | 4 | 2 | 1 | 1 | 5761 | 0 | 472928 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169886 | 95620495 | YB420AA02224-0006075830CFA032-0-0 | 3221639660 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 1 | px loop condition wait | address | 140047709876312 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 7302 | 1084106089 | 1033165 | 0 | 3 | 0 | 1 | 1 | 1697000513813046 | 1086015268 | 0 | 0 | 0 | 0 | 0 | 1086015268 | 0 | 0 | 0 | 0 | 0 | 0 | 14 | 0 | 0 | 0 | -1 | 0 | 35831 | 11 | 11 | 6 | 3 | 2 | 12349 | 0 | 497704 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169887 | 95620496 | YB420AA02224-0006075830CFA032-0-0 | 3221631555 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 6 | px loop condition wait | address | 140155013525592 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 7727 | 1084099443 | 1033276 | 0 | 3 | 0 | 1 | 1 | 1697000513848599 | 1085980003 | 0 | 0 | 0 | 0 | 0 | 1085980003 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | -1 | 0 | 15058 | 8 | 8 | 4 | 2 | 2 | 12249 | 0 | 481320 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169888 | 95620497 | YB420AA02224-0006075830CFA032-0-0 | 3221639700 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 3 | px loop condition wait | address | 140101537476696 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 8956 | 1084132981 | 1033527 | 0 | 3 | 0 | 1 | 1 | 1697000513847993 | 1085980774 | 0 | 0 | 0 | 0 | 0 | 1085980774 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | -1 | 0 | 14347 | 8 | 8 | 4 | 2 | 2 | 11916 | 0 | 481192 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169889 | 95620498 | YB420AA02224-0006075830CFA032-0-0 | 3221642830 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 11 | px loop condition wait | address | 140149062971480 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 8446 | 1084070253 | 1032913 | 0 | 3 | 0 | 1 | 1 | 1697000513853616 | 1085975409 | 0 | 0 | 0 | 0 | 0 | 1085975409 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | -1 | 0 | 12739 | 8 | 8 | 4 | 2 | 1 | 6286 | 0 | 480992 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169890 | 95620500 | YB420AA02224-0006075830CFA032-0-0 | 3221627299 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 10 | px loop condition wait | address | 139991434899544 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED KNOWN TIME | 10598 | 1084085689 | 1033456 | 0 | 3 | 0 | 1 | 1 | 1697000513854763 | 1085974675 | 0 | 0 | 0 | 0 | 0 | 1085974675 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | -1 | 0 | 12787 | 8 | 8 | 4 | 2 | 1 | 6432 | 0 | 481056 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169891 | 95620503 | YB420AA02224-0006075830CFA032-0-0 | 3221643217 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 5 | px loop condition wait | address | 140148888023128 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 7699 | 1084071289 | 1033423 | 0 | 3 | 0 | 1 | 1 | 1697000513848466 | 1085982268 | 0 | 0 | 0 | 0 | 0 | 1085982268 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | -1 | 0 | 15275 | 8 | 8 | 4 | 2 | 2 | 11889 | 0 | 481192 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169892 | 95620504 | YB420AA02224-0006075830CFA032-0-0 | 3221635693 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 15 | px loop condition wait | address | 140141041569880 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 6389 | 1084117955 | 1033276 | 0 | 3 | 0 | 1 | 1 | 1697000513866897 | 1085964012 | 0 | 0 | 0 | 0 | 0 | 1085964012 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 0 | 0 | 0 | -1 | 0 | 2786 | 4 | 4 | 2 | 1 | 1 | 6052 | 0 | 472928 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169893 | 95620505 | YB420AA02224-0006075830CFA032-0-0 | 3221643026 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 0EC3CE8ACE5FFBE216A3608B33B6E805 | | 4237 | 0 | 0 | 0 | -4662 | 1 | 0 | 1 | 4 | px loop condition wait | address | 140148103426136 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 8210 | 1084045479 | 1032871 | 0 | 3 | 0 | 1 | 1 | 1697000513848199 | 1085982713 | 0 | 0 | 0 | 0 | 0 | 1085982713 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 0 | 0 | 0 | -1 | 0 | 14442 | 8 | 8 | 4 | 2 | 2 | 11919 | 0 | 489512 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75169894 | 92367414 | YB420AA02224-0006075830CFA032-0-0 | 3221666838 | 10.160.34.36 | 21494 | 1002 | tnt_nh | 1002 | 200001 | root | 0 | 10.160.34.35 | 201001 | oceanbase | 0EC3CE8ACE5FFBE216A3608B33B6E805 | SELECT /*+ ENABLE_PARALLEL_DML PARALLEL(32) */ count(1) from(select AX.OC_CARR ,AX.OC_FLT_NBR ,BX.RTE_IND_NEW,BX.LEG_SEQ ,BX.DPT_AIRPT ,BX.ARRV_AIRPT ,AX.RTEFROM (SELECT A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW ,A.LEG_SEQ ,A.DPT_AIRPT ,A.ARRV_AIRPT,GROUP_CONCAT( A.V ORDER BY A.LEG_SEQ SEPARATOR '-') AS RTEFROM DEV_TMP.RTE_TEMP_2 AGROUP BY A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW)AXLEFT JOIN(SELECT * ,MAX(LEG_SEQ) OVER(PARTITION BY OC_CARR ,OC_FLT_NBR ,RTE_IND_NEW ORDER BY LEG_SEQ DESC) IDMAXFROM DEV_TMP.RTE_TEMP_2)BXON AX.OC_CARR=BX.OC_CARRAND AX.OC_FLT_NBR=BX.OC_FLT_NBRAND AX.RTE_IND_NEW=BX.RTE_IND_NEWWHERE BX.LEG_SEQ=BX.IDMAX ) | 4237 | 0 | 0 | 960 | -5065 | 0 | 0 | 0 | 0 | px loop condition wait | address | 139950694369120 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED KNOWN TIME | 13544 | 811547925 | 2268769 | 0 | 3 | 0 | 0 | 0 | 1697000513771784 | 1086064116 | 0 | 3 | 42 | 0 | 24945 | 1086039085 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 67151520526 | 64 | 64 | NULL | 0 | -1 | -1 | 0 | 1697000513796861727 | 2 | 0 | NULL | 5496358758150576028 | 0 | | | 1 | 0 | 0 | +--------------+----------+------------+-------------+-----------------------------------+------------+--------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------------------+---------+-----------------+--------+------+--------+------+-------+---------------+-------------+-------------+-------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+---------------------+--------------+-----------------------+---------------+---------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+ 17 rows in set (34.01 sec) mysql> select svr_ip,plan_depth,plan_line_id,operator,name,rows,cost,property from gv$ob_plan_cache_plan_explain where tenant_id=1002 and svip='10.160.34.36' and svr_port=2882 and plan_id=4237 ; +--------------+------------+--------------+-----------------------------+------------------------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | svr_ip | plan_depth | plan_line_id | operator | name | rows | cost | property | +--------------+------------+--------------+-----------------------------+------------------------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 10.160.34.36 | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL | 1 | 163028 | NULL | | 10.160.34.36 | 1 | 1 | PHY_NESTED_LOOP_JOIN | NULL | 1 | 163028 | NULL | | 10.160.34.36 | 2 | 2 | PHY_PX_FIFO_COORD | NULL | 1 | 162677 | NULL | | 10.160.34.36 | 3 | 3 | PHY_PX_REDUCE_TRANSMIT | NULL | 1 | 162677 | NULL | | 10.160.34.36 | 4 | 4 | PHY_SUBPLAN_SCAN | NULL | 1 | 162677 | NULL | | 10.160.34.36 | 5 | 5 | PHY_GRANULE_ITERATOR | NULL | 6018477 | 160584 | NULL | | 10.160.34.36 | 6 | 6 | PHY_WINDOW_FUNCTION | NULL | 6018477 | 160584 | NULL | | 10.160.34.36 | 7 | 7 | PHY_TABLE_SCAN | rte_temp_2(index_name) | 6018477 | 11257 | table_rows:6018477, psical_range_rows:6018477, logical_range_rows:6018477, index_back_rows:0, output_rows:6018477, avaiable_index_name[index_name,rte_temp_2], pred_index_name[rte_temp_2] | | 10.160.34.36 | 2 | 8 | PHY_SUBPLAN_SCAN | NULL | 1 | 300 | NULL | | 10.160.34.36 | 3 | 9 | PHY_LIMIT | NULL | 1 | 300 | NULL | | 10.160.34.36 | 4 | 10 | PHY_PX_FIFO_COORD | NULL | 1 | 300 | NULL | | 10.160.34.36 | 5 | 11 | PHY_PX_REDUCE_TRANSMIT | NULL | 1 | 300 | NULL | | 10.160.34.36 | 6 | 12 | PHY_LIMIT | NULL | 1 | 300 | NULL | | 10.160.34.36 | 7 | 13 | PHY_GRANULE_ITERATOR | NULL | 1 | 300 | NULL | | 10.160.34.36 | 8 | 14 | PHY_TABLE_SCAN | A(index_name) | 1 | 300 | table_rows:6018477, psical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[index_name,rte_temp_2], pruned_index_name[rttemp_2] | +--------------+------------+--------------+-----------------------------+------------------------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 15 rows in set (0.01 sec) -----observer 日志 failed to execute plan(ret=-4662) [2023-10-11 13:18:39.841540] WDIAG [SQL.ENG] check_status (ob_px_data_ch_provider.cpp:424) [11494][T1002_PX_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=23][errcode=-4662] received a interrupt(code={code:-4662, info:{msg:"tid:112273,from:"10.160.34.36:2882",PX ABORT DFO"}}, ret=-4662) [2023-10-11 13:18:39.841570] WDIAG [SQL.ENG] get_transmit_data_ch (ob_px_sqc_proxy.cpp:267) [11494][T1002_PX_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=34][errcode=-4662] fail peek data channel from ch_provider(ret=-4662) [2023-10-11 13:18:39.841578] WDIAG [SQL.ENG] get_data_ch (ob_px_transmit_op.cpp:99) [11494][T1002_PX_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=8][errcode=-4662] fail get data ch sets from provider(ret=-4662) [2023-10-11 13:18:39.841584] WDIAG [SQL.ENG] init_channel (ob_px_transmit_op.cpp:317) [11494][T1002_PX_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=6][errcode=-4662] Fail to get data dtl channel(ret=-4662) [2023-10-11 13:18:39.841591] WDIAG [SQL.ENG] inner_open (ob_px_transmit_op.cpp:201) [11494][T1002_PX_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=6][errcode=-4662] fail to exec init_channel(*trans_input)(ret=-4662) [2023-10-11 13:18:39.841596] WDIAG [SQL.ENG] inner_open (ob_px_reduce_transmit_op.cpp:37) [11494][T1002_PX_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=5][errcode=-4662] initialize operator context failed(ret=-4662) [2023-10-11 13:18:39.841602] WDIAG [SQL.ENG] open (ob_operator.cpp:714) [11494][T1002_PX_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=5][errcode=-4662] Open this operator failed(ret=-4662, op_type="PHY_PX_REDUCE_TRANSMIT") [2023-10-11 13:18:39.841612] WDIAG [SQL.ENG] execute (ob_px_task_process.cpp:288) [11494][T1002_PX_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=8][errcode=-4662] fail open dfo op(ret=-4662) [2023-10-11 13:18:39.841632] WDIAG [SQL.ENG] do_process (ob_px_task_process.cpp:451) [11494][T1002_PX_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=6][errcode=-4662] failed to execute plan(ret=-4662) [2023-10-11 13:18:39.841641] WDIAG [SQL.ENG] process (ob_px_task_process.cpp:178) [11494][T1002_PX_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=6][errcode=-4662] failed to process(get_tenant_id()=1002, ret=-4662) [2023-10-11 13:18:39.842265] INFO [SQL.ENG] all_task_success (ob_px_sqc_handler.cpp:304) [11494][T1002_PX_G0][T1002][Y0-0000000000000000-0-0] [lt=7] px task not succ(task.get_result()=-4662, i=2) [2023-10-11 13:18:39.842481] WDIAG update_qc_error_code (ob_px_util.h:589) [112273][T1002_L0_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=5][errcode=-4662] [2023-10-11 13:18:39.842496] WDIAG update_qc_error_code (ob_px_util.h:589) [112273][T1002_L0_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=10][errcode=-4662] [2023-10-11 13:18:39.845149] INFO [STORAGE.BLKMGR] alloc_block (ob_block_manager.cpp:329) [112785][T1002_PX_G0][T1002][YB420AA02225-00060757FA21A99C-0-0] [lt=16] successfully alloc block(macro_id=[1747507](ver=0,mode=0,seq=13793574)) [2023-10-11 13:18:39.845477] INFO [STORAGE.BLKMGR] async_write (ob_macro_block_handle.cpp:178) [112785][T1002_PX_G0][T1002][YB420AA02225-00060757FA21A99C-0-0] [lt=24] Async write macro block(macro_id_=[1747507](ver=0,mode=0,seq=13793574)) [2023-10-11 13:18:39.845614] INFO [STORAGE.BLKMGR] alloc_block (ob_block_manager.cpp:329) [2501][T1002_PX_G0][T1002][YB420AA02225-00060757BF51AA7F-0-0] [lt=11] successfully alloc block(macro_id=[1747508](ver=0,mode=0,seq=13793575)) [2023-10-11 13:18:39.846593] INFO [SQL.ENG] broadcast_dfo (ob_px_interruption.cpp:74) [112273][T1002_L0_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=4] success to send interrupt message(int_code={code:-4662, info:{msg:"tid:112273,from:"10.160.34.36:2882",PX ABORT DFO"}}, addr="10.160.34.37:2882", interrupt_id=task(1696922810486189,4303357292)) [2023-10-11 13:18:39.846619] INFO [SQL.ENG] broadcast_dfo (ob_px_interruption.cpp:74) [112273][T1002_L0_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=18] success to send interrupt message(int_code={code:-4662, info:{msg:"tid:112273,from:"10.160.34.36:2882",PX ABORT DFO"}}, addr="10.160.34.36:2882", interrupt_id=task(1696922810486189,4303357292)) [2023-10-11 13:18:39.846643] INFO [SQL.ENG] broadcast_dfo (ob_px_interruption.cpp:74) [112273][T1002_L0_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=6] success to send interrupt message(int_code={code:-4662, info:{msg:"tid:112273,from:"10.160.34.36:2882",PX ABORT DFO"}}, addr="10.160.34.37:2882", interrupt_id=task(1696922810486189,4303357292)) [2023-10-11 13:18:39.846653] INFO [SQL.ENG] broadcast_dfo (ob_px_interruption.cpp:74) [112273][T1002_L0_G0][T1002][YB420AA02224-0006075830CFA032-0-0] [lt=6] success to send interrupt message(int_code={code:-4662, info:{msg:"tid:112273,from:"10.160.34.36:2882",PX ABORT DFO"}}, addr="10.160.34.36:2882", interrupt_id=task(1696922810486189,4303357292)) mysql> SELECT count(1) from -> (select AX.OC_CARR ,AX.OC_FLT_NBR ,BX.RTE_IND_NEW,BX.LEG_SEQ ,BX.DPT_AIRPT ,BX.ARRV_AIRPT ,AX.RTE -> FROM ( -> SELECT A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW ,A.LEG_SEQ ,A.DPT_AIRPT ,A.ARRV_AIRPT -> ,GROUP_CONCAT( A.V ORDER BY A.LEG_SEQ SEPARATOR '-') AS RTE -> FROM DEV_TMP.RTE_TEMP_2 A -> GROUP BY A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW -> )AX -> LEFT JOIN -> (SELECT * -> ,MAX(LEG_SEQ) OVER(PARTITION BY OC_CARR ,OC_FLT_NBR ,RTE_IND_NEW ORDER BY LEG_SEQ DESC) IDMAX -> FROM DEV_TMP.RTE_TEMP_2 -> )BX -> ON AX.OC_CARR=BX.OC_CARR -> AND AX.OC_FLT_NBR=BX.OC_FLT_NBR -> AND AX.RTE_IND_NEW=BX.RTE_IND_NEW -> WHERE BX.LEG_SEQ=BX.IDMAX ) -> ; +----------+ | count(1) | +----------+ | 5521470 | +----------+ 1 row in set (1 min 41.11 sec) +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ================================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | -------------------------------------------------------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |5215278 | | | |1 |└─PX COORDINATOR | |1 |5215278 | | | |2 | └─EXCHANGE OUT DISTR |:EX10000 |1 |5215278 | | | |3 | └─MERGE GROUP BY | |1 |5215278 | | | |4 | └─NESTED-LOOP JOIN | |1 |5215278 | | | |5 | ├─SUBPLAN SCAN |BX |1 |5205667 | | | |6 | │ └─PX PARTITION ITERATOR | |6018477 |5138710 | | | |7 | │ └─WINDOW FUNCTION | |6018477 |5138710 | | | |8 | │ └─TABLE FULL SCAN |rte_temp_2(index_name,Reverse)|6018477 |360230 | | | |9 | └─SUBPLAN SCAN |AX |1 |9610 | | | |10| └─DISTRIBUTED TABLE RANGE SCAN|A(index_name) |1 |9610 | | | ================================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f59fc674490))(0x7f6805e756b0)]), filter(nil), rowset=256 | | group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*)(0x7f59fc674490))(0x7f6805e756b0)]) | | 1 - output([T_FUN_COUNT(*)(0x7f59fc674490)]), filter(nil), rowset=256 | | 2 - output([T_FUN_COUNT(*)(0x7f59fc674490)]), filter(nil), rowset=256 | | dop=1 | | 3 - output([T_FUN_COUNT(*)(0x7f59fc674490)]), filter(nil), rowset=256 | | group(nil), agg_func([T_FUN_COUNT(*)(0x7f59fc674490)]) | | 4 - output(nil), filter(nil), rowset=256 | | conds(nil), nl_params_([BX.RTE_IND_NEW(0x7f59fc6704c0)(:3)], [BX.OC_CARR(0x7f59fc66da60)(:4)], [BX.OC_FLT_NBR(0x7f59fc66ef90)(:5)]), use_batch=false | | 5 - output([BX.OC_CARR(0x7f59fc66da60)], [BX.OC_FLT_NBR(0x7f59fc66ef90)], [BX.RTE_IND_NEW(0x7f59fc6704c0)]), filter([BX.LEG_SEQ(0x7f59fc671710) = BX.IDMAX(0x7f59fc6719f0)(0x7f59fc671010)]), rowset=256 | | access([BX.OC_CARR(0x7f59fc66da60)], [BX.OC_FLT_NBR(0x7f59fc66ef90)], [BX.RTE_IND_NEW(0x7f59fc6704c0)], [BX.LEG_SEQ(0x7f59fc671710)], [BX.IDMAX(0x7f59fc6719f0)]) | | 6 - output([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)], [rte_temp_2.LEG_SEQ(0x7f59fc6660c0)], | | [T_FUN_MAX(rte_temp_2.LEG_SEQ(0x7f59fc6660c0))(0x7f59fc667300)(0x7f59fc666ee0)]), filter(nil), rowset=256 | | partition wise, force partition granule | | 7 - output([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)], [rte_temp_2.LEG_SEQ(0x7f59fc6660c0)], | | [T_FUN_MAX(rte_temp_2.LEG_SEQ(0x7f59fc6660c0))(0x7f59fc667300)(0x7f59fc666ee0)]), filter(nil), rowset=256 | | win_expr(T_FUN_MAX(rte_temp_2.LEG_SEQ(0x7f59fc6660c0))(0x7f59fc667300)(0x7f59fc666ee0)), partition_by([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], | | [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)]), order_by([rte_temp_2.LEG_SEQ(0x7f59fc6660c0), DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(CURRENT | | ROW) | | 8 - output([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)], [rte_temp_2.LEG_SEQ(0x7f59fc6660c0)]), filter(nil), rowset=256 | | access([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)], [rte_temp_2.LEG_SEQ(0x7f59fc6660c0)]), partitions(p[0-479]) | | is_index_back=false, is_global_index=false, | | range_key([rte_temp_2.OC_CARR(0x7f59fc665540)], [rte_temp_2.OC_FLT_NBR(0x7f59fc665810)], [rte_temp_2.RTE_IND_NEW(0x7f59fc665df0)], [rte_temp_2.LEG_SEQ(0x7f59fc6660c0)], | | [rte_temp_2.__pk_increment(0x7f59fc675870)]), range(MIN,MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX,MAX)always true | | 9 - output(nil), filter(nil), rowset=256 | | access(nil) | | 10 - output([A.OC_CARR(0x7f59fc64c210)], [A.OC_FLT_NBR(0x7f59fc64c4e0)], [A.RTE_IND_NEW(0x7f59fc64d120)]), filter(nil), rowset=256 | | access([A.OC_CARR(0x7f59fc64c210)], [A.OC_FLT_NBR(0x7f59fc64c4e0)], [A.RTE_IND_NEW(0x7f59fc64d120)]), partitions(p[0-479]) | | limit(1), offset(nil), is_index_back=false, is_global_index=false, | | range_key([A.OC_CARR(0x7f59fc64c210)], [A.OC_FLT_NBR(0x7f59fc64c4e0)], [A.RTE_IND_NEW(0x7f59fc64d120)], [A.LEG_SEQ(0x7f6bd1dca2d0)], [A.__pk_increment(0x7f59fc675420)]), | | range(MIN,MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX,MAX)always true, | | range_cond([A.RTE_IND_NEW(0x7f59fc64d120) = :3(0x7f6bd1cefe20)], [A.OC_CARR(0x7f59fc64c210) = :4(0x7f6bd1cf0650)], [A.OC_FLT_NBR(0x7f59fc64c4e0) = | | :5(0x7f6bd1cf0e80)]) | | Used Hint: | | ------------------------------------- | | /*+ | | | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 > SEL$EA493D33 > SEL$0EE93CA6 > SEL$D3A5388B | | stmt_id:2, SEL$2 > SEL$89B3808A | | stmt_id:3, SEL$3 > SEL$3E84DA65 > SEL$50E3642C | | stmt_id:4, SEL$4 > SEL$3656C1FA | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | GBY_PUSHDOWN(@"SEL$D3A5388B") | | LEADING(@"SEL$D3A5388B" ("BX"@"SEL$2" "AX"@"SEL$2")) | | USE_NL(@"SEL$D3A5388B" "AX"@"SEL$2") | | PQ_DISTRIBUTE(@"SEL$D3A5388B" "AX"@"SEL$2" NONE ALL) | | PQ_DISTRIBUTE_WINDOW(@"SEL$3656C1FA" (0) NONE) | | INDEX(@"SEL$3656C1FA" "dev_tmp"."rte_temp_2"@"SEL$4" "index_name") | | INDEX(@"SEL$50E3642C" "A"@"SEL$3" "index_name") | | USE_DAS(@"SEL$50E3642C" "A"@"SEL$3") | | SIMPLIFY_GROUP_BY(@"SEL$1") | | PROJECT_PRUNE(@"SEL$2") | | PROJECT_PRUNE(@"SEL$3") | | PROJECT_PRUNE(@"SEL$4") | | MERGE(@"SEL$89B3808A" > "SEL$EA493D33") | | OUTER_TO_INNER(@"SEL$0EE93CA6") | | SIMPLIFY_GROUP_BY(@"SEL$3E84DA65") | | OPTIMIZER_FEATURES_ENABLE('4.0.0.0') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | rte_temp_2: | | table_rows:6018477 | | physical_range_rows:6018477 | | logical_range_rows:6018477 | | index_back_rows:0 | | output_rows:6018477 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[index_name, rte_temp_2] | | pruned_index_name:[rte_temp_2] | | stats version:1696912418744268 | | dynamic sampling level:0 | | A: | | table_rows:6018477 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:DAS DOP | | avaiable_index_name:[index_name, rte_temp_2] | | pruned_index_name:[rte_temp_2] | | stats version:1696912418744268 | | dynamic sampling level:0 | | Plan Type: | | DISTRIBUTED | | Note: | | Degree of Parallelisim is 1 because of table property | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 114 rows in set (0.02 sec) YB420AA02224-00060758381FA034-0-0 mysql> mysql> * from oceanbase.gv$ob_sql_audit where trace_id='YB420AA02224-0006075830CFA032-0-0' and SVR_IP='10.160.34.36' and svr_port=2882 and sqlselect * from gv$ob_processlist where state='active'; +--------------+----------+----------+------------+-------------+--------------------+-----------+--------+---------+------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+-----------+-----------+------------+----------------------------------+-----------+-----------+------------+-----------------------------------+-----------------+--------+--------+-------------+ | SVR_IP | SVR_PORT | SQL_PORT | ID | USER | HOST | DB | TENANT | COMMAND | TIME | TOTAL_TIME | STATE | INFO | PROXY_SESSID | MASTER_SESSID | USER_CLIENT_IP | USER_HOST | RETRY_CNT | RETRY_INFO | SQL_ID | TRANS_ID | THREAD_ID | SSL_CIPHER | TRACE_ID | TRANS_STATE | ACTION | MODULE | CLIENT_INFO | +--------------+----------+----------+------------+-------------+--------------------+-----------+--------+---------+------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+-----------+-----------+------------+----------------------------------+-----------+-----------+------------+-----------------------------------+-----------------+--------+--------+-------------+ | 10.160.34.36 | 2882 | 2881 | 3221666838 | root | 10.160.34.36:21494 | oceanbase | tnt_nh | Query | 4 | 4 | ACTIVE | SELECT count(1) from (select AX.OC_CARR ,AX.OC_FLT_NBR ,BX.RTE_IND_NEW,BX.LEG_SEQ ,BX.DPT_AIRPT ,BX.ARRV_AIRPT ,AX.RTE FROM ( SELECT A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW ,A.LEG_SEQ ,A.DPT_AIRPT ,A.ARRV_AIRPT ,GROUP_CONCAT( A.V ORDER BY A.LEG_SEQ SEPARATOR '-') AS RTE FROM DEV_TMP.RTE_TEMP_2 A GROUP BY A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW )AX LEFT JOIN (SELECT * ,MAX(LEG_SEQ) OVER(PARTITION BY OC_CARR ,OC_FLT_NBR ,RTE_IND_NEW ORDER BY LEG_SEQ DESC) IDMAX FROM DEV_TMP.RTE_TEMP_2 )BX ON AX.OC_CARR=BX.OC_CARR AND AX.OC_FLT_NBR=BX.OC_FLT_NBR AND AX.RTE_IND_NEW=BX.RTE_IND_NEW WHERE BX.LEG_SEQ=BX.IDMAX ) | 765649474856092372 | NULL | 10.160.34.35 | % | 1 | -4023 | 7C6A755ECC4E724F3F96192B634F6B4C | 0 | 88622 | NULL | YB420AA02224-00060758283FA03E-0-0 | | | | | +--------------+----------+----------+------------+-------------+--------------------+-----------+--------+---------+------+------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+-----------+-----------+------------+----------------------------------+-----------+-----------+------------+-----------------------------------+-----------------+--------+--------+-------------+ mysql> select * from oceanbase.gv$ob_sql_audit where trace_id='YB420AA02224-00060758283FA03E-0-0' and SVR_IP='10.160.34.36' and svr_port=2882 and sql_id='7C6A755ECC4E724F3F96192B634F6B4C' and ELAPSED_TIME >10000000; +--------------+----------+------------+-------------+-----------------------------------+------------+--------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------------------+---------+-----------------+--------+------+--------+------+-------+---------------+-------------+-------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+ | SVR_IP | SVR_PORT | REQUEST_ID | SQL_EXEC_ID | TRACE_ID | SID | CLIENT_IP | CLIENT_PORT | TENANT_ID | TENANT_NAME | EFFECTIVE_TENANT_ID | USER_ID | USER_NAME | USER_GROUP | USER_CLIENT_IP | DB_ID | DB_NAME | SQL_ID | QUERY_SQL | PLAN_ID | AFFECTED_ROWS | RETURN_ROWS | PARTITION_CNT | RET_CODE | QC_ID | DFO_ID | SQC_ID | WORKER_ID | EVENT | P1TEXT | P1 | P2TEXT | P2 | P3TEXT | P3 | LEVEL | WAIT_CLASS_ID | WAIT_CLASS# | WAIT_CLASS | STATE | WAIT_TIME_MICRO | TOTAL_WAIT_TIME_MICRO | TOTAL_WAITS | RPC_COUNT | PLAN_TYPE | IS_INNER_SQL | IS_EXECUTOR_RPC | IS_HIT_PLAN | REQUEST_TIME | ELAPSED_TIME | NET_TIME | NET_WAIT_TIME | QUEUE_TIME | DECODE_TIME | GET_PLAN_TIME | EXECUTE_TIME | APPLICATION_WAIT_TIME | CONCURRENCY_WAIT_TIME | USER_IO_WAIT_TIME | SCHEDULE_TIME | ROW_CACHE_HIT | BLOOM_FILTER_CACHE_HIT | BLOCK_CACHE_HIT | DISK_READS | RETRY_CNT | TABLE_SCAN | CONSISTENCY_LEVEL | MEMSTORE_READ_ROW_COUNT | SSSTORE_READ_ROW_COUNT | DATA_BLOCK_READ_CNT | DATA_BLOCK_CACHE_HIT | INDEX_BLOCK_READ_CNT | INDEX_BLOCK_CACHE_HIT | BLOCKSCAN_BLOCK_CNT | BLOCKSCAN_ROW_CNT | PUSHDOWN_STORAGE_FILTER_ROW_CNT | REQUEST_MEMORY_USED | EXPECTED_WORKER_COUNT | USED_WORKER_COUNT | SCHED_INFO | FUSE_ROW_CACHE_HIT | PS_CLIENT_STMT_ID | PS_INNER_STMT_ID | TX_ID | SNAPSHOT_VERSION | REQUEST_TYPE | IS_BATCHED_MULTI_STMT | OB_TRACE_INFO | PLAN_HASH | LOCK_FOR_READ_TIME | PARAMS_VALUE | RULE_NAME | PARTITION_HIT | TX_INTERNAL_ROUTING | TX_STATE_VERSION | +--------------+----------+------------+-------------+-----------------------------------+------------+--------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------------------+---------+-----------------+--------+------+--------+------+-------+---------------+-------------+-------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+ | 10.160.34.36 | 2882 | 75429927 | 96351136 | YB420AA02224-00060758283FA03E-0-0 | 3221691424 | 10.160.34.36 | 21494 | 1002 | | 1002 | 200001 | | 0 | 10.160.34.35 | 201001 | | 7C6A755ECC4E724F3F96192B634F6B4C | | 4411 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | | | 0 | | 0 | | 0 | 0 | 100 | 0 | OTHER | MAX_WAIT TIME ZERO | 0 | 0 | 0 | 0 | 3 | 0 | 1 | 1 | 1697003638082399 | 96913142 | 0 | 0 | 0 | 0 | 0 | 96913142 | 0 | 0 | 0 | 0 | 0 | 0 | 5508580 | 0 | 0 | 0 | -1 | 0 | 5752807 | 2754987 | 2754987 | 5507186 | 2753593 | 217 | 1312230 | 0 | 5023832 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 0 | 4 | 0 | NULL | 0 | 0 | | | 1 | 0 | 0 | | 10.160.34.36 | 2882 | 75429997 | 96317505 | YB420AA02224-00060758283FA03E-0-0 | 3221666838 | 10.160.34.36 | 21494 | 1002 | tnt_nh | 1002 | 200001 | root | 100 | 10.160.34.35 | 201001 | oceanbase | 7C6A755ECC4E724F3F96192B634F6B4C | SELECT count(1) from (select AX.OC_CARR ,AX.OC_FLT_NBR ,BX.RTE_IND_NEW,BX.LEG_SEQ ,BX.DPT_AIRPT ,BX.ARRV_AIRPT ,AX.RTE FROM ( SELECT A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW ,A.LEG_SEQ ,A.DPT_AIRPT ,A.ARRV_AIRPT ,GROUP_CONCAT( A.V ORDER BY A.LEG_SEQ SEPARATOR '-') AS RTE FROM DEV_TMP.RTE_TEMP_2 A GROUP BY A.OC_CARR ,A.OC_FLT_NBR ,A.RTE_IND_NEW )AX LEFT JOIN (SELECT * ,MAX(LEG_SEQ) OVER(PARTITION BY OC_CARR ,OC_FLT_NBR ,RTE_IND_NEW ORDER BY LEG_SEQ DESC) IDMAX FROM DEV_TMP.RTE_TEMP_2 )BX ON AX.OC_CARR=BX.OC_CARR AND AX.OC_FLT_NBR=BX.OC_FLT_NBR AND AX.RTE_IND_NEW=BX.RTE_IND_NEW WHERE BX.LEG_SEQ=BX.IDMAX ) | 4411 | 0 | 1 | 480 | 0 | 0 | 0 | 0 | 0 | px loop condition wait | address | 140154120264832 | | 0 | | 0 | 0 | 104 | 4 | CONCURRENCY | WAITED SHORT TIME | 4455 | 97645562 | 93255 | 0 | 3 | 0 | 0 | 1 | 1697003638073145 | 97702427 | 0 | 3 | 150 | 2 | 5536 | 97696556 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2526924 | 0 | 0 | NULL | 0 | -1 | -1 | 0 | 1697003638079028352 | 2 | 0 | NULL | 15528409201006426418 | 0 | | | 1 | 0 | 0 | +--------------+----------+------------+-------------+-----------------------------------+------------+--------------+-------------+-----------+-------------+---------------------+---------+-----------+------------+----------------+--------+-----------+----------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+------------------------+---------+-----------------+--------+------+--------+------+-------+---------------+-------------+-------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+----------------------+----------------------+-----------------------+---------------------+-------------------+---------------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+-------+---------------------+--------------+-----------------------+---------------+----------------------+--------------------+--------------+-----------+---------------+---------------------+------------------+ 2 rows in set (33.50 sec) mysql> select svr_ip,plan_depth,plan_line_id,operator,name,rows,cost,property from gv$ob_plan_cache_plan_explain where tenant_id=1002 and svr_ip='10.160.34.36' and svr_port=2882 and plan_id=4411 ; +--------------+------------+--------------+----------------------------+------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | svr_ip | plan_depth | plan_line_id | operator | name | rows | cost | property | +--------------+------------+--------------+----------------------------+------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 10.160.34.36 | 0 | 0 | PHY_SCALAR_AGGREGATE | NULL | 1 | 5215277 | NULL | | 10.160.34.36 | 1 | 1 | PHY_PX_FIFO_COORD | NULL | 1 | 5215277 | NULL | | 10.160.34.36 | 2 | 2 | PHY_PX_REDUCE_TRANSMIT | NULL | 1 | 5215277 | NULL | | 10.160.34.36 | 3 | 3 | PHY_MERGE_GROUP_BY | NULL | 1 | 5215277 | NULL | | 10.160.34.36 | 4 | 4 | PHY_NESTED_LOOP_JOIN | NULL | 1 | 5215277 | NULL | | 10.160.34.36 | 5 | 5 | PHY_SUBPLAN_SCAN | NULL | 1 | 5205666 | NULL | | 10.160.34.36 | 6 | 6 | PHY_GRANULE_ITERATOR | NULL | 6018477 | 5138709 | NULL | | 10.160.34.36 | 7 | 7 | PHY_WINDOW_FUNCTION | NULL | 6018477 | 5138709 | NULL | | 10.160.34.36 | 8 | 8 | PHY_TABLE_SCAN | rte_temp_2(index_name) | 6018477 | 360229 | table_rows:6018477, physical_range_rows:6018477, logical_range_rows:6018477, index_back_rows:0, output_rows:6018477, avaiable_index_name[index_name,rte_temp_2], pruned_index_name[rte_temp_2] | | 10.160.34.36 | 5 | 9 | PHY_SUBPLAN_SCAN | NULL | 1 | 9609 | NULL | | 10.160.34.36 | 6 | 10 | PHY_TABLE_SCAN | A(index_name) | 1 | 9609 | table_rows:6018477, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, avaiable_index_name[index_name,rte_temp_2], pruned_index_name[rte_temp_2] | +--------------+------------+--------------+----------------------------+------------------------+---------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 11 rows in set (0.00 sec) mysql> show trace; +----------------------------------------------+----------------------------+---------------+ | Operation | StartTime | ElapseTime | +----------------------------------------------+----------------------------+---------------+ | ob_proxy | 2023-10-11 14:07:15.365841 | 188099.088 ms | | ├── ob_proxy_partition_location_lookup | 2023-10-11 14:07:15.365911 | 0.140 ms | | ├── ob_proxy_server_process_req | 2023-10-11 14:07:15.366139 | 188098.117 ms | | └── com_query_process | 2023-10-11 14:07:15.369671 | 188094.752 ms | | └── mpquery_single_stmt | 2023-10-11 14:07:15.369702 | 188094.706 ms | | └── sql_execute | 2023-10-11 14:07:15.372260 | 188091.975 ms | | ├── response_result | 2023-10-11 14:07:15.372410 | 188091.500 ms | | │ └── px_schedule | 2023-10-11 14:10:23.463749 | 0.001 ms | | └── close | 2023-10-11 14:10:23.463925 | 0.111 ms | | └── end_transaction | 2023-10-11 14:10:23.464011 | 0.006 ms | +----------------------------------------------+----------------------------+---------------+ +---------------------------------------+ | version() | +---------------------------------------+ | 5.7.25-OceanBase_CE_ActionDB-v4.2.0.0 | +---------------------------------------+ -------------- mysql Ver 14.14 Distrib 5.7.24, for linux-glibc2.12 (x86_64) using EditLine wrapper Connection id: 2328302 Current database: oceanbase Current user: root@10.160.34.35 SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.6.25 OceanBase_CE 4.2.0.0 (r1-7e912e012b5f2036b79eaeecaaf59884efc184fd) (Built Sep 7 2023 10:01:13) Protocol version: 10 Connection: 10.160.34.36 via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 TCP port: 2883 Active --------------