【详述】starrocks 2.5 有个sql查询执行时长不稳定，有时耗时特别长，CoordDeliverExec: 53190ms
【背景】可以稳定复现
【业务影响】
【是否存算分离】否
【StarRocks版本】starrocks 2.5
【集群规模】例如：3fe + 3be（fe与be混部）
【机器信息】48cores 256GB
【联系方式】
【附件】
Query:
Summary:
- Query ID: c57bb7ce-b2bc-11ef-be3e-083a3842f7a4
- Start Time: 2024-12-05 11:55:30
- End Time: 2024-12-05 11:56:25
- Total: 54s498ms
- Query Type: Query
- Query State: EOF
- StarRocks Version: 2.5.14-e4ca4dd
- User: tutor
- Default Db: tutor
- Sql Statement: select taskId ,userId ,ldap ,firstDistributedTime ,phase ,orderId ,paidFee ,lessonMarkId ,orderPaidTp ,order_paid_dt ,refund_dt ,first_assign_dt ,callBridgeTime ,keyFrom ,hostAppProductId ,less_subject_id ,dataSourceId ,followBizId ,case when is_renewal=1 and phase=3 and followBizId=15 and less_subject_id not in (204,206,207) and grade_type=‘xiaoxue’ and ( ( first_assign_dt in (‘2024-10-25’,‘2024-10-26’,‘2024-11-02’) and (keyFrom not like ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp%’ and keyFrom not like ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-sytc%’ and keyFrom not like ‘yfd-mkt-xiaoxue-7073-cta-doduo-x%’ and keyFrom not like ‘yfd-mkt-xiaoxue-xueke1-cta-duanxin%’ and keyFrom not like ‘yfd-mkt-xiaoxue-7073-cta%’ and keyFrom not like ‘yfd-mkt-xiaoxue-xueke1-cta%’ and keyFrom not like ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-1yuan%’) ) or ( first_assign_dt in (‘2024-10-29’,‘2024-10-30’) and (keyFrom not like ‘yfd-mkt-xiaoxue-xueke3-cta%’ and keyFrom not like ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-3yuan%’ and keyFrom not like ‘yfd-mkt-xiaoxue-xueke3-cta-duanxin%’) ) or ( first_assign_dt in (‘2024-10-31’,‘2024-11-01’) and (keyFrom not like ‘yfd-mkt-xiaoxue-xueke9-cta%’ and keyFrom not like ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-9yuan%’ and keyFrom not like ‘yfd-mkt-xiaoxue-xueke9-cta-duanxin%’) ) ) then 0 when is_renewal=1 and phase=3 and followBizId=15 and dataSourceId<<6 and order_paid_dt<=‘2024-11-15’ and grade_type=‘xiaoxue’ and ( keyFrom not rlike ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp’ and keyFrom not rlike ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-sytc’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-7073-cta-doduo-x’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-xueke1-cta’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-xueke1-cta-duanxin’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-7073-cta’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-yycta-cta’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-siweitx-cta’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-swcta-cta’ and (hostAppProductId not in (‘3000006’,‘28000006’,‘28000005’,‘378’,‘391’,‘328’,‘325’,‘341’,‘331’,‘321’,‘311’,‘301’,‘28000006’,‘28000005’,‘28000004’,‘28000003’,‘28000002’,‘28000001’) or hostAppProductId=’’ or hostAppProductId is null) ) then 0 when is_renewal=1 and phase=3 and followBizId=15 and dataSourceId=6 and order_paid_dt<=‘2024-11-15’ and grade_type=‘xiaoxue’ and (keyFrom not rlike ‘yfd-mkt-xiaoxue-xueke9-cta’ and keyFrom not rlike ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-9yuan’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-xueke9-cta-duanxin’) then 0 else is_renewal end as is_renewal ,case when is_renewal=1 and followBizId=15 and phase=3 and less_subject_id not in (204,206,207) and grade_type=‘xiaoxue’ and ( ( first_assign_dt in (‘2024-10-25’,‘2024-10-26’,‘2024-11-02’) and keyFrom not like ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp%’ and keyFrom not like ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-sytc%’ and keyFrom not like ‘yfd-mkt-xiaoxue-7073-cta-doduo-x%’ and keyFrom not like ‘yfd-mkt-xiaoxue-xueke1-cta-duanxin%’ and keyFrom not like ‘yfd-mkt-xiaoxue-7073-cta%’ and keyFrom not like ‘yfd-mkt-xiaoxue-xueke1-cta%’ and keyFrom not like ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-1yuan%’ ) or ( first_assign_dt in (‘2024-10-29’,‘2024-10-30’) and (keyFrom not like ‘yfd-mkt-xiaoxue-xueke3-cta%’ and keyFrom not like ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-3yuan%’ and keyFrom not like ‘yfd-mkt-xiaoxue-xueke3-cta-duanxin%’) ) or ( first_assign_dt in (‘2024-10-31’,‘2024-11-01’) and (keyFrom not like ‘yfd-mkt-xiaoxue-xueke9-cta%’ and keyFrom not like ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-9yuan%’ and keyFrom not like ‘yfd-mkt-xiaoxue-xueke9-cta-duanxin%’) ) ) then ‘10.25-11.2，测试期间剔除非指定链接成单’ when is_renewal=1 and followBizId=15 and phase=3 and dataSourceId<<6 and order_paid_dt<=‘2024-11-15’ and grade_type=‘xiaoxue’ and ( keyFrom not rlike ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp’ and keyFrom not rlike ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-sytc’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-7073-cta-doduo-x’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-xueke1-cta’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-xueke1-cta-duanxin’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-7073-cta’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-yycta-cta’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-siweitx-cta’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-swcta-cta’ and (hostAppProductId not in (‘3000006’,‘28000006’,‘28000005’,‘378’,‘391’,‘328’,‘325’,‘341’,‘331’,‘321’,‘311’,‘301’,‘28000006’,‘28000005’,‘28000004’,‘28000003’,‘28000002’,‘28000001’) or hostAppProductId=’’ or hostAppProductId is null) ) then ‘11.15起，未按照指定路径成单’ when is_renewal=1 and followBizId=15 and phase=3 and dataSourceId=6 and order_paid_dt<=‘2024-11-15’ and grade_type=‘xiaoxue’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-xueke9-cta’ and keyFrom not rlike ‘yfd-llc-xiaoxue-xueke-liuliangchi-miniapp-9yuan’ and keyFrom not rlike ‘yfd-mkt-xiaoxue-xueke9-cta-duanxin’ then ‘11.15起，测试团队未转化9元班课’ else reason end as reason from ( select t1.task_id as taskId ,t1.userId ,t1.distributedLdap as ldap ,t1.firstDistributedTime ,t1.phase ,t2.order_id as orderId ,t2.orderItemId ,t2.order_paid_tp as orderPaidTp ,t2.paid_fee as paidFee ,t2.lessonMarkId ,t2.order_paid_dt ,t2.refund_dt ,t1.first_assign_dt ,t3.callBridgeTime ,t2.less_subject_id ,t2.keyFrom ,t1.dataSourceId ,t4.hostAppProductId ,t2.grade_type ,t5.followBizId ,t2.flatted_category ,case when ( ( t1.phase=3 and t5.followBizId=15 and t2.grade_type in (‘xiaoxue’,‘chuzhong’) and ( t2.lessonMarkId in (15,302,342,518,514,53,390,35) or (t2.lessonMarkId=608 and t2.order_paid_dt<=‘2024-09-26’) or (t2.lessonMarkId=609 and t2.order_paid_dt<=‘2024-09-27’) or (t2.lessonMarkId=582 and t2.order_paid_dt<=‘2024-11-19’) or (t2.lessonMarkId=653 and t2.order_paid_dt<=‘2024-11-21’) or (t2.lessonMarkId=651 and t2.order_paid_dt<=‘2024-11-19’) ) and t2.order_paid_dt<=‘2024-09-01’ ) or ( t1.phase=1 and t5.followBizId=15 and (t2.grade_ids=‘6’ or t2.grade_type in (‘chuzhong’,‘gaozhong’)) and ( t2.lessonMarkId in (182,506,390,188,15,53,35,505) or (t2.lessonMarkId=651 and t2.order_paid_dt<=‘2024-11-19’) ) and t2.order_paid_dt<=‘2024-10-20’ ) or ( t1.phase=3 and t5.followBizId=16 and t2.grade_type in (‘xiaoxue’,‘chuzhong’) and t2.lessonMarkId in (15,302,342,518,514,35,390,53,582,653,651) ) or ( t1.phase=1 and t5.followBizId=16 and (t2.grade_ids=‘6’ or t2.grade_type in (‘chuzhong’,‘gaozhong’)) and t2.flatted_category in (1,2) ) ) and (t2.order_paid_tp<t3.callBridgeTime and t1.firstDistributedTime<=t3.callBridgeTime) and (t2.refund_tp = 0 or t2.refund_dt < date_add(t2.order_paid_dt,29)) then 1 else 0 end as is_renewal ,case when (t1.phase=3 and t5.followBizId=15 and t2.grade_type in (‘xiaoxue’,‘chuzhong’) and t2.lessonMarkId not in (15,302,342,518,514,53,390,35,608,609,582,653,651) and t2.order_paid_dt<=‘2024-09-01’) or (t1.phase=1 and t5.followBizId=15 and (t2.grade_ids=‘6’ or t2.grade_type in (‘chuzhong’,‘gaozhong’)) and t2.lessonMarkId not in (182,506,390,188,15,53,35,505,651) and t2.order_paid_dt<=‘2024-10-20’) or (t1.phase=3 and t5.followBizId=16 and t2.grade_type in (‘xiaoxue’,‘chuzhong’) and t2.lessonMarkId not in (15,302,342,518,514,35,390,53,582,653,651)) then ‘不符合转化班课范围’ when ( t1.phase=3 and t5.followBizId=15 and t2.grade_type in (‘xiaoxue’,‘chuzhong’) and ( (t2.lessonMarkId not in (15,302,342,518,514,53,390,35) and t2.order_paid_dt<=‘2024-09-01’) and( (t2.order_paid_dt<‘2024-09-26’ and t2.lessonMarkId=608 ) or (t2.order_paid_dt<‘2024-09-27’ and t2.lessonMarkId=609 ) or (t2.order_paid_dt<‘2024-11-19’ and t2.lessonMarkId=582 ) or (t2.order_paid_dt<‘2024-11-21’ and t2.lessonMarkId=653 ) or (t2.order_paid_dt<‘2024-11-19’ and t2.lessonMarkId=651 ) ) ) ) or ( t1.phase=1 and t5.followBizId=15 and (t2.grade_ids=‘6’ or t2.grade_type in (‘chuzhong’,‘gaozhong’)) and ( (t2.lessonMarkId not in (182,506,390,188,15,53,35,505) and t2.order_paid_dt<=‘2024-10-20’) and (t2.lessonMarkId=651 and t2.order_paid_dt<‘2024-11-19’) ) ) or ( t1.phase=3 and t5.followBizId=16 and t2.grade_type in (‘xiaoxue’,‘chuzhong’) and t2.lessonMarkId not in (15,302,342,518,514,35,390,53,582,653,651) ) then ‘不符合转化班课范围’ when t2.order_paid_tp<t3.callBridgeTime then ‘未触达’ when t2.refund_dt <= date_add(t2.order_paid_dt,29) and t2.refund_tp<<0 then ‘退款不算转化’ else ‘’ end as reason from ( select id as task_id ,userId ,phase ,dataSourceId ,leadsId ,distributedLdap ,firstDistributedTime ,from_unixtime(cast(firstDistributedTime/1000 as int),‘yyyy-MM-dd’) as first_assign_dt ,status ,finalStatusTime ,case when finalStatusTime=0 then unix_timestamp(date_add(from_unixtime(cast(firstDistributedTime/1000 as int),‘yyyy-MM-dd’),7))*1000 else finalStatusTime end as final_status_time_new from tutor_cta_promotion__task where phase in (1,3) and firstDistributedTime<0 ) t1 left join ( select orderId as order_id, userId as user_id, lessonId as less_id, subjectIds as less_subject_ids, subjectId as less_subject_id, gradeIds as grade_ids, gradeType as grade_type, orderPayedTime as order_paid_tp, from_unixtime(cast(orderPayedTime/1000 as int),‘yyyy-MM-dd’) as order_paid_dt, itemRefundedTime as refund_tp, from_unixtime(cast(itemRefundedTime/1000 as int),‘yyyy-MM-dd’) as refund_dt, paidFee as paid_fee, refundFee as refund_fee, flattedCategory as flatted_category, multiGradeType, orderItemId, semesterId, rootOrderItemId, lessonOrderTeamId, lessonMarkId, lower(keyFrom) as keyFrom from flink_rw_dwd_tutor_user_system_promotion_sort_s_da_v9 where flattedCategory in (1,2,6) and internal = 0 and semesterId <= 138 and skipOrderId = 0 and innerUser = 0 and orderPayedTime < 0 and isTransfer = 0 and subjectId << 201 and orderSource not in (12, 22, 19) and tutorOrderType<<1 ) t2 on t1.userId=t2.user_id join ( select taskId,min(callBridgeTime) as callBridgeTime from tutor_cta_promotion__call_record where case when (callLdap=‘system’ or callLdap=’’) and callType=1 then 0 else 1 end = 1 and callStatus=3 group by taskId ) t3 on t1.task_id=t3.taskId left join ( select outId ,hostAppProductId from ods_tutor_bolt_trade_t_order_da where hostAppProductId is not null and hostAppProductId<<’’ and hostAppProductId<<‘0’ ) t4 on t2.order_id=t4.outId join ( select id,followBizId from tutor_cta_promotion__data_source ) t5 on t1.dataSourceId=t5.id where ( (t1.firstDistributedTime<t2.order_paid_tp and t2.order_paid_dt<=date_add(t1.first_assign_dt,6) and t1.first_assign_dt<=‘2024-11-10’) or (t1.firstDistributedTime<t2.order_paid_tp and t2.order_paid_tp<final_status_time_new and t1.first_assign_dt<=‘2024-11-11’) ) and ( (t1.phase=3 and t2.grade_type in (‘xiaoxue’,‘chuzhong’)) or ( t1.phase=1 and (t2.grade_ids=‘6’ or t2.grade_type in (‘chuzhong’,‘gaozhong’))) ) and t5.followBizId in (15,16) ) ini；
- QueryCpuCost: 16s961ms
- QueryMemCost: 677.732MB
- Variables: parallel_fragment_exec_instance_num=1,max_parallel_scan_instance_num=-1,pipeline_dop=0,enable_adaptive_sink_dop=false,resource_group=rg_tutor
- Collect Profile Time: 1ms
Planner:
- Analyzer: 1ms / 1
- CoordDeliverExec: 53190ms / 1
- CoordPrepareExec: 0ms / 1
- ExecPlanBuild: 1ms / 1
- Optimizer: 78ms / 1
- Total: 86ms / 1
- Transformer: 5ms / 1
Optimizer:
- CostBaseOptimize: 3ms / 1
- PhysicalRewrite: 71ms / 1
- PlanValidate: 0ms / 1
- RuleBaseOptimize: 3ms / 1
- preprocessMvs: 0ms / 1
profile如下：profile (185.7 KB)