GaussDB(DWS)性能调优：Sort+Groupagg聚集引起的性能瓶颈案例

举报
O泡果奶~ 发表于 2023/09/13 10:54:26 2023/09/13
【摘要】 本文针对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的聚集方式。
image.png
通常情况下，Sort+GroupAgg性能并不如Hashagg，此时，可以通过利用

set enable_sort = off;

或是

+set [gloabal] (enable_sort off)

来避免使用Sort+GroupAgg聚集方式。

补充：Sort+GroupAgg与Hashagg对比

Hashagg Sort+GroupAgg
执行方式 每行元组根据聚集列散列建立hash表，进行去重比较 所有元组排序后进行聚集去重操作
限制 所有聚集列支持hash散列 所有聚集列支持排序
优势 通过哈希散列比较可以快速定位到重复元组 输出结果保持有序，有利于后续有序操作
劣势 受初始散列桶个数影响较大 如果初始集较大，Sort性能较差
适合使用的情况 去重后行数较少的场景 相比Hashagg，当去重后行数和输入行数差不多，且输入行数较少时适用

image.png
从上图中可以看出，调优后语句执行时间下降为22s+，性能大大提高。从performance计划可以看出，原始SQL语句verbose计划中E-rows不准确，导致优化器选择了Sort+GroupAgg聚集方式，从而使得语句执行性能下降。

附件enable_sort-1为调优前verbose执行计划，附件enable_sort-2为调优后performance执行计划

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

评论（0）

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

全部回复

上滑加载中

设置昵称

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

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

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

确认 取消

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

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