GaussDB(DWS)性能调优：关联时计算倾斜优化案例

举报
O泡果奶~ 发表于 2023/08/29 16:54:15 2023/08/29
【摘要】 表关联时，由于关联条件重复值过多，会发生运行倾斜的问题，进而影响语句执行性能。该案例主要针对这一类问题利用skew hint进行分析解决

1、【问题描述】

语句执行过程中，表关联时存在运行倾斜的问题，进而影响语句执行性能

2、【原始语句】

SET enable_nodegroup_debug = ON;
SET expected_computing_nodegroup = "LC_B";
SELECT
S.c0_cy_open_bal_amt,
S.c0_end_bal_amt,
S.c0_h1_amt,
S.c0_h1_end_bal_amt,
S.c0_ly_end_bal_amt,
S.c0_pp_cy_open_bal_amt,
S.c0_pp_end_bal_amt,
S.c0_pp_ptd_amt,
S.c0_pp_qtd_amt,
S.c0_pp_ytd_amt,
S.c0_ptd_amt,
S.c0_py_all_h1_amt,
S.c0_py_all_h1_end_bal_amt,
S.c0_py_all_q1_q3_amt,
S.c0_py_all_q1_q3_end_bal_amt,
S.c0_py_all_qtd_amt,
S.c0_py_all_ytd_amt,
S.c0_py_cy_open_bal_amt,
S.c0_py_end_bal_amt,
S.c0_py_ly_end_bal_amt,
S.c0_py_ptd_amt,
S.c0_py_qtd_amt,
S.c0_py_ytd_amt,
S.c0_q1_q3_amt,
S.c0_q1_q3_end_bal_amt,
S.c0_qtd_amt,
S.c0_ytd_amt,
S.c1_cy_open_bal_amt,
S.c1_end_bal_amt,
S.c1_h1_amt,
S.c1_h1_end_bal_amt,
S.c1_ly_end_bal_amt,
S.c1_pp_cy_open_bal_amt,
S.c1_pp_end_bal_amt,
S.c1_pp_ptd_amt,
S.c1_pp_qtd_amt,
S.c1_pp_ytd_amt,
S.c1_ptd_amt,
S.c1_py_all_h1_amt,
S.c1_py_all_h1_end_bal_amt,
S.c1_py_all_q1_q3_amt,
S.c1_py_all_q1_q3_end_bal_amt,
S.c1_py_all_qtd_amt,
S.c1_py_all_ytd_amt,
S.c1_py_cy_open_bal_amt,
S.c1_py_end_bal_amt,
S.c1_py_ly_end_bal_amt,
S.c1_py_ptd_amt,
S.c1_py_qtd_amt,
S.c1_py_ytd_amt,
S.c1_q1_q3_amt,
S.c1_q1_q3_end_bal_amt,
S.c1_qtd_amt,
S.c1_ytd_amt,
S.c2_cy_open_bal_amt,
S.c2_end_bal_amt,
S.c2_h1_amt,
S.c2_h1_end_bal_amt,
S.c2_ly_end_bal_amt,
S.c2_pp_cy_open_bal_amt,
S.c2_pp_end_bal_amt,
S.c2_pp_ptd_amt,
S.c2_pp_qtd_amt,
S.c2_pp_ytd_amt,
S.c2_ptd_amt,
S.c2_py_all_h1_amt,
S.c2_py_all_h1_end_bal_amt,
S.c2_py_all_q1_q3_amt,
S.c2_py_all_q1_q3_end_bal_amt,
S.c2_py_all_qtd_amt,
S.c2_py_all_ytd_amt,
S.c2_py_cy_open_bal_amt,
S.c2_py_end_bal_amt,
S.c2_py_ly_end_bal_amt,
S.c2_py_ptd_amt,
S.c2_py_qtd_amt,
S.c2_py_ytd_amt,
S.c2_q1_q3_amt,
S.c2_q1_q3_end_bal_amt,
S.c2_qtd_amt,
S.c2_ytd_amt,
S.currency_code,
S.ebg_chinese_funded_type,
S.ebg_focus_type,
S.ebg_psp_repoffice_flag,
S.FLAG,
S.focus_industry_cn_name,
S.focus_industry_code,
S.focus_industry_en_name,
S.industry_class_cn_name,
S.industry_class_code,
S.industry_class_en_name,
S.last_update_date,
S.overseas_flag,
S.period_id,
S.raito,
S.region_cn_name,
S.region_code,
S.region_en_name,
S.repoffice_cn_name,
S.repoffice_code,
S.repoffice_en_name,
S.report_item_cn_alias,
S.report_item_code,
S.report_item_en_alias,
S.report_item_type,
S.scenario_code,
S.sub_industry_class_cn_name,
S.sub_industry_class_code,
S.sub_industry_class_en_name,
S.subject_area_id 
FROM
	(
	SELECT
		SQL1.PERIOD_ID,
		SQL1.SUBJECT_AREA_ID,
		SQL1.CURRENCY_CODE,
		SQL1.REPORT_ITEM_CODE,
		SQL1.REPORT_ITEM_CN_ALIAS,
		SQL1.REPORT_ITEM_EN_ALIAS,
		SQL1.REPORT_ITEM_TYPE,
		SQL1.RAITO,
		SQL1.FLAG,
		SQL1.REGION_CODE,
		SQL1.REGION_CN_NAME,
		SQL1.REGION_EN_NAME,
		SQL1.REPOFFICE_CODE,
		SQL1.REPOFFICE_CN_NAME,
		SQL1.REPOFFICE_EN_NAME,
		SQL1.EBG_CHINESE_FUNDED_TYPE,
		SQL1.EBG_FOCUS_TYPE,
		SQL1.EBG_PSP_REPOFFICE_FLAG,
		SQL1.OVERSEAS_FLAG,
		SQL1.INDUSTRY_CLASS_CODE,
		SQL1.INDUSTRY_CLASS_CN_NAME,
		SQL1.INDUSTRY_CLASS_EN_NAME,
		SQL1.SUB_INDUSTRY_CLASS_CODE,
		SQL1.SUB_INDUSTRY_CLASS_CN_NAME,
		SQL1.SUB_INDUSTRY_CLASS_EN_NAME,
		SQL1.FOCUS_INDUSTRY_CODE,
		SQL1.FOCUS_INDUSTRY_CN_NAME,
		SQL1.FOCUS_INDUSTRY_EN_NAME,
		SQL1.SCENARIO_CODE,
		SYSDATE AS LAST_UPDATE_DATE,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PTD_AMT END ) AS C0_PTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PTD_AMT END ) AS C1_PTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PTD_AMT END ) AS C2_PTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.QTD_AMT END ) AS C0_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.QTD_AMT END ) AS C1_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.QTD_AMT END ) AS C2_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.YTD_AMT END ) AS C0_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.YTD_AMT END ) AS C1_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.YTD_AMT END ) AS C2_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.Q1_Q3_AMT END ) AS C0_Q1_Q3_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.Q1_Q3_AMT END ) AS C1_Q1_Q3_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.Q1_Q3_AMT END ) AS C2_Q1_Q3_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.H1_AMT END ) AS C0_H1_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.H1_AMT END ) AS C1_H1_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.H1_AMT END ) AS C2_H1_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_PTD_AMT END ) AS C0_PY_PTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_PTD_AMT END ) AS C1_PY_PTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_PTD_AMT END ) AS C2_PY_PTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_QTD_AMT END ) AS C0_PY_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_QTD_AMT END ) AS C1_PY_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_QTD_AMT END ) AS C2_PY_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_YTD_AMT END ) AS C0_PY_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_YTD_AMT END ) AS C1_PY_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_YTD_AMT END ) AS C2_PY_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_ALL_QTD_AMT END ) AS C0_PY_ALL_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_ALL_QTD_AMT END ) AS C1_PY_ALL_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_ALL_QTD_AMT END ) AS C2_PY_ALL_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_ALL_YTD_AMT END ) AS C0_PY_ALL_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_ALL_YTD_AMT END ) AS C1_PY_ALL_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_ALL_YTD_AMT END ) AS C2_PY_ALL_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_ALL_Q1_Q3_AMT END ) AS C0_PY_ALL_Q1_Q3_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_ALL_Q1_Q3_AMT END ) AS C1_PY_ALL_Q1_Q3_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_ALL_Q1_Q3_AMT END ) AS C2_PY_ALL_Q1_Q3_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_ALL_H1_AMT END ) AS C0_PY_ALL_H1_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_ALL_H1_AMT END ) AS C1_PY_ALL_H1_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_ALL_H1_AMT END ) AS C2_PY_ALL_H1_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PP_PTD_AMT END ) AS C0_PP_PTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PP_PTD_AMT END ) AS C1_PP_PTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PP_PTD_AMT END ) AS C2_PP_PTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PP_QTD_AMT END ) AS C0_PP_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PP_QTD_AMT END ) AS C1_PP_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PP_QTD_AMT END ) AS C2_PP_QTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PP_YTD_AMT END ) AS C0_PP_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PP_YTD_AMT END ) AS C1_PP_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PP_YTD_AMT END ) AS C2_PP_YTD_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.CY_OPEN_BAL_AMT END ) AS C0_CY_OPEN_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.CY_OPEN_BAL_AMT END ) AS C1_CY_OPEN_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.CY_OPEN_BAL_AMT END ) AS C2_CY_OPEN_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_CY_OPEN_BAL_AMT END ) AS C0_PY_CY_OPEN_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_CY_OPEN_BAL_AMT END ) AS C1_PY_CY_OPEN_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_CY_OPEN_BAL_AMT END ) AS C2_PY_CY_OPEN_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_END_BAL_AMT END ) AS C0_PY_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_END_BAL_AMT END ) AS C1_PY_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_END_BAL_AMT END ) AS C2_PY_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.END_BAL_AMT END ) AS C0_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.END_BAL_AMT END ) AS C1_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.END_BAL_AMT END ) AS C2_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.LY_END_BAL_AMT END ) AS C0_LY_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.LY_END_BAL_AMT END ) AS C1_LY_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.LY_END_BAL_AMT END ) AS C2_LY_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_LY_END_BAL_AMT END ) AS C0_PY_LY_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_LY_END_BAL_AMT END ) AS C1_PY_LY_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_LY_END_BAL_AMT END ) AS C2_PY_LY_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PP_CY_OPEN_BAL_AMT END ) AS C0_PP_CY_OPEN_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PP_CY_OPEN_BAL_AMT END ) AS C1_PP_CY_OPEN_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PP_CY_OPEN_BAL_AMT END ) AS C2_PP_CY_OPEN_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PP_END_BAL_AMT END ) AS C0_PP_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PP_END_BAL_AMT END ) AS C1_PP_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PP_END_BAL_AMT END ) AS C2_PP_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_ALL_H1_END_BAL_AMT END ) AS C0_PY_ALL_H1_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_ALL_H1_END_BAL_AMT END ) AS C1_PY_ALL_H1_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_ALL_H1_END_BAL_AMT END ) AS C2_PY_ALL_H1_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.H1_END_BAL_AMT END ) AS C0_H1_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.H1_END_BAL_AMT END ) AS C1_H1_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.H1_END_BAL_AMT END ) AS C2_H1_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.Q1_Q3_END_BAL_AMT END ) AS C0_Q1_Q3_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.Q1_Q3_END_BAL_AMT END ) AS C1_Q1_Q3_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.Q1_Q3_END_BAL_AMT END ) AS C2_Q1_Q3_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 0 THEN SQL1.PY_ALL_Q1_Q3_END_BAL_AMT END ) AS C0_PY_ALL_Q1_Q3_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 1 THEN SQL1.PY_ALL_Q1_Q3_END_BAL_AMT END ) AS C1_PY_ALL_Q1_Q3_END_BAL_AMT,
		SUM ( CASE WHEN SQL1.REPORT_ITEM_FLAG = 2 THEN SQL1.PY_ALL_Q1_Q3_END_BAL_AMT END ) AS C2_PY_ALL_Q1_Q3_END_BAL_AMT 
	FROM
		(
		SELECT /*+ set (agg_redistribute_enhancement on) set (best_agg_plan 3)*/
			F.PERIOD_ID,
			F.SUBJECT_AREA_ID,
			F.CURRENCY_CODE,
			ITEM.REPORT_ITEM_CODE,
			ITEM.REPORT_ITEM_CN_ALIAS,
			ITEM.REPORT_ITEM_EN_ALIAS,
			ITEM.REPORT_ITEM_TYPE,
			ITEM.REPORT_ITEM_FLAG,
			ITEM.RAITO,
			ITEM.INC_BAL_TYPE,
		CASE
				
				WHEN ITEM.REPORT_ITEM_CODE = I.REPORT_ITEM_CODE THEN
				I.FLAG ELSE'SNULL' 
			END AS FLAG,
			REG.REGION_CODE,
			REG.REGION_CN_NAME,
			REG.REGION_EN_NAME,
			REG.REPOFFICE_CODE,
			REG.REPOFFICE_CN_NAME,
			REG.REPOFFICE_EN_NAME,
			F.EBG_CHINESE_FUNDED_TYPE,
		CASE
				
				WHEN F.EBG_FOCUS_TYPE = '代表处维度' THEN
				'Y' ELSE'N' 
			END AS EBG_FOCUS_TYPE,
			F.EBG_PSP_REPOFFICE_FLAG,
			F.OVERSEA_FLAG AS OVERSEAS_FLAG,
			F.INDUSTRY_CLASS_CODE,
			F.INDUSTRY_CLASS_CN_NAME,
			F.INDUSTRY_CLASS_EN_NAME,
			F.SUB_INDUSTRY_CLASS_CODE,
			F.SUB_INDUSTRY_CLASS_CN_NAME,
			F.SUB_INDUSTRY_CLASS_EN_NAME,
			F.FOCUS_INDUSTRY_CODE,
			F.FOCUS_INDUSTRY_CN_NAME,
			F.FOCUS_INDUSTRY_EN_NAME,
			NULL AS SCENARIO_CODE,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.END_BAL_AMT ELSE F.PTD_AMT END ) AS PTD_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.END_BAL_AMT ELSE F.QTD_AMT END ) AS QTD_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.END_BAL_AMT ELSE F.YTD_AMT END ) AS YTD_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.Q1_Q3_END_BAL_AMT ELSE F.Q1_Q3_AMT END ) AS Q1_Q3_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.H1_END_BAL_AMT ELSE F.H1_AMT END ) AS H1_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.PY_END_BAL_AMT ELSE F.PY_PTD_AMT END ) AS PY_PTD_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.PY_END_BAL_AMT ELSE F.PY_QTD_AMT END ) AS PY_QTD_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.PY_END_BAL_AMT ELSE F.PY_YTD_AMT END ) AS PY_YTD_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.PY_END_BAL_AMT ELSE F.PY_ALL_QTD_AMT END ) AS PY_ALL_QTD_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.LY_END_BAL_AMT ELSE F.PY_ALL_YTD_AMT END ) AS PY_ALL_YTD_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.PY_ALL_Q1_Q3_END_BAL_AMT ELSE F.PY_ALL_Q1_Q3_AMT END ) AS PY_ALL_Q1_Q3_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.PY_ALL_H1_END_BAL_AMT ELSE F.PY_ALL_H1_AMT END ) AS PY_ALL_H1_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.PP_END_BAL_AMT ELSE F.PP_PTD_AMT END ) AS PP_PTD_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.PP_END_BAL_AMT ELSE F.PP_QTD_AMT END ) AS PP_QTD_AMT,
			SUM ( CASE WHEN ITEM.INC_BAL_TYPE = 'BALANCE' THEN F.PP_END_BAL_AMT ELSE F.PP_YTD_AMT END ) AS PP_YTD_AMT,
			SUM ( F.CY_OPEN_BAL_AMT ) AS CY_OPEN_BAL_AMT,
			SUM ( F.END_BAL_AMT ) AS END_BAL_AMT,
			SUM ( F.PY_END_BAL_AMT ) AS PY_END_BAL_AMT,
			SUM ( F.PY_CY_OPEN_BAL_AMT ) AS PY_CY_OPEN_BAL_AMT,
			SUM ( F.CP_OPEN_BAL_AMT ) AS CP_OPEN_BAL_AMT,
			SUM ( F.LY_END_BAL_AMT ) AS LY_END_BAL_AMT,
			SUM ( F.PY_LY_END_BAL_AMT ) AS PY_LY_END_BAL_AMT,
			SUM ( F.PP_END_BAL_AMT ) AS PP_END_BAL_AMT,
			SUM ( F.PY_ALL_H1_END_BAL_AMT ) AS PY_ALL_H1_END_BAL_AMT,
			SUM ( F.H1_END_BAL_AMT ) AS H1_END_BAL_AMT,
			SUM ( F.Q1_Q3_END_BAL_AMT ) AS Q1_Q3_END_BAL_AMT,
			SUM ( F.PY_ALL_Q1_Q3_END_BAL_AMT ) AS PY_ALL_Q1_Q3_END_BAL_AMT,
			SUM ( CASE WHEN SUBSTR( F.PERIOD_ID, 5, 2 ) = '01' THEN F.PY_CY_OPEN_BAL_AMT ELSE F.CY_OPEN_BAL_AMT END ) AS PP_CY_OPEN_BAL_AMT,
			SUM ( CASE WHEN SUBSTR( F.PERIOD_ID, 5, 2 ) = '01' THEN F.PY_LY_END_BAL_AMT ELSE F.LY_END_BAL_AMT END ) AS PP_LY_END_BAL_AMT 
		FROM
			FIN_DMR_EBG.DM_EBG_REG_KPI_SUM_W_V F
			INNER JOIN FIN_DMR_EBG.DM_DIM_SALE_HRMS_REGION_D REG ON F.REGION_ORG_ID = REG.REGION_ORG_ID
			INNER JOIN DMDIM_W.DM_DIM_REPORT_ITEM_EXT_D ITEM ON F.REPORT_ITEM_ID = ITEM.REPORT_ITEM_ID
			INNER JOIN FIN_DMR_EBG.APD_DM_EBG_RI_DIS_MAP_T APD ON ITEM.REPORT_ITEM_CODE = APD.REPORT_ITEM_CODE 
			AND APD.DATASET_CODE IN ( 'EIOC_REG' ) /*宽表指标过滤*/
			LEFT JOIN FIN_DMR_EBG.APD_DM_DSO_ITO_ITEM_T I ON ITEM.REPORT_ITEM_CODE = I.REPORT_ITEM_CODE 
		WHERE
			1 = 1 
			AND F.PERIOD_ID = 202308 
			AND F.SUBJECT_AREA_ID = 1 
		GROUP BY
			F.PERIOD_ID,
			F.SUBJECT_AREA_ID,
			F.CURRENCY_CODE,
			ITEM.REPORT_ITEM_CODE,
			ITEM.REPORT_ITEM_CN_ALIAS,
			ITEM.REPORT_ITEM_EN_ALIAS,
			ITEM.REPORT_ITEM_TYPE,
			ITEM.REPORT_ITEM_FLAG,
			ITEM.RAITO,
			ITEM.INC_BAL_TYPE,
		CASE
				
				WHEN ITEM.REPORT_ITEM_CODE = I.REPORT_ITEM_CODE THEN
				I.FLAG ELSE'SNULL' 
			END,
			REG.REGION_CODE,
			REG.REGION_CN_NAME,
			REG.REGION_EN_NAME,
			REG.REPOFFICE_CODE,
			REG.REPOFFICE_CN_NAME,
			REG.REPOFFICE_EN_NAME,
			F.EBG_CHINESE_FUNDED_TYPE,
		CASE
				
				WHEN F.EBG_FOCUS_TYPE = '代表处维度' THEN
				'Y' ELSE'N' 
			END,
			F.EBG_PSP_REPOFFICE_FLAG,
			F.OVERSEA_FLAG,
			F.INDUSTRY_CLASS_CODE,
			F.INDUSTRY_CLASS_CN_NAME,
			F.INDUSTRY_CLASS_EN_NAME,
			F.SUB_INDUSTRY_CLASS_CODE,
			F.SUB_INDUSTRY_CLASS_CN_NAME,
			F.SUB_INDUSTRY_CLASS_EN_NAME,
			F.FOCUS_INDUSTRY_CODE,
			F.FOCUS_INDUSTRY_CN_NAME,
			F.FOCUS_INDUSTRY_EN_NAME 
		) SQL1 
	GROUP BY
		SQL1.PERIOD_ID,
		SQL1.SUBJECT_AREA_ID,
		SQL1.CURRENCY_CODE,
		SQL1.REPORT_ITEM_CODE,
		SQL1.REPORT_ITEM_CN_ALIAS,
		SQL1.REPORT_ITEM_EN_ALIAS,
		SQL1.REPORT_ITEM_TYPE,
		SQL1.REPORT_ITEM_FLAG,
		SQL1.RAITO,
		SQL1.INC_BAL_TYPE,
		SQL1.FLAG,
		SQL1.REGION_CODE,
		SQL1.REGION_CN_NAME,
		SQL1.REGION_EN_NAME,
		SQL1.REPOFFICE_CODE,
		SQL1.REPOFFICE_CN_NAME,
		SQL1.REPOFFICE_EN_NAME,
		SQL1.EBG_CHINESE_FUNDED_TYPE,
		SQL1.EBG_FOCUS_TYPE,
		SQL1.EBG_PSP_REPOFFICE_FLAG,
		SQL1.OVERSEAS_FLAG,
		SQL1.INDUSTRY_CLASS_CODE,
		SQL1.INDUSTRY_CLASS_CN_NAME,
		SQL1.INDUSTRY_CLASS_EN_NAME,
		SQL1.SUB_INDUSTRY_CLASS_CODE,
		SQL1.SUB_INDUSTRY_CLASS_CN_NAME,
		SQL1.SUB_INDUSTRY_CLASS_EN_NAME,
		SQL1.FOCUS_INDUSTRY_CODE,
		SQL1.FOCUS_INDUSTRY_CN_NAME,
		SQL1.FOCUS_INDUSTRY_EN_NAME,
	SQL1.SCENARIO_CODE 
	) S

