GaussDB(DWS)性能调优:Sort+Groupagg聚集引起的性能瓶颈案例
本文分享自华为云社区《GaussDB(DWS)性能调优:Sort+Groupagg聚集引起的性能瓶颈案例》,作者: O泡果奶~ 。
本文针对SQL语句长时间执行不出来,且verbose执行计划中出现Sort+GroupAgg聚集方式的案例进行分析。
1、【问题描述】
语句执行时间过长,2300s+也无法得出结果。从verbose执行计划可以看出存在sort聚合。
2、【原始语句】
SELECT /*+ set global(agg_redistribute_enhancement on) set global (best_agg_plan 3)*/ dm_ebg_glb_kpi_sum_w_v."na_level_name", dm_ebg_glb_kpi_sum_w_v."na_level", dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_code", dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_en_name", dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_en_name", dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_cn_name", dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_cn_name", dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_code", dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_code", dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_en_name", dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_cn_name", dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_cn_name", dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_en_name", dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_code", dm_ebg_glb_kpi_sum_w_v."period_id", dm_ebg_glb_kpi_sum_w_v."year", dm_ebg_glb_kpi_sum_w_v."month", dm_ebg_glb_kpi_sum_w_v."report_category_cn_name", dm_ebg_glb_kpi_sum_w_v."report_category_en_name", dm_ebg_glb_kpi_sum_w_v."currency_code", dm_ebg_glb_kpi_sum_w_v."exchange_rate_type_cn_name", dm_ebg_glb_kpi_sum_w_v."exchange_rate_type_en_name", dm_ebg_glb_kpi_sum_w_v."report_item_code", dm_ebg_glb_kpi_sum_w_v."report_item_cn_name", dm_ebg_glb_kpi_sum_w_v."report_item_en_name", dm_ebg_glb_kpi_sum_w_v."report_item_type", dm_ebg_glb_kpi_sum_w_v."report_item_flag", dm_ebg_glb_kpi_sum_w_v."region_code", dm_ebg_glb_kpi_sum_w_v."region_cn_name", dm_ebg_glb_kpi_sum_w_v."region_en_name", dm_ebg_glb_kpi_sum_w_v."oversea_flag", dm_ebg_glb_kpi_sum_w_v."repoffice_code", dm_ebg_glb_kpi_sum_w_v."repoffice_cn_name", dm_ebg_glb_kpi_sum_w_v."repoffice_en_name", dm_ebg_glb_kpi_sum_w_v."ebg_focus_cn_name", dm_ebg_glb_kpi_sum_w_v."ebg_focus_en_name", dm_ebg_glb_kpi_sum_w_v."lv0_prod_rnd_team_code", dm_ebg_glb_kpi_sum_w_v."lv0_prod_rd_team_cn_name", dm_ebg_glb_kpi_sum_w_v."lv0_prod_rd_team_en_name", dm_ebg_glb_kpi_sum_w_v."lv1_prod_rnd_team_code", dm_ebg_glb_kpi_sum_w_v."lv1_prod_rd_team_cn_name", dm_ebg_glb_kpi_sum_w_v."lv1_prod_rd_team_en_name", dm_ebg_glb_kpi_sum_w_v."lv2_prod_rnd_team_code", dm_ebg_glb_kpi_sum_w_v."lv2_prod_rd_team_cn_name", dm_ebg_glb_kpi_sum_w_v."lv2_prod_rd_team_en_name", dm_ebg_glb_kpi_sum_w_v."lv3_prod_rnd_team_code", dm_ebg_glb_kpi_sum_w_v."lv3_prod_rd_team_cn_name", dm_ebg_glb_kpi_sum_w_v."lv3_prod_rd_team_en_name", dm_ebg_glb_kpi_sum_w_v."named_account_flag", dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_code", dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_cn_name", dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_en_name", dm_ebg_glb_kpi_sum_w_v."industry_class_code", dm_ebg_glb_kpi_sum_w_v."industry_class_cn_name", dm_ebg_glb_kpi_sum_w_v."industry_class_en_name", dm_ebg_glb_kpi_sum_w_v."sub_industry_class_code", dm_ebg_glb_kpi_sum_w_v."sub_industry_class_cn_name", dm_ebg_glb_kpi_sum_w_v."sub_industry_class_en_name", dm_ebg_glb_kpi_sum_w_v."focus_industry_cn_name", dm_ebg_glb_kpi_sum_w_v."focus_industry_en_name", dm_ebg_glb_kpi_sum_w_v."sales_mode_code", dm_ebg_glb_kpi_sum_w_v."sales_mode_cn_name", dm_ebg_glb_kpi_sum_w_v."sales_mode_en_name", SUM(dm_ebg_glb_kpi_sum_w_v."ptd_amt") as "ptd_amt", SUM(dm_ebg_glb_kpi_sum_w_v."py_ptd_amt") as "py_ptd_amt", SUM(dm_ebg_glb_kpi_sum_w_v."pp_ptd_amt") as "pp_ptd_amt", SUM(dm_ebg_glb_kpi_sum_w_v."qtd_amt") as "qtd_amt", SUM(dm_ebg_glb_kpi_sum_w_v."py_qtd_amt") as "py_qtd_amt", SUM(dm_ebg_glb_kpi_sum_w_v."pp_qtd_amt") as "pp_qtd_amt", SUM(dm_ebg_glb_kpi_sum_w_v."ytd_amt") as "ytd_amt", SUM(dm_ebg_glb_kpi_sum_w_v."py_ytd_amt") as "py_ytd_amt", SUM(dm_ebg_glb_kpi_sum_w_v."py_all_ytd_amt") as "py_all_ytd_amt", SUM(dm_ebg_glb_kpi_sum_w_v."end_bal_amt") as "end_bal_amt", SUM(dm_ebg_glb_kpi_sum_w_v."cp_open_bal_amt") as "cp_open_bal_amt", SUM(dm_ebg_glb_kpi_sum_w_v."pq_end_bal_amt") as "pq_end_bal_amt", SUM(dm_ebg_glb_kpi_sum_w_v."cy_open_bal_amt") as "cy_open_bal_amt", SUM(dm_ebg_glb_kpi_sum_w_v."py_end_bal_amt") as "py_end_bal_amt" FROM fin_dmr_ebgdis.dm_ebg_glb_kpi_sum_w_v where 1 = 1 and 1 = 1 AND dm_ebg_glb_kpi_sum_w_v."period_id" = 202302 group by dm_ebg_glb_kpi_sum_w_v."na_level_name", dm_ebg_glb_kpi_sum_w_v."na_level", dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_code", dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_en_name", dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_en_name", dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_cn_name", dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_cn_name", dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_code", dm_ebg_glb_kpi_sum_w_v."sales_lv1_prod_list_code", dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_en_name", dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_cn_name", dm_ebg_glb_kpi_sum_w_v."sales_lv3_prod_list_cn_name", dm_ebg_glb_kpi_sum_w_v."sales_lv0_prod_list_en_name", dm_ebg_glb_kpi_sum_w_v."sales_lv2_prod_list_code", dm_ebg_glb_kpi_sum_w_v."period_id", dm_ebg_glb_kpi_sum_w_v."year", dm_ebg_glb_kpi_sum_w_v."month", dm_ebg_glb_kpi_sum_w_v."report_category_cn_name", dm_ebg_glb_kpi_sum_w_v."report_category_en_name", dm_ebg_glb_kpi_sum_w_v."currency_code", dm_ebg_glb_kpi_sum_w_v."exchange_rate_type_cn_name", dm_ebg_glb_kpi_sum_w_v."exchange_rate_type_en_name", dm_ebg_glb_kpi_sum_w_v."report_item_code", dm_ebg_glb_kpi_sum_w_v."report_item_cn_name", dm_ebg_glb_kpi_sum_w_v."report_item_en_name", dm_ebg_glb_kpi_sum_w_v."report_item_type", dm_ebg_glb_kpi_sum_w_v."report_item_flag", dm_ebg_glb_kpi_sum_w_v."region_code", dm_ebg_glb_kpi_sum_w_v."region_cn_name", dm_ebg_glb_kpi_sum_w_v."region_en_name", dm_ebg_glb_kpi_sum_w_v."oversea_flag", dm_ebg_glb_kpi_sum_w_v."repoffice_code", dm_ebg_glb_kpi_sum_w_v."repoffice_cn_name", dm_ebg_glb_kpi_sum_w_v."repoffice_en_name", dm_ebg_glb_kpi_sum_w_v."ebg_focus_cn_name", dm_ebg_glb_kpi_sum_w_v."ebg_focus_en_name", dm_ebg_glb_kpi_sum_w_v."lv0_prod_rnd_team_code", dm_ebg_glb_kpi_sum_w_v."lv0_prod_rd_team_cn_name", dm_ebg_glb_kpi_sum_w_v."lv0_prod_rd_team_en_name", dm_ebg_glb_kpi_sum_w_v."lv1_prod_rnd_team_code", dm_ebg_glb_kpi_sum_w_v."lv1_prod_rd_team_cn_name", dm_ebg_glb_kpi_sum_w_v."lv1_prod_rd_team_en_name", dm_ebg_glb_kpi_sum_w_v."lv2_prod_rnd_team_code", dm_ebg_glb_kpi_sum_w_v."lv2_prod_rd_team_cn_name", dm_ebg_glb_kpi_sum_w_v."lv2_prod_rd_team_en_name", dm_ebg_glb_kpi_sum_w_v."lv3_prod_rnd_team_code", dm_ebg_glb_kpi_sum_w_v."lv3_prod_rd_team_cn_name", dm_ebg_glb_kpi_sum_w_v."lv3_prod_rd_team_en_name", dm_ebg_glb_kpi_sum_w_v."named_account_flag", dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_code", dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_cn_name", dm_ebg_glb_kpi_sum_w_v."domtc_entps_indu_class_en_name", dm_ebg_glb_kpi_sum_w_v."industry_class_code", dm_ebg_glb_kpi_sum_w_v."industry_class_cn_name", dm_ebg_glb_kpi_sum_w_v."industry_class_en_name", dm_ebg_glb_kpi_sum_w_v."sub_industry_class_code", dm_ebg_glb_kpi_sum_w_v."sub_industry_class_cn_name", dm_ebg_glb_kpi_sum_w_v."sub_industry_class_en_name", dm_ebg_glb_kpi_sum_w_v."focus_industry_cn_name", dm_ebg_glb_kpi_sum_w_v."focus_industry_en_name", dm_ebg_glb_kpi_sum_w_v."sales_mode_code", dm_ebg_glb_kpi_sum_w_v."sales_mode_cn_name", dm_ebg_glb_kpi_sum_w_v."sales_mode_en_name" limit 200
3、【性能分析】
由于语句长时间无法执行完毕,通过其verbose执行计划中的E-rows可以看出,由于较小且去重后行数变化不大,优化器采用了Sort+GroupAgg的聚集方式。
通常情况下,Sort+GroupAgg性能并不如Hashagg,此时,可以通过利用
set enable_sort = off;
或是
+set [gloabal] (enable_sort off)
来避免使用Sort+GroupAgg聚集方式。
补充:Sort+GroupAgg与Hashagg对比
Hashagg |
Sort+GroupAgg | |
---|---|---|
执行方式 |
每行元组根据聚集列散列建立hash表,进行去重比较 |
所有元组排序后进行聚集去重操作 |
限制 |
所有聚集列支持hash散列 |
所有聚集列支持排序 |
优势 |
通过哈希散列比较可以快速定位到重复元组 |
输出结果保持有序,有利于后续有序操作 |
劣势 |
受初始散列桶个数影响较大 |
如果初始集较大,Sort性能较差 |
适合使用的情况 |
去重后行数较少的场景 |
相比Hashagg,当去重后行数和输入行数差不多,且输入行数较少时适用 |
从上图中可以看出,调优后语句执行时间下降为22s+,性能大大提高。从performance计划可以看出,原始SQL语句verbose计划中E-rows不准确,导致优化器选择了Sort+GroupAgg聚集方式,从而使得语句执行性能下降。
附件enable_sort-1为调优前verbose执行计划,附件enable_sort-2为调优后performance执行计划
- 附件:enable_sort-1.txt688.04KB
- 附件:enable_sort-2.txt1.57MB
热门相关:豪门蜜爱:独宠天后小萌妻 异能特工:军火皇后 隐婚试爱:娇妻,好甜! 名门盛婚:首席,别来无恙! 慕少,你老婆又重生了