数仓实时场景下表行数估算不准确引起的的性能瓶颈问题案例
1.数仓实践丨主动预防-DWS关键工具安装确认2.一条SQL如何被MySQL架构中的各个组件操作执行的?3.GaussDB(DWS)网络流控与管控效果4.GaussDB(DWS)字符串处理函数返回错误结果集排查5.从缓存的本质说起,说服技术大佬用Redis6.这年头怕数据泄露?全密态数据库:无所谓,我会出手7.华为云新一代分布式数据库GaussDB,给世界一个更优选择8.GaussDB技术解读丨高级压缩9.掌数科技携手华为云GaussDB,助力金融科技创新,联合打造行业标杆10.一文带你全面了解openGemini11.GaussDB(for Redis)多租户:读写权限控制和数据库隔离的完美融合12.5分钟迁移关系型数据库到图数据库13.数仓现网案例丨超大结果集接收异常14.DWS轻量化更新黑科技:宽表加工优化15.数据库行业需要什么样的人才?高校老师这样说16.数仓性能优化:倾斜优化-表达式计算倾斜的hint优化17.GaussDB技术解读系列之SQL Audit,面向应用开发的SQL审核工具18.带你认识数仓的监控系统TopSQL19.带你走进数仓大集群内幕丨详解关于作业hang及残留问题定位20.实时入库不用愁,HStore帮分忧21.openGauss数据库在CentOS上的安装实践22.揭秘华为云GaussDB(for Redis)丨大key治理23.GaussDB(DWS)函数不同写法引发的结果差异24.数仓中典型的几种不下推语句整改案例25.GaussDB技术解读系列之应用无损透明(ALT)26.华为云GaussDB(for Influx)单机版上线,企业降本增效利器来了27.数仓备份经验分享丨详解roach备份原理及问题处理套路28.中国云数据仓库,双第一!29.华为云GaussDB打造最可信的数据库,给世界一个更优选择30.GaussDB技术解读系列:高级压缩之OLTP表压缩31.十年磨一剑的华为云GES,高明在哪32.使用DWS集群,用户被锁定如何解锁33.GaussDB技术解读系列:高安全之密态等值34.GaussDB技术解读:应用无损透明(ALT)35.数仓资源管控理论已掌握,是时候实战了36.row_number函数的不稳定性37.GaussDB技术解读丨数据库迁移创新实践38.聊聊GaussDB AP是如何执行SQL的39.Navicat 携手华为云GaussDB,联合打造便捷高效的数据库开发和建模工具方案40.GaussDB技术解读系列丨运维自动驾驶探索41.一次性全讲透GaussDB(DWS)锁的问题42.GaussDB(DWS)性能调优:Sort+Groupagg聚集引起的性能瓶颈案例43.多主架构:VLDB技术论文《Taurus MM: bringing multi-master to the cloud》解读44.GaussDB(for Redis)游戏实践:玩家下线行为上报45.一文详解数据仓库的物理细粒度备份恢复46.华为云HBase冷热分离最佳实践47.四问复合索引,让你的数据查询速度飞起48.GaussDB(DWS)案例丨MERGE场景下语句不下推引起的性能瓶颈问题49.如何强制SQL走性能更优的hash join50.如何使用GaussDB(DWS)的本地临时表进行数据处理51.华为云GaussDB亮相金融业数据库技术大会52.2个数仓中不等值关联优化案例
53.数仓实时场景下表行数估算不准确引起的的性能瓶颈问题案例
54.详解GuassDB数据库权限命令:GRANT和REVOKE55.DWS临时内存不可用报错: memory temporarily unavailable56.华为云GaussDB城市沙龙活动走进安徽,助力金融行业数字化转型57.理论+应用,带你了解数据库资源池58.人人用数不用愁,动态数据脱敏为您解忧59.实例讲解数据库的数据去重60.数仓实践丨表扫描时过滤行数过多引起的性能瓶颈问题61.实例详解构建数仓中的行列转换62.Proxy下的Prepare透传,让GaussDB(for MySQL)更稳固,性能更卓越63.浅析KV存储之长尾时延解决办法64.实例讲解数据库的定义重载函数65.详解数据库SQL中的三个语句:DROP、TRUNCATE 、DELETE66.华为云GaussDB助力工商银行、华夏银行斩获“十佳卓越实践奖”67.Navicat 基于 GaussDB 主备版的快速入门68.数仓实时算子难以观测,快来试试算子级监控吧69.列举数据库缓存使用场景实例和命令速查表70.带你认识多模数据库GeminiDB架构与应用实践71.3招解决时序数据高基数难题,性能多维度提升!72.数仓性能调优:row_number() over(p)-rn=1性能瓶颈发现和改写套路73.数仓实践丨常量标量子查询做全连接导致整体慢74.细说GaussDB(DWS)的2种查询优化技术75.细说SQL与ETL之间的小秘密76.从概念到实践,带你掌握层次递归查询77.GeminiDB Cassandra接口新特性PITR发布:支持任意时间点恢复78.你的JoinHint为什么不生效79.六步走向无忧,华为云数据库高可用的秘密武器80.数仓调优实践丨SQL改写消除相关子查询81.GaussDB(for MySQL)新特性TDE发布:支持透明数据加密82.详解GaussDB(DWS)通信安全的小妙招:连接认证机制83.GaussDB(for MySQL) RegionlessDB发布:全球数据库技术84.5分钟带您了解DRS录制回放85.ICDM'23 BICE论文解读:基于双向LSTM和集成学习的模型框架86.数仓如何递归查询视图依赖87.支撑核心系统分布式改造,GaussDB为江南农商银行筑稳根基88.近6成金融机构的选择!华为云GaussDB加快金融核心系统转型89.GaussDB(for MySQL)剪枝功能,让查询性能提升70倍!90.2023年度十佳课题公布:华为云GaussDB获权威认可91.详解如何在数仓中搭建细粒度容灾应用92.对话苏光牛:国内数据库市场已进入关键转折点,2024年或是分水岭93.GaussDB通信运维:详解stream连接池设计原理94.GaussDB(for MySQL) Serverless全面商用:无感弹性,极致性价比95.华为云GaussDB支撑农行超级网银业务,性能和稳定性备受认可96.实例详解数据库的游标管理97.数仓实践丨从CU入手优化HStore表98.数仓的等待视图中,为什么会有Hashjoin-nestloop99.如何基于Sharding-JDBC实现GaussDB在客户端应用的读写分离100.如何迅速并识别处理MDL锁阻塞问题本文分享自华为云社区《GaussDB(DWS)性能调优:实时场景下表行数估算不准确引起的的性能瓶颈问题案例》,作者: O泡果奶~。
本文针对实时场景下SQL语句因表行数估算不准确而导致语句执行超时报错的案例进行分析。
1、【问题描述】
实时场景下,select查询语句执行时间过长,该语句verbose执行计划中存在nestloop,且使用hint(set (enable_index_nestloop off)) 无法生效。
2、【原始语句】
select * from ( select wo.work_order_id /*工单id*/, wo.work_order_code /*工单编码*/, wo.work_order_name /*工单名称*/, wo.work_order_level /*工单层级(第一层级(未拆分工单/父工单):1,第二层级(子工单):10)*/, decode(wo.work_order_level,1, '第一层级(未拆分工单/父工单)', 10,'第二层级(子工单)') as work_order_level_desc /*工单层级描述*/, substrb(wo.wo_description, 1, 1000) as wo_description /*工单描述*/, wo.wo_version /*工单版本号*/, wo.wo_lifecycle_status /*生命周期标识:0:正常工单,-1: 已删除*/, wo.business_id /*工单来源业务id*/, wo.business_type /*工单来源业务类型(10:活动流工单 20:手工派单 30:拆单工单 40:临时mos工单 50:ihub工单 60:ipmo工单 70:wbs工单 80:ncs工单 90:hr工单 100:ls工单 默认10)*/, decode( wo.business_type, '10', '活动流工单', '20', '手工派单', '30', '拆单工单', '40', '临时MOS工单', '50', 'ihub工单', '60', 'ipmo工单', '70', 'WBS工单', '80', 'NCS工单', '90', 'HR工单', '100', 'LS工单' ) as business_type_desc /*工单来源业务类型描述*/, wo.parent_activity_id /*父节点活动id*/, wo.activity_lib_id /*活动库活动id*/, wo.activity_type /*作业类型,1wbs,2活动,3里程碑*/, ac.activity_name /*活动名称*/, ac.std_ms_code as standard_ms_code /*标准里程碑编码*/, wo.plan_id /*计划id*/, wo.project_number as proj_num /*项目编码*/, wo.du_id /*交付单元id*/, wo.duration /*工期*/, wo.billing_flag /*开票标识:y-开票*/, wo.na_flag /*na标识*/, wo.inv_flag /*inv标识*/, wo.master_flag /*拆分标示,n:未拆分 ; y:已拆分*/, wo.created_by as created_by_id /*创建人user id*/, u1.lname as created_by /*创建人*/, wo.creation_date /*创建时间*/, wo.last_updated_by as last_updated_by_id /*最后更新人user id*/, u2.lname as last_updated_by /*最后更新人*/, wo.last_update_date /*最后更新时间*/, wp.wo_progress_id /*活动进度id*/, wp.expect_start_date /*预期开始日期*/, wp.expect_end_date /*预期结束日期*/, wp.plan_start_time /*计划开始时间*/, wp.plan_end_time /*计划完成时间*/, wp.actual_start_time /*实际开始时间*/, wp.actual_end_time /*实际完成时间*/, wp.close_time /*活动关闭时间*/, wp.completion_rate /*完工比率(数值如 0.8666)*/, to_char(substr(wp.remark, 1, 333)) as progress_description /*进度备注信息*/, wp.total_value /*总值*/, wp.accumulate_value /*累计值*/, wp.report_time /*值反馈时间*/, wp.total_plan_value /*总计划值*/, wp.ehs_risk /*高危活动类型*/, wp.delay_reason_id /*延迟原因id*/, substrb(ag.description, 1, 1000) as delay_reason_description /*延迟原因描述*/, wp.wo_status /*活动状态 psc_lookup_item_t_3220 classify_code = 'WO_STATUS_CODE'*/, l2.item_name as wo_status_desc /*活动状态描述*/,( case when lengthb(wp.approve_status) = 0 then null else wp.approve_status end ) :: number as approve_status /*审批状态 psc_lookup_item_t_3220 classify_code = 'WORK_ORDER_APPROVE_STATUS'*/, l3.item_name as approve_status_desc /*审批状态描述*/, wp.par_workorder_doc_flag /*父工单是否有交付件(y/n)*/, wp.deliverables_complete /*交付件上传状态 0:不涉及交付件 1:待上传交付件 2:交付件上传中,未上传完9:交付件已上传完*/, wp.revenue_trigger_status /*触发状态(0:未触发过 1:已触发 2:已触发,pc校验触发失败 3:pc触发成功)*/, wp.billing_status /*开票状态(空值:未触发过 1:已开票)*/, wp.frozen_flag /*冻结标识(y/n)*/, wp.mr_frozen_flag /*mr是否冻结站点要货通过更新实施计划刷新字段*/, wp.mr_status /*站点签状态 1未签收,2部分签收,3全部签收,10未签收,20部分签收,30全部签收,40部分超配置签收,50全部超配置签收 完工验状态 p:部分完成,f:全部完成*/, wp.tool_flag /*是否挂工具工单回写(y/n)*/, wp.split_cp_flag /*拆分施工计划标识 y已拆分 n未拆分*/, wp.mos_data_source /*站点签完工验状数据来源*/, wo.template_id /*模板id,例如活动流节点id*/, tfn.task_flow_id /*任务流id*/, tfn.task_flow_node_id /*活动流节点id*/, tfn.revenue_flag /*收入里程碑标识(y/n)*/, tfn.on_site /*是否现场*/, nvl(l1.item_name, tfn.owner_type) as owner_type /*责任方类型 客户/华为/分包商*/, tfn.subcon /*是否分包*/ /*产品域*/,case when wo.enable_flag = 'Y' and wp.enable_flag = 'Y' and wo.wo_lifecycle_status = 0 and nvl(du.enable_flag, 'Y') = 'Y' then 'Y' else 'N' end as enable_flag /*有效标识,y为有效n为失效*/, 'N' as del_flag /*删除标识 y为已删除*/, 3 as data_center_id /*数据中心id*/, tf.task_flow_code /*活动流编码 add by jwx528041 20200408*/, tfn.task_flow_node_code /*任务流节点编码 add by jwx528041 20200408*/, tfn.task_flow_node_name /*任务流节点名称 add by jwx528041 20200408*/, tfn.task_flow_node_type /*任务流节点类型 add by jwx528041 20200408*/, tfn.enable_flag as flow_enable_flag /*活动流有效标识 add by jwx528041 20200408*/, wo.tenant_code /*租户编码 add by jwx528041 20200408*/, tfn.activity_id /*活动流水号 add by jwx528041 20200408*/, tfn.lead_time /*持续时间 add by jwx528041 20200408*/, wo.resource_id as wo_actual_owner_id /*工单实际责任人id update by swx949890 202207*/, wo.resource_name as wo_actual_owner /*工单实际责任人 update by swx949890 202207*/, wo.contractor_id as wo_actual_owner_contr_id /*工单实际责任人分包商id update by swx949890 202207*/, wo.contractor_name as wo_actual_owner_contr_name /*工单实际责任人分包商名称 update by swx949890 202207*/, nvl(l4.item_name, tfn.delivery_model) as delivery_model /*工单交付模式 add by cwx613468 20200711*/, tfn.on_line_site /*是否上站 add by cwx613468 20200711*/, u3.lname as dispatcher_user_name /*调度人 add by cwx613468 20200711*/, tfn.approve_level_qty /*审批总层级 add by jwx528041 20200819*/, tf.task_flow_name /*活动流名称 add by jwx528041 20200819*/, tf.task_flow_type /*活动流类型 add by jwx528041 20200819*/, wp.source_code /*标识actual时间的修改来源,值为mobile标识从手机端回写 add by jwx528041 20200819*/, wp.plan_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/, wp.plan_update_time /*计划时间更新时间 add by jwx528041 20200819*/, wp.dispatch_time /*调度时间 add by jwx528041 20200819*/, wp.first_actual_update_time /*第一次实际开始时间填入时间 add by jwx528041 20200819*/, wp.first_actual_end_time /*第一次实际结束时间填入时间 add by jwx528041 20200819*/, wp.first_actual_updated_by /*第一次实际时间填入人user id add by jwx528041 20200819*/, wp.actual_start_update_time /*实际开始时间更新日期 add by jwx528041 20200819*/, wp.actual_start_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/, wp.actual_time_source /*实际完成时间更新来源 add by jwx528041 20200819*/, wp.actual_end_update_time /*实际完成时间更新日期 add by jwx528041 20200819*/, wp.actual_end_updated_by /*实际完成时间更新人user id add by jwx528041 20200819*/, wp.revenue_trigger_failed_msg /*收入触发失败原因 add by jwx528041 20200819*/, ag.souce_type as delay_reason_souce_type /*延迟原因数据来源:1、自定义 2、 add by jwx528041 20200819*/ --,ras.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type add by jwx528041 20200819*/ , wo.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type update by swx949890 202207*/, dr.resouce_type as wo_owner_resouce_type /*工单责任人资源类型 add by jwx528041 20200819*/, l5.item_name as wo_owner_resouce_type_desc /*工单责任人资源类型 add by jwx528041 20200819*/, u4.w3_account as wo_owner_w3_account /*工单责任人w3账号 add by jwx528041 20200819*/, rel.du_tf_rel_enable /*du与活动流关系有效性标识 y:有效 n:失效 add by lwx617215 20210116*/, t.billing_sla /*sla*/, t.billing_milestone /*开票里程碑*/, tf.required_tools, wp.active, gp.plan_code, gp.plan_name, gp.template_plan_id from sdisd.ogg_wo_work_order_2_3220 wo inner join sdisd.ogg_wo_progress_2_3220 wp on wo.work_order_id = wp.work_order_id left join sdisd.ogg_wo_task_flow_node_br_3220 tfn on wo.template_id = tfn.task_flow_node_id and nvl(wo.wo_version, 0) = case when nvl(wo.wo_version, 0) > 0 then tfn.version else tfn.wo_version end and wo.project_number = tfn.project_number left join sdisd.ogg_sds_activity_t_br_3220 ac on wo.activity_lib_id = ac.activity_id left join sdisd.ogg_sds_task_flow_t_br_3220 tf on tfn.task_flow_id = tf.task_flow_id left join sdisd.ogg_du_release_t_br_3220 du /*enable_flag新增有效du的判断 lwx617215 20210116*/ on wo.du_id = du.du_id left join sdisd.ogg_gcc_plan_2_3220 gp --dwx1189869 on wo.plan_id = gp.plan_id and gp.tenant_code = 'RolloutPlan' and gp.parent_plan_id = -1 and gp.enable_flag = 'Y' left join ( select r.du_id, r.task_flow_id, /*du与活动流有效标识*/ case when r.enable_flag = 'Y' and publish_flag = 'P' then 'Y' else 'N' end as du_tf_rel_enable, row_number() over( partition by r.du_id, r.task_flow_id order by r.last_update_date desc ) as rn from sdisd.ogg_rp_du_tf_release_3_3220 r ) rel on wo.du_id = rel.du_id and tfn.task_flow_id = rel.task_flow_id and rel.rn = 1 left join sdisd.ogg_tpl_user_t_3220 u1 on wo.created_by = u1.user_id left join sdisd.ogg_tpl_user_t_3220 u2 on wo.last_updated_by = u2.user_id left join sdisd.ogg_tpl_user_t_3220 u3 on wp.dispatcher_user_id = u3.user_id left join sdisd.ogg_sds_activity_gap_t_br_3220 ag on wp.delay_reason_id = ag.activity_gap_id left join sdisd.ogg_tpl_lookup_item_t_3220 l1 on tfn.owner_type = l1.item_code and l1.classify_code = 'SDS_TASK_OWNER_TYPE' and l1.language = 'en_US' left join sdisd.ogg_psc_lookup_item_t_3220 l2 on wp.wo_status = l2.item_code and l2.classify_code = 'WO_STATUS_CODE' and l2.language = 'en_US' left join sdisd.ogg_psc_lookup_item_t_3220 l3 on wp.approve_status = l3.item_code and l3.classify_code = 'WORK_ORDER_APPROVE_STATUS' and l3.language = 'en_US' left join sdisd.ogg_tpl_lookup_item_t_3220 l4 on tfn.delivery_model = l4.item_code and l4.classify_code = 'SDS_TASK_ON_SITE' and l4.language = 'en_US' left join sdisd.ogg_pm_project_tree_node_3220 tn on wo.resource_id = tn.tree_id left join sdisd.ogg_pm_delivery_resource_3220 dr on tn.resource_id = dr.resource_id left join sdisd.ogg_tpl_user_t_3220 u4 on dr.user_id = u4.user_id left join sdisd.ogg_tpl_lookup_item_t_3220 l5 on dr.resouce_type = l5.item_code and l5.classify_code = 'PM_RESOURCE_TYPE' and l5.language = 'zh_CN' left join sdisd.ogg_sds_task_flow_node_br_3220 t on tfn.task_flow_node_id = t.task_flow_node_id where ( wo.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or wp.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or tfn.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or ag.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 ) union all select wo.work_order_id /*工单id*/, wo.work_order_code /*工单编码*/, wo.work_order_name /*工单名称*/, wo.work_order_level /*工单层级(第一层级(未拆分工单/父工单):1,第二层级(子工单):10)*/, decode( wo.work_order_level, 1, '第一层级(未拆分工单/父工单)', 10, '第二层级(子工单)' ) as work_order_level_desc /*工单层级描述*/, substrb(wo.wo_description, 1, 1000) as wo_description /*工单描述*/, wo.wo_version /*工单版本号*/, wo.wo_lifecycle_status /*生命周期标识:0:正常工单,-1: 已删除*/, wo.business_id /*工单来源业务id*/, wo.business_type /*工单来源业务类型(10:活动流工单 20:手工派单 30:拆单工单 40:临时mos工单 50:ihub工单 60:ipmo工单 70:wbs工单 80:ncs工单 90:hr工单 100:ls工单 默认10)*/, decode( wo.business_type, '10', '活动流工单', '20', '手工派单', '30', '拆单工单', '40', '临时MOS工单', '50', 'ihub工单', '60', 'ipmo工单', '70', 'WBS工单', '80', 'NCS工单', '90', 'HR工单', '100', 'LS工单' ) as business_type_desc /*工单来源业务类型描述*/, wo.parent_activity_id /*父节点活动id*/, wo.activity_lib_id /*活动库活动id*/, wo.activity_type /*作业类型,1wbs,2活动,3里程碑*/, ac.activity_name /*活动名称*/, ac.std_ms_code as standard_ms_code /*标准里程碑编码*/, wo.plan_id /*计划id*/, wo.project_number as proj_num /*项目编码*/, wo.du_id /*交付单元id*/, wo.duration /*工期*/, wo.billing_flag /*开票标识:y-开票*/, wo.na_flag /*na标识*/, wo.inv_flag /*inv标识*/, wo.master_flag /*拆分标示,n:未拆分 ; y:已拆分*/, wo.created_by as created_by_id /*创建人user id*/, u1.lname as created_by /*创建人*/, wo.creation_date /*创建时间*/, wo.last_updated_by as last_updated_by_id /*最后更新人user id*/, u2.lname as last_updated_by /*最后更新人*/, wo.last_update_date /*最后更新时间*/, wp.wo_progress_id /*活动进度id*/, wp.expect_start_date /*预期开始日期*/, wp.expect_end_date /*预期结束日期*/, wp.plan_start_time /*计划开始时间*/, wp.plan_end_time /*计划完成时间*/, wp.actual_start_time /*实际开始时间*/, wp.actual_end_time /*实际完成时间*/, wp.close_time /*活动关闭时间*/, wp.completion_rate /*完工比率(数值如 0.8666)*/, to_char(substr(wp.remark, 1, 333)) as progress_description /*进度备注信息*/, wp.total_value /*总值*/, wp.accumulate_value /*累计值*/, wp.report_time /*值反馈时间*/, wp.total_plan_value /*总计划值*/, wp.ehs_risk /*高危活动类型*/, wp.delay_reason_id /*延迟原因id*/, substrb(ag.description, 1, 1000) as delay_reason_description /*延迟原因描述*/, wp.wo_status /*活动状态 psc_lookup_item_t_3220 classify_code = 'WO_STATUS_CODE'*/, l2.item_name as wo_status_desc /*活动状态描述*/,( case when lengthb(wp.approve_status) = 0 then null else wp.approve_status end ) :: number as approve_status /*审批状态 psc_lookup_item_t_3220 classify_code = 'WORK_ORDER_APPROVE_STATUS'*/, l3.item_name as approve_status_desc /*审批状态描述*/, wp.par_workorder_doc_flag /*父工单是否有交付件(y/n)*/, wp.deliverables_complete /*交付件上传状态 0:不涉及交付件 1:待上传交付件 2:交付件上传中,未上传完9:交付件已上传完*/, wp.revenue_trigger_status /*触发状态(0:未触发过 1:已触发 2:已触发,pc校验触发失败 3:pc触发成功)*/, wp.billing_status /*开票状态(空值:未触发过 1:已开票)*/, wp.frozen_flag /*冻结标识(y/n)*/, wp.mr_frozen_flag /*mr是否冻结站点要货通过更新实施计划刷新字段*/, wp.mr_status /*站点签状态 1未签收,2部分签收,3全部签收,10未签收,20部分签收,30全部签收,40部分超配置签收,50全部超配置签收 完工验状态 p:部分完成,f:全部完成*/, wp.tool_flag /*是否挂工具工单回写(y/n)*/, wp.split_cp_flag /*拆分施工计划标识 y已拆分 n未拆分*/, wp.mos_data_source /*站点签完工验状数据来源*/, wo.template_id /*模板id,例如活动流节点id*/, tfn.task_flow_id /*任务流id*/, tfn.task_flow_node_id /*活动流节点id*/, tfn.revenue_flag /*收入里程碑标识(y/n)*/, tfn.on_site /*是否现场*/, nvl(l1.item_name, tfn.owner_type) as owner_type /*责任方类型 客户/华为/分包商*/, tfn.subcon /*是否分包*/ /*产品域*/,case when wo.enable_flag = 'Y' and wp.enable_flag = 'Y' and wo.wo_lifecycle_status = 0 and nvl(du.enable_flag, 'Y') = 'Y' then 'Y' else 'N' end as enable_flag /*有效标识,y为有效n为失效*/, 'N' as del_flag /*删除标识 y为已删除*/, 4 as data_center_id /*数据中心id*/, tf.task_flow_code /*活动流编码 add by jwx528041 20200408*/, tfn.task_flow_node_code /*任务流节点编码 add by jwx528041 20200408*/, tfn.task_flow_node_name /*任务流节点名称 add by jwx528041 20200408*/, tfn.task_flow_node_type /*任务流节点类型 add by jwx528041 20200408*/, tfn.enable_flag as flow_enable_flag /*活动流有效标识 add by jwx528041 20200408*/, wo.tenant_code /*租户编码 add by jwx528041 20200408*/, tfn.activity_id /*活动流水号 add by jwx528041 20200408*/, tfn.lead_time /*持续时间 add by jwx528041 20200408*/, wo.resource_id as wo_actual_owner_id /*工单实际责任人id update by swx949890 202207*/, wo.resource_name as wo_actual_owner /*工单实际责任人 update by swx949890 202207*/, wo.contractor_id as wo_actual_owner_contr_id /*工单实际责任人分包商id update by swx949890 202207*/, wo.contractor_name as wo_actual_owner_contr_name /*工单实际责任人分包商名称 update by swx949890 202207*/, nvl(l4.item_name, tfn.delivery_model) as delivery_model /*工单交付模式 add by cwx613468 20200711*/, tfn.on_line_site /*是否上站 add by cwx613468 20200711*/, u3.lname as dispatcher_user_name /*调度人 add by cwx613468 20200711*/, tfn.approve_level_qty /*审批总层级 add by jwx528041 20200819*/, tf.task_flow_name /*活动流名称 add by jwx528041 20200819*/, tf.task_flow_type /*活动流类型 add by jwx528041 20200819*/, wp.source_code /*标识actual时间的修改来源,值为mobile标识从手机端回写 add by jwx528041 20200819*/, wp.plan_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/, wp.plan_update_time /*计划时间更新时间 add by jwx528041 20200819*/, wp.dispatch_time /*调度时间 add by jwx528041 20200819*/, wp.first_actual_update_time /*第一次实际开始时间填入时间 add by jwx528041 20200819*/, wp.first_actual_end_time /*第一次实际结束时间填入时间 add by jwx528041 20200819*/, wp.first_actual_updated_by /*第一次实际时间填入人user id add by jwx528041 20200819*/, wp.actual_start_update_time /*实际开始时间更新日期 add by jwx528041 20200819*/, wp.actual_start_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/, wp.actual_time_source /*实际完成时间更新来源 add by jwx528041 20200819*/, wp.actual_end_update_time /*实际完成时间更新日期 add by jwx528041 20200819*/, wp.actual_end_updated_by /*实际完成时间更新人user id add by jwx528041 20200819*/, wp.revenue_trigger_failed_msg /*收入触发失败原因 add by jwx528041 20200819*/, ag.souce_type as delay_reason_souce_type /*延迟原因数据来源:1、自定义 2、 add by jwx528041 20200819*/ --,ras.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type add by jwx528041 20200819*/ , wo.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type update by swx949890 202207*/, dr.resouce_type as wo_owner_resouce_type /*工单责任人资源类型 add by jwx528041 20200819*/, l5.item_name as wo_owner_resouce_type_desc /*工单责任人资源类型 add by jwx528041 20200819*/, u4.w3_account as wo_owner_w3_account /*工单责任人w3账号 add by jwx528041 20200819*/, rel.du_tf_rel_enable /*du与活动流关系有效性标识 y:有效 n:失效 add by lwx617215 20210116*/, t.billing_sla /*sla*/, t.billing_milestone /*开票里程碑*/, tf.required_tools, wp.active, gp.plan_code, gp.plan_name, gp.template_plan_id from sdisd.ogg_wo_work_order17_3220 wo inner join sdisd.ogg_wo_progress17_3220 wp on wo.work_order_id = wp.work_order_id left join sdisd.ogg_wo_task_flow_node_za_3220 tfn on wo.template_id = tfn.task_flow_node_id and nvl(wo.wo_version, 0) = case when nvl(wo.wo_version, 0) > 0 then tfn.version else tfn.wo_version end and wo.project_number = tfn.project_number left join sdisd.ogg_sds_activity_t_za_3220 ac on wo.activity_lib_id = ac.activity_id left join sdisd.ogg_sds_task_flow_t_za_3220 tf on tfn.task_flow_id = tf.task_flow_id left join sdisd.ogg_du_release_t_za_3220 du /*enable_flag新增有效du的判断 lwx617215 20210116*/ on wo.du_id = du.du_id left join sdisd.ogg_gcc_plan17_3220 gp --dwx1189869 on wo.plan_id = gp.plan_id and gp.tenant_code = 'RolloutPlan' and gp.parent_plan_id = -1 and gp.enable_flag = 'Y' left join ( select r.du_id, r.task_flow_id, /*du与活动流有效标识*/ case when r.enable_flag = 'Y' and publish_flag = 'P' then 'Y' else 'N' end as du_tf_rel_enable, row_number() over( partition by r.du_id, r.task_flow_id order by r.last_update_date desc ) as rn from sdisd.ogg_rp_du_tf_release18_3220 r ) rel on wo.du_id = rel.du_id and tfn.task_flow_id = rel.task_flow_id and rel.rn = 1 left join sdisd.ogg_tpl_user_t_3220 u1 on wo.created_by = u1.user_id left join sdisd.ogg_tpl_user_t_3220 u2 on wo.last_updated_by = u2.user_id left join sdisd.ogg_tpl_user_t_3220 u3 on wp.dispatcher_user_id = u3.user_id left join sdisd.ogg_sds_activity_gap_t_za_3220 ag on wp.delay_reason_id = ag.activity_gap_id left join sdisd.ogg_tpl_lookup_item_t_3220 l1 on tfn.owner_type = l1.item_code and l1.classify_code = 'SDS_TASK_OWNER_TYPE' and l1.language = 'en_US' left join sdisd.ogg_psc_lookup_item_t_3220 l2 on wp.wo_status = l2.item_code and l2.classify_code = 'WO_STATUS_CODE' and l2.language = 'en_US' left join sdisd.ogg_psc_lookup_item_t_3220 l3 on wp.approve_status = l3.item_code and l3.classify_code = 'WORK_ORDER_APPROVE_STATUS' and l3.language = 'en_US' left join sdisd.ogg_tpl_lookup_item_t_3220 l4 on tfn.delivery_model = l4.item_code and l4.classify_code = 'SDS_TASK_ON_SITE' and l4.language = 'en_US' left join sdisd.ogg_pm_project_tree_node_3220 tn on wo.resource_id = tn.tree_id left join sdisd.ogg_pm_delivery_resource_3220 dr on tn.resource_id = dr.resource_id left join sdisd.ogg_tpl_user_t_3220 u4 on dr.user_id = u4.user_id left join sdisd.ogg_tpl_lookup_item_t_3220 l5 on dr.resouce_type = l5.item_code and l5.classify_code = 'PM_RESOURCE_TYPE' and l5.language = 'zh_CN' left join sdisd.ogg_sds_task_flow_node_za_3220 t on tfn.task_flow_node_id = t.task_flow_node_id where ( wo.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or wp.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or tfn.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or ag.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 )) as t limit 10
3、【性能分析】
优化前SQL语句执行时间达到3600s,超时自动报错,如下图所示:
可以看出(具体verbose执行计划如附件1所示),verbose执行计划中存在过多的NestLoop算子,一般情况下,该算子影响SQL语句执行性能,应该尽可能避免使用。通常可以利用语句
set [global] (enable_index_nestloop off)
来避免执行器走NestLoop算子。但有些场景下,该语句无法保证不使用NestLoop算子。因此,可以从另一方面入手解决这一问题,优化器因为对表估算不准确,故给出NestLoop算子的方案,可以利用tablescan这一hint对表进行全表扫描,以保证执行器走HashJoin算子而非NestLoop算子,从而提高语句执行性能。
注意:在使用tablescan这个hint时要保证NestLoop算子涉及到的表都要加上
优化后的SQL语句如下所示:
select * from ( select/*+tablescan(wp) tablescan(wo) tablescan(du) tablescan(ac) tablescan(u3) tablescan(u1) tablescan(u2) tablescan(tn) tablescan(dr) tablescan(u4) tablescan(t)*/ wo.work_order_id /*工单id*/, wo.work_order_code /*工单编码*/, wo.work_order_name /*工单名称*/, wo.work_order_level /*工单层级(第一层级(未拆分工单/父工单):1,第二层级(子工单):10)*/, decode(wo.work_order_level,1, '第一层级(未拆分工单/父工单)', 10,'第二层级(子工单)') as work_order_level_desc /*工单层级描述*/, substrb(wo.wo_description, 1, 1000) as wo_description /*工单描述*/, wo.wo_version /*工单版本号*/, wo.wo_lifecycle_status /*生命周期标识:0:正常工单,-1: 已删除*/, wo.business_id /*工单来源业务id*/, wo.business_type /*工单来源业务类型(10:活动流工单 20:手工派单 30:拆单工单 40:临时mos工单 50:ihub工单 60:ipmo工单 70:wbs工单 80:ncs工单 90:hr工单 100:ls工单 默认10)*/, decode( wo.business_type, '10', '活动流工单', '20', '手工派单', '30', '拆单工单', '40', '临时MOS工单', '50', 'ihub工单', '60', 'ipmo工单', '70', 'WBS工单', '80', 'NCS工单', '90', 'HR工单', '100', 'LS工单' ) as business_type_desc /*工单来源业务类型描述*/, wo.parent_activity_id /*父节点活动id*/, wo.activity_lib_id /*活动库活动id*/, wo.activity_type /*作业类型,1wbs,2活动,3里程碑*/, ac.activity_name /*活动名称*/, ac.std_ms_code as standard_ms_code /*标准里程碑编码*/, wo.plan_id /*计划id*/, wo.project_number as proj_num /*项目编码*/, wo.du_id /*交付单元id*/, wo.duration /*工期*/, wo.billing_flag /*开票标识:y-开票*/, wo.na_flag /*na标识*/, wo.inv_flag /*inv标识*/, wo.master_flag /*拆分标示,n:未拆分 ; y:已拆分*/, wo.created_by as created_by_id /*创建人user id*/, u1.lname as created_by /*创建人*/, wo.creation_date /*创建时间*/, wo.last_updated_by as last_updated_by_id /*最后更新人user id*/, u2.lname as last_updated_by /*最后更新人*/, wo.last_update_date /*最后更新时间*/, wp.wo_progress_id /*活动进度id*/, wp.expect_start_date /*预期开始日期*/, wp.expect_end_date /*预期结束日期*/, wp.plan_start_time /*计划开始时间*/, wp.plan_end_time /*计划完成时间*/, wp.actual_start_time /*实际开始时间*/, wp.actual_end_time /*实际完成时间*/, wp.close_time /*活动关闭时间*/, wp.completion_rate /*完工比率(数值如 0.8666)*/, to_char(substr(wp.remark, 1, 333)) as progress_description /*进度备注信息*/, wp.total_value /*总值*/, wp.accumulate_value /*累计值*/, wp.report_time /*值反馈时间*/, wp.total_plan_value /*总计划值*/, wp.ehs_risk /*高危活动类型*/, wp.delay_reason_id /*延迟原因id*/, substrb(ag.description, 1, 1000) as delay_reason_description /*延迟原因描述*/, wp.wo_status /*活动状态 psc_lookup_item_t_3220 classify_code = 'WO_STATUS_CODE'*/, l2.item_name as wo_status_desc /*活动状态描述*/,( case when lengthb(wp.approve_status) = 0 then null else wp.approve_status end ) :: number as approve_status /*审批状态 psc_lookup_item_t_3220 classify_code = 'WORK_ORDER_APPROVE_STATUS'*/, l3.item_name as approve_status_desc /*审批状态描述*/, wp.par_workorder_doc_flag /*父工单是否有交付件(y/n)*/, wp.deliverables_complete /*交付件上传状态 0:不涉及交付件 1:待上传交付件 2:交付件上传中,未上传完9:交付件已上传完*/, wp.revenue_trigger_status /*触发状态(0:未触发过 1:已触发 2:已触发,pc校验触发失败 3:pc触发成功)*/, wp.billing_status /*开票状态(空值:未触发过 1:已开票)*/, wp.frozen_flag /*冻结标识(y/n)*/, wp.mr_frozen_flag /*mr是否冻结站点要货通过更新实施计划刷新字段*/, wp.mr_status /*站点签状态 1未签收,2部分签收,3全部签收,10未签收,20部分签收,30全部签收,40部分超配置签收,50全部超配置签收 完工验状态 p:部分完成,f:全部完成*/, wp.tool_flag /*是否挂工具工单回写(y/n)*/, wp.split_cp_flag /*拆分施工计划标识 y已拆分 n未拆分*/, wp.mos_data_source /*站点签完工验状数据来源*/, wo.template_id /*模板id,例如活动流节点id*/, tfn.task_flow_id /*任务流id*/, tfn.task_flow_node_id /*活动流节点id*/, tfn.revenue_flag /*收入里程碑标识(y/n)*/, tfn.on_site /*是否现场*/, nvl(l1.item_name, tfn.owner_type) as owner_type /*责任方类型 客户/华为/分包商*/, tfn.subcon /*是否分包*/ /*产品域*/,case when wo.enable_flag = 'Y' and wp.enable_flag = 'Y' and wo.wo_lifecycle_status = 0 and nvl(du.enable_flag, 'Y') = 'Y' then 'Y' else 'N' end as enable_flag /*有效标识,y为有效n为失效*/, 'N' as del_flag /*删除标识 y为已删除*/, 3 as data_center_id /*数据中心id*/, tf.task_flow_code /*活动流编码 add by jwx528041 20200408*/, tfn.task_flow_node_code /*任务流节点编码 add by jwx528041 20200408*/, tfn.task_flow_node_name /*任务流节点名称 add by jwx528041 20200408*/, tfn.task_flow_node_type /*任务流节点类型 add by jwx528041 20200408*/, tfn.enable_flag as flow_enable_flag /*活动流有效标识 add by jwx528041 20200408*/, wo.tenant_code /*租户编码 add by jwx528041 20200408*/, tfn.activity_id /*活动流水号 add by jwx528041 20200408*/, tfn.lead_time /*持续时间 add by jwx528041 20200408*/, wo.resource_id as wo_actual_owner_id /*工单实际责任人id update by swx949890 202207*/, wo.resource_name as wo_actual_owner /*工单实际责任人 update by swx949890 202207*/, wo.contractor_id as wo_actual_owner_contr_id /*工单实际责任人分包商id update by swx949890 202207*/, wo.contractor_name as wo_actual_owner_contr_name /*工单实际责任人分包商名称 update by swx949890 202207*/, nvl(l4.item_name, tfn.delivery_model) as delivery_model /*工单交付模式 add by cwx613468 20200711*/, tfn.on_line_site /*是否上站 add by cwx613468 20200711*/, u3.lname as dispatcher_user_name /*调度人 add by cwx613468 20200711*/, tfn.approve_level_qty /*审批总层级 add by jwx528041 20200819*/, tf.task_flow_name /*活动流名称 add by jwx528041 20200819*/, tf.task_flow_type /*活动流类型 add by jwx528041 20200819*/, wp.source_code /*标识actual时间的修改来源,值为mobile标识从手机端回写 add by jwx528041 20200819*/, wp.plan_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/, wp.plan_update_time /*计划时间更新时间 add by jwx528041 20200819*/, wp.dispatch_time /*调度时间 add by jwx528041 20200819*/, wp.first_actual_update_time /*第一次实际开始时间填入时间 add by jwx528041 20200819*/, wp.first_actual_end_time /*第一次实际结束时间填入时间 add by jwx528041 20200819*/, wp.first_actual_updated_by /*第一次实际时间填入人user id add by jwx528041 20200819*/, wp.actual_start_update_time /*实际开始时间更新日期 add by jwx528041 20200819*/, wp.actual_start_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/, wp.actual_time_source /*实际完成时间更新来源 add by jwx528041 20200819*/, wp.actual_end_update_time /*实际完成时间更新日期 add by jwx528041 20200819*/, wp.actual_end_updated_by /*实际完成时间更新人user id add by jwx528041 20200819*/, wp.revenue_trigger_failed_msg /*收入触发失败原因 add by jwx528041 20200819*/, ag.souce_type as delay_reason_souce_type /*延迟原因数据来源:1、自定义 2、 add by jwx528041 20200819*/ --,ras.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type add by jwx528041 20200819*/ , wo.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type update by swx949890 202207*/, dr.resouce_type as wo_owner_resouce_type /*工单责任人资源类型 add by jwx528041 20200819*/, l5.item_name as wo_owner_resouce_type_desc /*工单责任人资源类型 add by jwx528041 20200819*/, u4.w3_account as wo_owner_w3_account /*工单责任人w3账号 add by jwx528041 20200819*/, rel.du_tf_rel_enable /*du与活动流关系有效性标识 y:有效 n:失效 add by lwx617215 20210116*/, t.billing_sla /*sla*/, t.billing_milestone /*开票里程碑*/, tf.required_tools, wp.active, gp.plan_code, gp.plan_name, gp.template_plan_id from sdisd.ogg_wo_work_order_2_3220 wo inner join sdisd.ogg_wo_progress_2_3220 wp on wo.work_order_id = wp.work_order_id left join sdisd.ogg_wo_task_flow_node_br_3220 tfn on wo.template_id = tfn.task_flow_node_id and nvl(wo.wo_version, 0) = case when nvl(wo.wo_version, 0) > 0 then tfn.version else tfn.wo_version end and wo.project_number = tfn.project_number left join sdisd.ogg_sds_activity_t_br_3220 ac on wo.activity_lib_id = ac.activity_id left join sdisd.ogg_sds_task_flow_t_br_3220 tf on tfn.task_flow_id = tf.task_flow_id left join sdisd.ogg_du_release_t_br_3220 du /*enable_flag新增有效du的判断 lwx617215 20210116*/ on wo.du_id = du.du_id left join sdisd.ogg_gcc_plan_2_3220 gp --dwx1189869 on wo.plan_id = gp.plan_id and gp.tenant_code = 'RolloutPlan' and gp.parent_plan_id = -1 and gp.enable_flag = 'Y' left join ( select r.du_id, r.task_flow_id, /*du与活动流有效标识*/ case when r.enable_flag = 'Y' and publish_flag = 'P' then 'Y' else 'N' end as du_tf_rel_enable, row_number() over( partition by r.du_id, r.task_flow_id order by r.last_update_date desc ) as rn from sdisd.ogg_rp_du_tf_release_3_3220 r ) rel on wo.du_id = rel.du_id and tfn.task_flow_id = rel.task_flow_id and rel.rn = 1 left join sdisd.ogg_tpl_user_t_3220 u1 on wo.created_by = u1.user_id left join sdisd.ogg_tpl_user_t_3220 u2 on wo.last_updated_by = u2.user_id left join sdisd.ogg_tpl_user_t_3220 u3 on wp.dispatcher_user_id = u3.user_id left join sdisd.ogg_sds_activity_gap_t_br_3220 ag on wp.delay_reason_id = ag.activity_gap_id left join sdisd.ogg_tpl_lookup_item_t_3220 l1 on tfn.owner_type = l1.item_code and l1.classify_code = 'SDS_TASK_OWNER_TYPE' and l1.language = 'en_US' left join sdisd.ogg_psc_lookup_item_t_3220 l2 on wp.wo_status = l2.item_code and l2.classify_code = 'WO_STATUS_CODE' and l2.language = 'en_US' left join sdisd.ogg_psc_lookup_item_t_3220 l3 on wp.approve_status = l3.item_code and l3.classify_code = 'WORK_ORDER_APPROVE_STATUS' and l3.language = 'en_US' left join sdisd.ogg_tpl_lookup_item_t_3220 l4 on tfn.delivery_model = l4.item_code and l4.classify_code = 'SDS_TASK_ON_SITE' and l4.language = 'en_US' left join sdisd.ogg_pm_project_tree_node_3220 tn on wo.resource_id = tn.tree_id left join sdisd.ogg_pm_delivery_resource_3220 dr on tn.resource_id = dr.resource_id left join sdisd.ogg_tpl_user_t_3220 u4 on dr.user_id = u4.user_id left join sdisd.ogg_tpl_lookup_item_t_3220 l5 on dr.resouce_type = l5.item_code and l5.classify_code = 'PM_RESOURCE_TYPE' and l5.language = 'zh_CN' left join sdisd.ogg_sds_task_flow_node_br_3220 t on tfn.task_flow_node_id = t.task_flow_node_id where ( wo.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or wp.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or tfn.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or ag.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 ) union all select wo.work_order_id /*工单id*/, wo.work_order_code /*工单编码*/, wo.work_order_name /*工单名称*/, wo.work_order_level /*工单层级(第一层级(未拆分工单/父工单):1,第二层级(子工单):10)*/, decode( wo.work_order_level, 1, '第一层级(未拆分工单/父工单)', 10, '第二层级(子工单)' ) as work_order_level_desc /*工单层级描述*/, substrb(wo.wo_description, 1, 1000) as wo_description /*工单描述*/, wo.wo_version /*工单版本号*/, wo.wo_lifecycle_status /*生命周期标识:0:正常工单,-1: 已删除*/, wo.business_id /*工单来源业务id*/, wo.business_type /*工单来源业务类型(10:活动流工单 20:手工派单 30:拆单工单 40:临时mos工单 50:ihub工单 60:ipmo工单 70:wbs工单 80:ncs工单 90:hr工单 100:ls工单 默认10)*/, decode( wo.business_type, '10', '活动流工单', '20', '手工派单', '30', '拆单工单', '40', '临时MOS工单', '50', 'ihub工单', '60', 'ipmo工单', '70', 'WBS工单', '80', 'NCS工单', '90', 'HR工单', '100', 'LS工单' ) as business_type_desc /*工单来源业务类型描述*/, wo.parent_activity_id /*父节点活动id*/, wo.activity_lib_id /*活动库活动id*/, wo.activity_type /*作业类型,1wbs,2活动,3里程碑*/, ac.activity_name /*活动名称*/, ac.std_ms_code as standard_ms_code /*标准里程碑编码*/, wo.plan_id /*计划id*/, wo.project_number as proj_num /*项目编码*/, wo.du_id /*交付单元id*/, wo.duration /*工期*/, wo.billing_flag /*开票标识:y-开票*/, wo.na_flag /*na标识*/, wo.inv_flag /*inv标识*/, wo.master_flag /*拆分标示,n:未拆分 ; y:已拆分*/, wo.created_by as created_by_id /*创建人user id*/, u1.lname as created_by /*创建人*/, wo.creation_date /*创建时间*/, wo.last_updated_by as last_updated_by_id /*最后更新人user id*/, u2.lname as last_updated_by /*最后更新人*/, wo.last_update_date /*最后更新时间*/, wp.wo_progress_id /*活动进度id*/, wp.expect_start_date /*预期开始日期*/, wp.expect_end_date /*预期结束日期*/, wp.plan_start_time /*计划开始时间*/, wp.plan_end_time /*计划完成时间*/, wp.actual_start_time /*实际开始时间*/, wp.actual_end_time /*实际完成时间*/, wp.close_time /*活动关闭时间*/, wp.completion_rate /*完工比率(数值如 0.8666)*/, to_char(substr(wp.remark, 1, 333)) as progress_description /*进度备注信息*/, wp.total_value /*总值*/, wp.accumulate_value /*累计值*/, wp.report_time /*值反馈时间*/, wp.total_plan_value /*总计划值*/, wp.ehs_risk /*高危活动类型*/, wp.delay_reason_id /*延迟原因id*/, substrb(ag.description, 1, 1000) as delay_reason_description /*延迟原因描述*/, wp.wo_status /*活动状态 psc_lookup_item_t_3220 classify_code = 'WO_STATUS_CODE'*/, l2.item_name as wo_status_desc /*活动状态描述*/,( case when lengthb(wp.approve_status) = 0 then null else wp.approve_status end ) :: number as approve_status /*审批状态 psc_lookup_item_t_3220 classify_code = 'WORK_ORDER_APPROVE_STATUS'*/, l3.item_name as approve_status_desc /*审批状态描述*/, wp.par_workorder_doc_flag /*父工单是否有交付件(y/n)*/, wp.deliverables_complete /*交付件上传状态 0:不涉及交付件 1:待上传交付件 2:交付件上传中,未上传完9:交付件已上传完*/, wp.revenue_trigger_status /*触发状态(0:未触发过 1:已触发 2:已触发,pc校验触发失败 3:pc触发成功)*/, wp.billing_status /*开票状态(空值:未触发过 1:已开票)*/, wp.frozen_flag /*冻结标识(y/n)*/, wp.mr_frozen_flag /*mr是否冻结站点要货通过更新实施计划刷新字段*/, wp.mr_status /*站点签状态 1未签收,2部分签收,3全部签收,10未签收,20部分签收,30全部签收,40部分超配置签收,50全部超配置签收 完工验状态 p:部分完成,f:全部完成*/, wp.tool_flag /*是否挂工具工单回写(y/n)*/, wp.split_cp_flag /*拆分施工计划标识 y已拆分 n未拆分*/, wp.mos_data_source /*站点签完工验状数据来源*/, wo.template_id /*模板id,例如活动流节点id*/, tfn.task_flow_id /*任务流id*/, tfn.task_flow_node_id /*活动流节点id*/, tfn.revenue_flag /*收入里程碑标识(y/n)*/, tfn.on_site /*是否现场*/, nvl(l1.item_name, tfn.owner_type) as owner_type /*责任方类型 客户/华为/分包商*/, tfn.subcon /*是否分包*/ /*产品域*/,case when wo.enable_flag = 'Y' and wp.enable_flag = 'Y' and wo.wo_lifecycle_status = 0 and nvl(du.enable_flag, 'Y') = 'Y' then 'Y' else 'N' end as enable_flag /*有效标识,y为有效n为失效*/, 'N' as del_flag /*删除标识 y为已删除*/, 4 as data_center_id /*数据中心id*/, tf.task_flow_code /*活动流编码 add by jwx528041 20200408*/, tfn.task_flow_node_code /*任务流节点编码 add by jwx528041 20200408*/, tfn.task_flow_node_name /*任务流节点名称 add by jwx528041 20200408*/, tfn.task_flow_node_type /*任务流节点类型 add by jwx528041 20200408*/, tfn.enable_flag as flow_enable_flag /*活动流有效标识 add by jwx528041 20200408*/, wo.tenant_code /*租户编码 add by jwx528041 20200408*/, tfn.activity_id /*活动流水号 add by jwx528041 20200408*/, tfn.lead_time /*持续时间 add by jwx528041 20200408*/, wo.resource_id as wo_actual_owner_id /*工单实际责任人id update by swx949890 202207*/, wo.resource_name as wo_actual_owner /*工单实际责任人 update by swx949890 202207*/, wo.contractor_id as wo_actual_owner_contr_id /*工单实际责任人分包商id update by swx949890 202207*/, wo.contractor_name as wo_actual_owner_contr_name /*工单实际责任人分包商名称 update by swx949890 202207*/, nvl(l4.item_name, tfn.delivery_model) as delivery_model /*工单交付模式 add by cwx613468 20200711*/, tfn.on_line_site /*是否上站 add by cwx613468 20200711*/, u3.lname as dispatcher_user_name /*调度人 add by cwx613468 20200711*/, tfn.approve_level_qty /*审批总层级 add by jwx528041 20200819*/, tf.task_flow_name /*活动流名称 add by jwx528041 20200819*/, tf.task_flow_type /*活动流类型 add by jwx528041 20200819*/, wp.source_code /*标识actual时间的修改来源,值为mobile标识从手机端回写 add by jwx528041 20200819*/, wp.plan_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/, wp.plan_update_time /*计划时间更新时间 add by jwx528041 20200819*/, wp.dispatch_time /*调度时间 add by jwx528041 20200819*/, wp.first_actual_update_time /*第一次实际开始时间填入时间 add by jwx528041 20200819*/, wp.first_actual_end_time /*第一次实际结束时间填入时间 add by jwx528041 20200819*/, wp.first_actual_updated_by /*第一次实际时间填入人user id add by jwx528041 20200819*/, wp.actual_start_update_time /*实际开始时间更新日期 add by jwx528041 20200819*/, wp.actual_start_updated_by /*实际开始时间更新人user id add by jwx528041 20200819*/, wp.actual_time_source /*实际完成时间更新来源 add by jwx528041 20200819*/, wp.actual_end_update_time /*实际完成时间更新日期 add by jwx528041 20200819*/, wp.actual_end_updated_by /*实际完成时间更新人user id add by jwx528041 20200819*/, wp.revenue_trigger_failed_msg /*收入触发失败原因 add by jwx528041 20200819*/, ag.souce_type as delay_reason_souce_type /*延迟原因数据来源:1、自定义 2、 add by jwx528041 20200819*/ --,ras.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type add by jwx528041 20200819*/ , wo.tree_type as wo_owner_tree_type /*工单责任人项目树节点类型tree_type update by swx949890 202207*/, dr.resouce_type as wo_owner_resouce_type /*工单责任人资源类型 add by jwx528041 20200819*/, l5.item_name as wo_owner_resouce_type_desc /*工单责任人资源类型 add by jwx528041 20200819*/, u4.w3_account as wo_owner_w3_account /*工单责任人w3账号 add by jwx528041 20200819*/, rel.du_tf_rel_enable /*du与活动流关系有效性标识 y:有效 n:失效 add by lwx617215 20210116*/, t.billing_sla /*sla*/, t.billing_milestone /*开票里程碑*/, tf.required_tools, wp.active, gp.plan_code, gp.plan_name, gp.template_plan_id from sdisd.ogg_wo_work_order17_3220 wo inner join sdisd.ogg_wo_progress17_3220 wp on wo.work_order_id = wp.work_order_id left join sdisd.ogg_wo_task_flow_node_za_3220 tfn on wo.template_id = tfn.task_flow_node_id and nvl(wo.wo_version, 0) = case when nvl(wo.wo_version, 0) > 0 then tfn.version else tfn.wo_version end and wo.project_number = tfn.project_number left join sdisd.ogg_sds_activity_t_za_3220 ac on wo.activity_lib_id = ac.activity_id left join sdisd.ogg_sds_task_flow_t_za_3220 tf on tfn.task_flow_id = tf.task_flow_id left join sdisd.ogg_du_release_t_za_3220 du /*enable_flag新增有效du的判断 lwx617215 20210116*/ on wo.du_id = du.du_id left join sdisd.ogg_gcc_plan17_3220 gp --dwx1189869 on wo.plan_id = gp.plan_id and gp.tenant_code = 'RolloutPlan' and gp.parent_plan_id = -1 and gp.enable_flag = 'Y' left join ( select r.du_id, r.task_flow_id, /*du与活动流有效标识*/ case when r.enable_flag = 'Y' and publish_flag = 'P' then 'Y' else 'N' end as du_tf_rel_enable, row_number() over( partition by r.du_id, r.task_flow_id order by r.last_update_date desc ) as rn from sdisd.ogg_rp_du_tf_release18_3220 r ) rel on wo.du_id = rel.du_id and tfn.task_flow_id = rel.task_flow_id and rel.rn = 1 left join sdisd.ogg_tpl_user_t_3220 u1 on wo.created_by = u1.user_id left join sdisd.ogg_tpl_user_t_3220 u2 on wo.last_updated_by = u2.user_id left join sdisd.ogg_tpl_user_t_3220 u3 on wp.dispatcher_user_id = u3.user_id left join sdisd.ogg_sds_activity_gap_t_za_3220 ag on wp.delay_reason_id = ag.activity_gap_id left join sdisd.ogg_tpl_lookup_item_t_3220 l1 on tfn.owner_type = l1.item_code and l1.classify_code = 'SDS_TASK_OWNER_TYPE' and l1.language = 'en_US' left join sdisd.ogg_psc_lookup_item_t_3220 l2 on wp.wo_status = l2.item_code and l2.classify_code = 'WO_STATUS_CODE' and l2.language = 'en_US' left join sdisd.ogg_psc_lookup_item_t_3220 l3 on wp.approve_status = l3.item_code and l3.classify_code = 'WORK_ORDER_APPROVE_STATUS' and l3.language = 'en_US' left join sdisd.ogg_tpl_lookup_item_t_3220 l4 on tfn.delivery_model = l4.item_code and l4.classify_code = 'SDS_TASK_ON_SITE' and l4.language = 'en_US' left join sdisd.ogg_pm_project_tree_node_3220 tn on wo.resource_id = tn.tree_id left join sdisd.ogg_pm_delivery_resource_3220 dr on tn.resource_id = dr.resource_id left join sdisd.ogg_tpl_user_t_3220 u4 on dr.user_id = u4.user_id left join sdisd.ogg_tpl_lookup_item_t_3220 l5 on dr.resouce_type = l5.item_code and l5.classify_code = 'PM_RESOURCE_TYPE' and l5.language = 'zh_CN' left join sdisd.ogg_sds_task_flow_node_za_3220 t on tfn.task_flow_node_id = t.task_flow_node_id where ( wo.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or wp.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or tfn.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 or ag.cdc_create_date >= to_timestamp('20231021', 'yyyy-mm-dd hh24:mi:ss.ff') -1 / 24 / 60 )) as t limit 10
如下图所示,该语句执行时间降为27s+,提升了语句的执行性能。
具体的performance执行计划如附件2所示。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业
2022-10-24 openGemini内核源码正式对外开源
2022-10-24 升级全新网络方案,给你低成本、高性能的裸金属体验
2022-10-24 详解Native Memory Tracking 追踪区域分析
2022-10-24 你应该知道的数仓安全:都是同名Schema惹的祸