3、【性能分析】

image.png
从图中可以看出，语句执行过程中，在表关联时存在严重的运行倾斜问题，导致语句执行时间过长。
由于该表关联操作位于视图 FIN_DMR_EBG.DM_EBG_REG_KPI_SUM_W_V 中，因为需要利用语句

select pg_get_viewdef('FIN_DMR_EBG.DM_EBG_REG_KPI_SUM_W_V')

获取该视图定义（具体SQL语句如附件所示），关联逻辑如下图所示
image.png
该SQL语句存在两个性能问题：

  1. rel作为一个维度表，并没有利用peroid_id进行过滤，导致优化器将该条件作为关联条件与主表f进行关联，降低了语句的执行性能
  2. f.report_item_id = rel.child_rpt_item_id 这一条件存在过多的重复值，导致关联时发生运行倾斜

解决办法：

  • 问题1：增加rel.peroid_id = 202308，与主表中f.peroid_id = 202308 保持一直
  • 问题2：判断主表f中report_item_id存在重复较多的10个value，然后利用skew hint进行运行倾斜避免。
    skew hint作用：指明查询运行时重分布过程中存在倾斜的重分布键和倾斜值，针对Join和HashAgg运算中的重分布进行优化
    skew hint使用方法： skew(表名(字段名)(重复值))
    具体使用方法可以看 https://support.huaweicloud.com/intl/zh-cn/performance-dws/dws_10_0029.html

重复值判断方法：

select count(1),report_item_id from fin_dmr_ebg.dm_ebg_reg_kpi_sum_w_f
where 
group by 2
order by 1 desc

优化后，如下图所示，从执行计划中看出，运行倾斜问题得到了缓解
image.png
这两个问题解决后，语句执行性能变好，由992.835s变为173.7s，
其中，优化前后完整的performance执行计划如附件所示。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
应用性能调优
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入