为了更快的定位您的问题，请提供以下信息，谢谢
【详述】问题详细描述
同样一条SQL跑十次左右时间基本都在1s左右，但是偶尔会出现10s以上的问题. 用EXPLAIN ANALYZE分析发现其中ExecutionTime里的Network比较高, 不明白为什么.
【是否存算分离】
否
【StarRocks版本】
3.2.4
【集群规模】3FE +3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，32C/256G/万兆
【联系方式】微信: zhangheng311
有问题时候的EXPLAIN ANALYZE输出
mysql> EXPLAIN ANALYZE SELECT unit, zyid, name, ROUND(ddcca/100, 2) AS 充值金额 FROM ( SELECT date_trunc(‘day’,a.$part_date) AS unit, u.zyid , u.name , SUM( CASE WHEN ( (a.$part_event=‘action_currency’) ) THEN a.payamount ELSE NULL END ) AS ddcca FROM bi.v_event_1515 a LEFT JOIN bi.v_user_1515 u ON a.#account_id = u.#account_id WHERE ((a.$part_event=‘action_currency’) ) AND (a.$part_date BETWEEN ‘2024-06-04’ AND ‘2024-06-10’ ) GROUP BY date_trunc(‘day’,a.$part_date), u.zyid , u.name ) c WHERE ddcca IS NOT NULL AND ddcca != 0 LIMIT 1000;
±--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Explain String |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Summary |
| QueryId: 12c1f863-32c6-11ef-af68-00163e2c6d41 |
| Version: 3.2.4-613f0b5 |
| State: Finished |
| TotalTime: 15s363ms |
| ExecutionTime: 13s228ms [Scan: 10.503ms (0.08%), Network: 52s153ms (394.27%), ResultDeliverTime: 0ns (0.00%), ScheduleTime: 49.915ms (0.38%)] |
| CollectProfileTime: 93ms |
| FrontendProfileMergeTime: 4.958ms |
| QueryPeakMemoryUsage: 21.424 MB, QueryAllocatedMemoryUsage: 94.077 MB |
| Top Most Time-consuming Nodes: |
| 1. EXCHANGE (id=6) [SHUFFLE]: 26s217ms (50.17%) |
| 2. EXCHANGE (id=2) [SHUFFLE]: 25s958ms (49.67%) |
| 3. AGGREGATION (id=5) [serialize, update]: 44.019ms (0.08%) |
| 4. OLAP_SCAN (id=0) : 18.083ms (0.03%) |
| 5. HASH_JOIN (id=3) [BUCKET_SHUFFLE, RIGHT OUTER JOIN]: 9.230ms (0.02%) |
| 6. OLAP_SCAN (id=1) : 5.831ms (0.01%) |
| 7. EXCHANGE (id=9) [GATHER]: 5.112ms (0.01%) |
| 8. AGGREGATION (id=7) [finalize, merge]: 1.037ms (0.00%) |
| 9. RESULT_SINK: 133.589us (0.00%) |
| 10. PROJECT (id=4) : 48.174us (0.00%) |
| Top Most Memory-consuming Nodes: |
| 1. HASH_JOIN (id=3) [BUCKET_SHUFFLE, RIGHT OUTER JOIN]: 3.465 MB |
| 2. EXCHANGE (id=6) [SHUFFLE]: 3.150 MB |
| 3. AGGREGATION (id=5) [serialize, update]: 1.767 MB |
| 4. AGGREGATION (id=7) [finalize, merge]: 1.765 MB |
| 5. EXCHANGE (id=2) [SHUFFLE]: 1.694 MB |
| 6. EXCHANGE (id=9) [GATHER]: 165.609 KB |
| NonDefaultVariables: |
| enable_adaptive_sink_dop: false -> true |
| enable_async_profile: true -> false |
| enable_profile: false -> true |
| query_timeout: 300 -> 259200 |
| Fragment 0 |
| │ BackendNum: 1 |
| │ InstancePeakMemoryUsage: 605.008 KB, InstanceAllocatedMemoryUsage: 1.731 MB |
| │ PrepareTime: 493.651us |
| └──RESULT_SINK |
| │ TotalTime: 133.589us (0.00%) [CPUTime: 133.589us] |
| │ OutputRows: 1.000K (1000) |
| │ SinkType: MYSQL_PROTOCAL |
| └──EXCHANGE (id=9) [GATHER] |
| Estimates: [row: 1000, cpu: 0.00, memory: 0.00, network: 0.00, cost: 18212591.89] |
| TotalTime: 5.112ms (0.01%) [CPUTime: 258.527us, NetworkTime: 4.854ms] |
| OutputRows: 1.000K (1000) |
| PeakMemory: 165.609 KB, AllocatedMemory: 1.257 MB |
| |
| Fragment 1 |
| │ BackendNum: 3 |
| │ InstancePeakMemoryUsage: 9.414 MB, InstanceAllocatedMemoryUsage: 22.561 MB |
| │ PrepareTime: 1.104ms |
| └──DATA_STREAM_SINK (id=9) |
| │ PartitionType: UNPARTITIONED |
| └──PROJECT (id=8) |
| │ Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?] |
| │ TotalTime: 46.973us (0.00%) [CPUTime: 46.973us] |
| │ OutputRows: 1.972K (1972) |
| │ Expression: [363: zyid, 368: name, 374: date_trunc, round(376: sum / 100.0, 2)] |
| └──AGGREGATION (id=7) [finalize, merge] |
| │ Estimates: [row: 1, cpu: 29.85, memory: 37.85, network: 0.00, cost: 18212532.19] |
| │ TotalTime: 1.037ms (0.00%) [CPUTime: 1.037ms] |
| │ OutputRows: 1.972K (1972) |
| │ PeakMemory: 1.765 MB, AllocatedMemory: 8.253 MB |
| │ AggExprs: [sum(376: sum)] |
| │ GroupingExprs: [374: date_trunc, 363: zyid, 368: name] |
| │ SubordinateOperators: |
| │ CHUNK_ACCUMULATE |
| └──EXCHANGE (id=6) [SHUFFLE] |
| Estimates: [row: 1, cpu: 2.99, memory: 0.00, network: 2.99, cost: 18212441.56] |
| TotalTime: 26s217ms (50.17%) [CPUTime: 25.991ms, NetworkTime: 26s191ms] |
| OutputRows: 2.840K (2840) |
| PeakMemory: 3.150 MB, AllocatedMemory: 16.808 MB |
| Detail Timers: |
| OverallTime: 8s653ms [min=14.742ms, max=13s33ms] |
| WaitTime: 8s636ms [min=8.323ms, max=13s21ms] |
| |
| Fragment 2 |
| │ BackendNum: 3 |
| │ InstancePeakMemoryUsage: 9.968 MB, InstanceAllocatedMemoryUsage: 57.107 MB |
| │ PrepareTime: 1.735ms |
| └──DATA_STREAM_SINK (id=6) |
| │ PartitionType: HASH_PARTITIONED |
| │ PartitionExprs: [374: date_trunc, 363: zyid, 368: name] |
| └──AGGREGATION (id=5) [serialize, update] |
| │ Estimates: [row: 1, cpu: 9.60, memory: 2.99, network: 0.00, cost: 18212435.59] |
| │ TotalTime: 44.019ms (0.08%) [CPUTime: 44.019ms] |
| │ OutputRows: 2.990K (2990) |
| │ PeakMemory: 1.767 MB, AllocatedMemory: 5.179 MB |
| │ AggExprs: [sum(375: case)] |
| │ GroupingExprs: [374: date_trunc, 363: zyid, 368: name] |
| │ SubordinateOperators: |
| │ LOCAL_EXCHANGE [Passthrough] |
| └──PROJECT (id=4) |
| │ Estimates: [row: ?, cpu: ?, memory: ?, network: ?, cost: ?] |
| │ TotalTime: 48.174us (0.00%) [CPUTime: 48.174us] |
| │ OutputRows: 5.789K (5789) |
| │ Expression: [363: zyid, 368: name, …] |
| └──HASH_JOIN (id=3) [BUCKET_SHUFFLE, RIGHT OUTER JOIN] |
| │ Estimates: [row: 1, cpu: 18212293.69, memory: 50.72, network: 0.00, cost: 18212424.82] |
| │ TotalTime: 9.230ms (0.02%) [CPUTime: 9.230ms] |
| │ OutputRows: 5.789K (5789) |
| │ PeakMemory: 3.465 MB, AllocatedMemory: 9.408 MB |
| │ BuildTime: 8.978ms |
| │ ProbeTime: 117.026us |
| │ EqJoinConjuncts: [355: #account_id = 6: #account_id] |
| │ SubordinateOperators: |
| │ CHUNK_ACCUMULATE |
| │ LOCAL_EXCHANGE [Partition(BUCKET_SHUFFLE_HASH_PARTITIONED)] |
| ├── OLAP_SCAN (id=0) |
| │ Estimates: [row: 812301, cpu: 18212147.00, memory: 0.00, network: 0.00, cost: 9106073.50] |
| │ TotalTime: 18.083ms (0.03%) [CPUTime: 10.842ms, ScanTime: 7.240ms] |
| │ OutputRows: 1.482K (1482) |
| │ RuntimeFilter: 1.482K (1482) -> 1.482K (1482) (0.00%) |
| │ Table: : v_user_1515 |
| │ SubordinateOperators: |
| │ CHUNK_ACCUMULATE |
| │ NOOP |
| │ OLAP_SCAN_PREPARE |
| └── EXCHANGE (id=2) [SHUFFLE] |
| Estimates: [row: 1, cpu: 50.72, memory: 0.00, network: 50.72, cost: 126.81] |
| TotalTime: 25s958ms (49.67%) [CPUTime: 1.096ms, NetworkTime: 25s957ms] |
| OutputRows: 5.789K (5789) |
| PeakMemory: 1.694 MB, AllocatedMemory: 5.554 MB |
| Detail Timers: |
| OverallTime: 6s503ms [min=23.297ms, max=12s984ms] |
| WaitTime: 6s493ms [min=17.008ms, max=12s970ms] |
| |
| Fragment 3 |
| │ BackendNum: 2 |
| │ InstancePeakMemoryUsage: 1.225 MB, InstanceAllocatedMemoryUsage: 12.677 MB |
| │ PrepareTime: 590.695us |
| └──DATA_STREAM_SINK (id=2) |
| │ PartitionType: BUCKET_SHUFFLE_HASH_PARTITIONED |
| │ PartitionExprs: [6: #account_id] |
| └──OLAP_SCAN (id=1) |
| Estimates: [row: 1, cpu: 50.72, memory: 0.00, network: 0.00, cost: 25.36] |
| TotalTime: 5.831ms (0.01%) [CPUTime: 2.569ms, ScanTime: 3.262ms] |
| OutputRows: 5.789K (5789) |
| Table: : v_event_1515 |
| SubordinateOperators: |
| CHUNK_ACCUMULATE |
| NOOP |
| OLAP_SCAN_PREPARE |
| |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------+
143 rows in set (15.38 sec)