本文分享自華為雲社區《GaussDB(DWS)性能調優：實時場景下表行數估算不准確引起的的性能瓶頸問題案例》，作者： O泡果奶~。 本文針對實時場景下SQL語句因表行數估算不准確而導致語句執行超時報錯的案例進行分析。 1、【問題描述】 實時場景下，select查詢語句執行時間過長，該語句verbos ...
本文分享自華為雲社區《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 <
