GaussDB(DWS)性能调优：执行计划出现预期外发散——hint无法调整就试试调整SQL——案例分析
【摘要】 在本案例中，在无法通过hint消除的情景下，通过调整SQL消除了预期外的Nest Loop，减轻了Sort和Sort Agg算子的瓶颈。
1、【问题描述】
表关联慢，看计划出现Nest Loop做笛卡尔积，实际业务逻辑并不需要做发散；hint无法消去。
2、【原始SQL】
SELECT
IFNULL(T.mtg_qty, 0) AS mtg_qty,
IFNULL(T.mtg_completed_qty, 0) AS mtg_completed_qty,
IFNULL(T.lastmon_notConvened_mtg_qty, 0) AS lastmon_notConvened_mtg_qty,
IFNULL(T.mon_notConvened_mtg_qty, 0) AS mon_notConvened_mtg_qty,
CASE
WHEN IFNULL(T.mtg_qty, 0) = 0 THEN 0
WHEN T.mtg_completed_qty > T.mtg_qty THEN 100
ELSE ROUND(T.mtg_completed_qty / T.mtg_qty * 100, 2)
END AS Execution_rate --客户关系分析会执行率（月度累计）
,CASE
WHEN 'REGION' IN ('REGION') THEN T.REGION_CODE
END AS REGION_CODE,CASE
WHEN 'REGION' IN ('REGION') THEN T.REGION_CN_NAME
END AS REGION_CN_NAME,CASE
WHEN 'REGION' IN ('REGION') THEN T.REGION_EN_NAME
END AS REGION_EN_NAME,CASE
WHEN 'REPOFFICE' IN ('REGION') THEN T.REPOFFICE_CODE
END AS REPOFFICE_CODE,CASE
WHEN 'REPOFFICE' IN ('REGION') THEN T.REPOFFICE_CN_NAME
END AS REPOFFICE_CN_NAME,CASE
WHEN 'REPOFFICE' IN ('REGION') THEN T.REPOFFICE_EN_NAME
END AS REPOFFICE_EN_NAME,CASE
WHEN 'OFFICE' IN ('REGION') THEN T.OFFICE_CODE
END AS OFFICE_CODE,CASE
WHEN 'OFFICE' IN ('REGION') THEN T.OFFICE_CN_NAME
END AS OFFICE_CN_NAME,CASE
WHEN 'OFFICE' IN ('REGION') THEN T.OFFICE_EN_NAME
END AS OFFICE_EN_NAME,CASE
WHEN 'CUSTCATG' IN ('REGION') THEN T.REGION_CUSTCATG_CODE
END AS REGION_CUSTCATG_CODE,CASE
WHEN 'CUSTCATG' IN ('REGION') THEN T.REGION_CUSTCATG_CN_NAME
END AS REGION_CUSTCATG_CN_NAME,CASE
WHEN 'CUSTCATG' IN ('REGION') THEN T.REGION_CUSTCATG_EN_NAME
END AS REGION_CUSTCATG_EN_NAME,CASE
WHEN 'KAD' IN ('REGION') THEN T.KAD_CODE
END AS KAD_CODE,CASE
WHEN 'KAD' IN ('REGION') THEN T.KAD_NAME_CN
END AS KAD_NAME_CN,CASE
WHEN 'KAD' IN ('REGION') THEN T.KAD_NAME_EN
END AS KAD_NAME_EN,CASE
WHEN 'KAD1' IN ('REGION') THEN T.KAD_LV1_CODE
END AS KAD_LV1_CODE,CASE
WHEN 'KAD1' IN ('REGION') THEN T.KAD_LV1_NAME_CN
END AS KAD_LV1_NAME_CN,CASE
WHEN 'KAD1' IN ('REGION') THEN T.KAD_LV1_NAME_EN
END AS KAD_LV1_NAME_EN,CASE
WHEN 'KAD2' IN ('REGION') THEN T.KAD_LV2_CODE
END AS KAD_LV2_CODE,CASE
WHEN 'KAD2' IN ('REGION') THEN T.KAD_LV2_NAME_CN
END AS KAD_LV2_NAME_CN,CASE
WHEN 'KAD2' IN ('REGION') THEN T.KAD_LV2_NAME_EN
END AS KAD_LV2_NAME_EN,CASE
WHEN 'KAD3' IN ('REGION') THEN T.KAD_LV3_CODE
END AS KAD_LV3_CODE,CASE
WHEN 'KAD3' IN ('REGION') THEN T.KAD_LV3_NAME_CN
END AS KAD_LV3_NAME_CN,CASE
WHEN 'KAD3' IN ('REGION') THEN T.KAD_LV3_NAME_EN
END AS KAD_LV3_NAME_EN,CASE
WHEN 'KAD4' IN ('REGION') THEN T.KAD_LV4_CODE
END AS KAD_LV4_CODE,CASE
WHEN 'KAD4' IN ('REGION') THEN T.KAD_LV4_NAME_CN
END AS KAD_LV4_NAME_CN,CASE
WHEN 'KAD4' IN ('REGION') THEN T.KAD_LV4_NAME_EN
END AS KAD_LV4_NAME_EN
FROM
(
SELECT
1 AS DUAL_ID,
count(DISTINCT kad.kad_code) * cast(
EXTRACT(
MONTH
FROM
TO_DATE('202310' || '01')
) - EXTRACT(
MONTH
FROM
TO_DATE('202301' || '01')
) + 1 AS INT
) AS mtg_qty,
count(
DISTINCT (
concat(
m.type_id,
TO_CHAR(date(m.release_date), 'YYYYMM')
)
)
) AS mtg_completed_qty,
count(DISTINCT kad.kad_code) - count(
DISTINCT (
CASE
WHEN TO_CHAR(date(m.release_date), 'YYYYMM') = TO_CHAR(ADD_MONTHS(now(), - 1), 'YYYYMM') THEN kad.kad_code
ELSE NULL
END
)
) AS lastmon_notConvened_mtg_qty,
kad.kad_code ELSE NULL END )) AS mon_notConvened_mtg_qty --本月未召开客户群数量
count(DISTINCT kad.kad_code) - count(
DISTINCT (
CASE
WHEN TO_CHAR(date(m.release_date), 'YYYYMM') = TO_CHAR(now(), 'YYYYMM') THEN kad.kad_code
ELSE NULL
END
)
) AS mon_notConvened_mtg_qty --本月未召开客户群数量
,CASE
WHEN 'REGION' IN ('REGION') THEN REG.REGION_CODE
END AS REGION_CODE,CASE
WHEN 'REGION' IN ('REGION') THEN REG.REGION_CN_NAME
END AS REGION_CN_NAME,CASE
WHEN 'REGION' IN ('REGION') THEN REG.REGION_EN_NAME
END AS REGION_EN_NAME,CASE
WHEN 'REPOFFICE' IN ('REGION') THEN REG.REPOFFICE_CODE
END AS REPOFFICE_CODE,CASE
WHEN 'REPOFFICE' IN ('REGION') THEN REG.REPOFFICE_CN_NAME
END AS REPOFFICE_CN_NAME,CASE
WHEN 'REPOFFICE' IN ('REGION') THEN REG.REPOFFICE_EN_NAME
END AS REPOFFICE_EN_NAME,CASE
WHEN 'OFFICE' IN ('REGION') THEN REG.OFFICE_CODE
END AS OFFICE_CODE,CASE
WHEN 'OFFICE' IN ('REGION') THEN REG.OFFICE_CN_NAME
END AS OFFICE_CN_NAME,CASE
WHEN 'OFFICE' IN ('REGION') THEN REG.OFFICE_EN_NAME
END AS OFFICE_EN_NAME,CASE
WHEN 'CUSTCATG' IN ('REGION') THEN KAD.REGION_CUSTCATG_CODE
END AS REGION_CUSTCATG_CODE,CASE
WHEN 'CUSTCATG' IN ('REGION') THEN KAD.REGION_CUSTCATG_CN_NAME
END AS REGION_CUSTCATG_CN_NAME,CASE
WHEN 'CUSTCATG' IN ('REGION') THEN KAD.REGION_CUSTCATG_EN_NAME
END AS REGION_CUSTCATG_EN_NAME,CASE
WHEN 'KAD' IN ('REGION') THEN KAD.KAD_CODE
END AS KAD_CODE,CASE
WHEN 'KAD' IN ('REGION') THEN KAD.KAD_NAME_CN
END AS KAD_NAME_CN,CASE
WHEN 'KAD' IN ('REGION') THEN KAD.KAD_NAME_EN
END AS KAD_NAME_EN,CASE
WHEN 'KAD1' IN ('REGION') THEN KAD.KAD_LV1_CODE
END AS KAD_LV1_CODE,CASE
WHEN 'KAD1' IN ('REGION') THEN KAD.KAD_LV1_NAME_CN
END AS KAD_LV1_NAME_CN,CASE
WHEN 'KAD1' IN ('REGION') THEN KAD.KAD_LV1_NAME_EN
END AS KAD_LV1_NAME_EN,CASE
WHEN 'KAD2' IN ('REGION') THEN KAD.KAD_LV2_CODE
END AS KAD_LV2_CODE,CASE
WHEN 'KAD2' IN ('REGION') THEN KAD.KAD_LV2_NAME_CN
END AS KAD_LV2_NAME_CN,CASE
WHEN 'KAD2' IN ('REGION') THEN KAD.KAD_LV2_NAME_EN
END AS KAD_LV2_NAME_EN,CASE
WHEN 'KAD3' IN ('REGION') THEN KAD.KAD_LV3_CODE
END AS KAD_LV3_CODE,CASE
WHEN 'KAD3' IN ('REGION') THEN KAD.KAD_LV3_NAME_CN
END AS KAD_LV3_NAME_CN,CASE
WHEN 'KAD3' IN ('REGION') THEN KAD.KAD_LV3_NAME_EN
END AS KAD_LV3_NAME_EN,CASE
WHEN 'KAD4' IN ('REGION') THEN KAD.KAD_LV4_CODE
END AS KAD_LV4_CODE,CASE
WHEN 'KAD4' IN ('REGION') THEN KAD.KAD_LV4_NAME_CN
END AS KAD_LV4_NAME_CN,CASE
WHEN 'KAD4' IN ('REGION') THEN KAD.KAD_LV4_NAME_EN
END AS KAD_LV4_NAME_EN
FROM
dmsalesw.dm_sale_kad_d KAD --LTC.DWR_SALE_KAD_D KAD
LEFT JOIN dwrdimw.dwr_dim_hrms_region_d REG --LTC.DWR_DIM_HRMS_REGION_D REG
ON KAD.REGION_ORG_ID = REG.REGION_ORG_ID
AND REG.SCD_ACTIVE_IND = 1
AND REG.DEL_FLAG = 'N'
LEFT JOIN dwrdimw.dwr_dim_account_period_d PER --LTC.DWR_DIM_ACCOUNT_PERIOD_D PER
ON 1 = 1
AND PER.PERIOD_TYPE = 'D'
AND SUBSTR(PER.PERIOD_ID, 1, 6) >= '202301' --时间筛选
AND SUBSTR(PER.PERIOD_ID, 1, 6) <= '202310'
LEFT JOIN dmsalesw.dm_mcr_meetingsummary_f M --"_SYS_BIC"."com.huawei.ltc.DWR_MCR/CA_DWR_MCR_MEETINGSUMMARY_V" m
ON m.type_id = KAD.KAD_CODE
AND M.DEL_FLAG = 'N'
AND TO_CHAR(PER.PERIOD_ID) = TO_CHAR(date(M.release_date), 'YYYYMMDD') --AND TO_CHAR(PER.PERIOD_ID,'YYYYMM') >= '202301' --入参 P_START_MONTH 202201
AND M.release_date IS NOT NULL
WHERE
1 = 1
AND KAD.ACTIVE_FLAG = 'Y'
AND KAD.DEL_FLAG = 'N'
AND KAD.KAD_CODE LIKE 'VCN%'
AND (
CASE
WHEN 'Y' = 'Y' THEN REG.REGION_CODE
ELSE '1'
END
) NOT IN (
'026700',
'025718',
'045222',
'048625',
'049682',
'038185',
'SNULL',
'024387',
'072133'
)
AND (
CASE
WHEN 'Y' = 'Y' THEN REG.REPOFFICE_CODE
ELSE '1'
END
) NOT IN ('037370', '026679') -- 2022/08/01 剔除俄罗斯、白俄罗斯
AND (
CASE
WHEN 'Y' = 'Y' THEN REG.OFFICE_CODE
ELSE '1'
END
) NOT IN ('026700', '045222')
AND CASE
WHEN KAD.REGION_CUSTCATG_CODE = 'Others'
AND KAD.REGION_ORG_CODE IN ('022471', '027425') THEN 'N'
ELSE 'Y'
END = 'Y'
GROUP BY
1,CASE
WHEN 'REGION' IN ('REGION') THEN REG.REGION_CODE
END,CASE
WHEN 'REGION' IN ('REGION') THEN REG.REGION_CN_NAME
END,CASE
WHEN 'REGION' IN ('REGION') THEN REG.REGION_EN_NAME
END,CASE
WHEN 'REPOFFICE' IN ('REGION') THEN REG.REPOFFICE_CODE
END,CASE
WHEN 'REPOFFICE' IN ('REGION') THEN REG.REPOFFICE_CN_NAME
END,CASE
WHEN 'REPOFFICE' IN ('REGION') THEN REG.REPOFFICE_EN_NAME
END,CASE
WHEN 'OFFICE' IN ('REGION') THEN REG.OFFICE_CODE
END,CASE
WHEN 'OFFICE' IN ('REGION') THEN REG.OFFICE_CN_NAME
END,CASE
WHEN 'OFFICE' IN ('REGION') THEN REG.OFFICE_EN_NAME
END,CASE
WHEN 'CUSTCATG' IN ('REGION') THEN KAD.REGION_CUSTCATG_CODE
END,CASE
WHEN 'CUSTCATG' IN ('REGION') THEN KAD.REGION_CUSTCATG_CN_NAME
END,CASE
WHEN 'CUSTCATG' IN ('REGION') THEN KAD.REGION_CUSTCATG_EN_NAME
END,CASE
WHEN 'KAD' IN ('REGION') THEN KAD.KAD_CODE
END,CASE
WHEN 'KAD' IN ('REGION') THEN KAD.KAD_NAME_CN
END,CASE
WHEN 'KAD' IN ('REGION') THEN KAD.KAD_NAME_EN
END,CASE
WHEN 'KAD1' IN ('REGION') THEN KAD.KAD_LV1_CODE
END,CASE
WHEN 'KAD1' IN ('REGION') THEN KAD.KAD_LV1_NAME_CN
END,CASE
WHEN 'KAD1' IN ('REGION') THEN KAD.KAD_LV1_NAME_EN
END,CASE
WHEN 'KAD2' IN ('REGION') THEN KAD.KAD_LV2_CODE
END,CASE
WHEN 'KAD2' IN ('REGION') THEN KAD.KAD_LV2_NAME_CN
END,CASE
WHEN 'KAD2' IN ('REGION') THEN KAD.KAD_LV2_NAME_EN
END,CASE
WHEN 'KAD3' IN ('REGION') THEN KAD.KAD_LV3_CODE
END,CASE
WHEN 'KAD3' IN ('REGION') THEN KAD.KAD_LV3_NAME_CN
END,CASE
WHEN 'KAD3' IN ('REGION') THEN KAD.KAD_LV3_NAME_EN
END,CASE
WHEN 'KAD4' IN ('REGION') THEN KAD.KAD_LV4_CODE
END,CASE
WHEN 'KAD4' IN ('REGION') THEN KAD.KAD_LV4_NAME_CN
END,CASE
WHEN 'KAD4' IN ('REGION') THEN KAD.KAD_LV4_NAME_EN
END
) T
3、【性能分析】
打印performance执行计划，找瓶颈点
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+------------------------------------------------------------------------------------------------------+----------------------+--------+---------+------------+----------------+----------------+-----------+---------+-----------
1 | -> Row Adapter | 2051.427 | 8 | 2 | | 289KB | | | 90 | 237845.12
2 | -> Vector Streaming (type: GATHER) | 2051.397 | 8 | 2 | | 568KB | | | 90 | 237845.12
3 | -> Vector Subquery Scan on t | [145.467, 1762.097] | 8 | 2 | | [504KB, 1MB] | 1MB | | 90 | 237811.12
4 | -> Vector Sonic Hash Join (5,50) | [145.461, 1761.945] | 8 | 2 | | [6MB, 6MB] | 16MB | | 862 | 237811.09
5 | -> Vector Sonic Hash Join (6,20) | [1191.275, 1213.717] | 8 | 14 | 1 | [10MB, 10MB] | 16MB | | 2514 | 177181.90
6 | -> Vector Sort Aggregate | [529.857, 558.023] | 8 | 848 | 50 | [2MB, 2MB] | 16MB | | 86 | 60935.01
7 | -> Vector Sort | [300.851, 530.600] | 909185 | 2212480 | | [2MB, 230MB] | 282MB(10130MB) | [331,347] | 78 | 48489.28
8 | -> Vector Streaming(type: REDISTRIBUTE) | [63.724, 528.457] | 909185 | 2212474 | | [857KB, 873KB] | 2MB | | 78 | 36336.38
9 | -> Vector Hash Right Join (10, 12) | [125.451, 158.049] | 909185 | 2212474 | | [16MB, 16MB] | 16MB | | 78 | 28304.26
10 | -> Vector Streaming(type: REDISTRIBUTE) | [1.084, 1.784] | 222387 | 221342 | 2819 | [553KB, 553KB] | 2MB | | 20 | 15564.32
11 | -> CStore Scan on dmsalesw.dm_mcr_meetingsummary_f m | [8.165, 12.518] | 222387 | 221342 | | [2MB, 2MB] | 1MB | | 20 | 14146.34
12 | -> Vector Streaming(type: REDISTRIBUTE) | [77.371, 106.772] | 869440 | 392952 | 2290 | [649KB, 649KB] | 2MB | [204,204] | 76 | 11896.11
13 | -> Vector Nest Loop Left Join (14, 17) | [18.347, 55.699] | 869440 | 392952 | | [568KB, 568KB] | 1MB | | 76 | 9378.71
14 | -> Vector Sonic Hash Join (15,16) | [2.351, 4.527] | 2860 | 2339 | | [1MB, 1MB] | 16MB | | 70 | 6966.89
15 | -> CStore Scan on dmsalesw.dm_sale_kad_d kad | [1.825, 3.467] | 3115 | 2339 | 4 | [2MB, 2MB] | 1MB | | 19 | 6713.77
16 | -> CStore Scan on dwrdimw.dwr_dim_hrms_region_d reg | [0.212, 0.568] | 605 | 843 | 53 | [2MB, 2MB] | 1MB | | 64 | 250.45
17 | -> Vector Materialize | [2.537, 12.495] | 874304 | 2688 | | [336KB, 336KB] | 16MB | [23,23] | 6 | 2105.64
18 | -> Vector Streaming(type: BROADCAST) | [1.045, 8.734] | 4864 | 2688 | | [280KB, 280KB] | 2MB | | 6 | 2104.80
19 | -> CStore Scan on dwrdimw.dwr_dim_account_period_d per | [1.086, 1.988] | 304 | 168 | | [1MB, 1MB] | 1MB | | 6 | 2079.60
20 | -> Vector Sonic Hash Join (21,35) | [636.847, 664.620] | 8 | 110 | 7 | [6MB, 6MB] | 16MB | | 1676 | 116242.01
21 | -> Vector Sort Aggregate | [133.706, 439.237] | 8 | 848 | 50 | [2MB, 2MB] | 16MB | | 86 | 59897.91
22 | -> Vector Sort | [132.185, 303.779] | 909185 | 2212480 | | [2MB, 215MB] | 282MB(10056MB) | [315,333] | 78 | 48489.28
23 | -> Vector Streaming(type: REDISTRIBUTE) | [64.202, 130.467] | 909185 | 2212474 | | [857KB, 873KB] | 2MB | | 78 | 36336.38
24 | -> Vector Hash Right Join (25, 27) | [104.895, 145.198] | 909185 | 2212474 | | [16MB, 16MB] | 16MB | | 78 | 28304.26
25 | -> Vector Streaming(type: REDISTRIBUTE) | [1.090, 2.003] | 222387 | 221342 | 2819 | [553KB, 553KB] | 2MB | | 20 | 15564.32
26 | -> CStore Scan on dmsalesw.dm_mcr_meetingsummary_f m | [8.082, 13.472] | 222387 | 221342 | | [2MB, 2MB] | 1MB | | 20 | 14146.34
27 | -> Vector Streaming(type: REDISTRIBUTE) | [56.952, 98.538] | 869440 | 392952 | 2290 | [649KB, 649KB] | 2MB | [204,204] | 76 | 11896.11
28 | -> Vector Nest Loop Left Join (29, 32) | [22.577, 47.447] | 869440 | 392952 | | [568KB, 568KB] | 1MB | | 76 | 9378.71
29 | -> Vector Sonic Hash Join (30,31) | [3.289, 4.262] | 2860 | 2339 | | [1MB, 1MB] | 16MB | | 70 | 6966.89
30 | -> CStore Scan on dmsalesw.dm_sale_kad_d kad | [2.490, 3.398] | 3115 | 2339 | 4 | [2MB, 2MB] | 1MB | | 19 | 6713.77
31 | -> CStore Scan on dwrdimw.dwr_dim_hrms_region_d reg | [0.232, 0.532] | 605 | 843 | 53 | [2MB, 2MB] | 1MB | | 64 | 250.45
32 | -> Vector Materialize | [2.743, 12.289] | 874304 | 2688 | | [336KB, 336KB] | 16MB | [23,23] | 6 | 2105.64
33 | -> Vector Streaming(type: BROADCAST) | [0.115, 10.655] | 4864 | 2688 | | [280KB, 280KB] | 2MB | | 6 | 2104.80
34 | -> CStore Scan on dwrdimw.dwr_dim_account_period_d per | [1.057, 2.068] | 304 | 168 | | [1MB, 1MB] | 1MB | | 6 | 2079.60
35 | -> Vector Subquery Scan on subquery | [194.065, 519.102] | 8 | 848 | 50 | [504KB, 504KB] | 1MB | | 838 | 56335.26
36 | -> Vector Sonic Hash Aggregate | [194.056, 519.095] | 8 | 848 | | [6MB, 6MB] | 16MB | [309,326] | 86 | 56334.73
37 | -> Vector Sonic Hash Aggregate | [191.281, 516.765] | 2860 | 1971776 | | [7MB, 7MB] | 164MB(9722MB) | [326,342] | 78 | 47399.59
38 | -> Vector Streaming(type: REDISTRIBUTE) | [77.701, 513.055] | 909185 | 2212474 | | [857KB, 873KB] | 2MB | | 70 | 35796.23
39 | -> Vector Hash Right Join (40, 42) | [125.181, 150.393] | 909185 | 2212474 | | [16MB, 16MB] | 16MB | | 70 | 28304.26
40 | -> Vector Streaming(type: REDISTRIBUTE) | [1.034, 1.925] | 222387 | 221342 | 2819 | [553KB, 553KB] | 2MB | | 20 | 15564.32
41 | -> CStore Scan on dmsalesw.dm_mcr_meetingsummary_f m | [6.952, 19.396] | 222387 | 221342 | | [2MB, 2MB] | 1MB | | 20 | 14146.34
42 | -> Vector Streaming(type: REDISTRIBUTE) | [69.853, 105.700] | 869440 | 392952 | 2290 | [649KB, 649KB] | 2MB | [204,204] | 76 | 11896.11
43 | -> Vector Nest Loop Left Join (44, 47) | [18.675, 51.349] | 869440 | 392952 | | [568KB, 568KB] | 1MB | | 76 | 9378.71
44 | -> Vector Sonic Hash Join (45,46) | [2.640, 4.576] | 2860 | 2339 | | [1MB, 1MB] | 16MB | | 70 | 6966.89
45 | -> CStore Scan on dmsalesw.dm_sale_kad_d kad | [1.994, 3.434] | 3115 | 2339 | 4 | [2MB, 2MB] | 1MB | | 19 | 6713.77
46 | -> CStore Scan on dwrdimw.dwr_dim_hrms_region_d reg | [0.286, 0.495] | 605 | 843 | 53 | [2MB, 2MB] | 1MB | | 64 | 250.45
47 | -> Vector Materialize | [5.782, 10.695] | 874304 | 2688 | | [336KB, 336KB] | 16MB | [23,23] | 6 | 2105.64
48 | -> Vector Streaming(type: BROADCAST) | [2.877, 8.391] | 4864 | 2688 | | [280KB, 280KB] | 2MB | | 6 | 2104.80
49 | -> CStore Scan on dwrdimw.dwr_dim_account_period_d per | [1.054, 2.065] | 304 | 168 | | [1MB, 1MB] | 1MB | | 6 | 2079.60
50 | -> Vector Subquery Scan on subquery | [137.994, 546.120] | 8 | 848 | 50 | [504KB, 504KB] | 1MB | | 838 | 60589.84
51 | -> Vector Sort Aggregate | [137.987, 546.112] | 8 | 848 | | [2MB, 2MB] | 16MB | | 86 | 60589.31
52 | -> Vector Sort | [135.381, 320.487] | 909185 | 2212480 | | [768KB, 230MB] | 282MB(9866MB) | [0,347] | 78 | 48489.28
53 | -> Vector Streaming(type: REDISTRIBUTE) | [68.857, 239.635] | 909185 | 2212474 | | [520KB, 873KB] | 2MB | | 78 | 36336.38
54 | -> Vector Hash Right Join (55, 57) | [122.369, 150.707] | 909185 | 2212474 | | [16MB, 16MB] | 16MB | | 78 | 28304.26
55 | -> Vector Streaming(type: REDISTRIBUTE) | [1.209, 2.076] | 222387 | 221342 | 2819 | [553KB, 553KB] | 2MB | | 20 | 15564.32
56 | -> CStore Scan on dmsalesw.dm_mcr_meetingsummary_f m | [6.801, 12.737] | 222387 | 221342 | | [2MB, 2MB] | 1MB | | 20 | 14146.34
57 | -> Vector Streaming(type: REDISTRIBUTE) | [67.940, 96.786] | 869440 | 392952 | 2290 | [649KB, 649KB] | 2MB | [204,204] | 76 | 11896.11
58 | -> Vector Nest Loop Left Join (59, 62) | [13.895, 50.134] | 869440 | 392952 | | [568KB, 568KB] | 1MB | | 76 | 9378.71
59 | -> Vector Sonic Hash Join (60,61) | [2.862, 4.965] | 2860 | 2339 | | [1MB, 1MB] | 16MB | | 70 | 6966.89
60 | -> CStore Scan on dmsalesw.dm_sale_kad_d kad | [2.213, 3.626] | 3115 | 2339 | 4 | [2MB, 2MB] | 1MB | | 19 | 6713.77
61 | -> CStore Scan on dwrdimw.dwr_dim_hrms_region_d reg | [0.215, 0.560] | 605 | 843 | 53 | [2MB, 2MB] | 1MB | | 64 | 250.45
62 | -> Vector Materialize | [1.924, 12.205] | 874304 | 2688 | | [336KB, 336KB] | 16MB | [23,23] | 6 | 2105.64
63 | -> Vector Streaming(type: BROADCAST) | [0.618, 9.395] | 4864 | 2688 | | [280KB, 280KB] | 2MB | | 6 | 2104.80
64 | -> CStore Scan on dwrdimw.dwr_dim_account_period_d per | [0.999, 2.103] | 304 | 168 | | [1MB, 1MB] | 1MB | | 6 | 2079.60 从计划中可以看出，瓶颈点在于其中的4个Sort Aggregate和其下的Sort算子（如第51、52层算子），以及4个相似的结果集互相join，可能是重复逻辑。我们先分析其中的一组算子。
从第53层算子看起，按reg.region_code重分布出现了较严重的数据倾斜，而上层的Sort和Sort Agg算子都是这样倾斜的。但是sort算子的倾斜没法使用hint进行倾斜调优。我们可以从减小中间结果集的角度出发，看看能不能做一些优化。
我们注意到，第58层算子Nest Loop，使中间结果集扩大了许多倍。分析SQL，发现如果调整join的位置，使生成没有Nest Loop的计划，结果集不会改变，但是可以避免发生关联发散。
...
FROM
dmsalesw.dm_sale_kad_d KAD --LTC.DWR_SALE_KAD_D KAD
LEFT JOIN dwrdimw.dwr_dim_hrms_region_d REG --LTC.DWR_DIM_HRMS_REGION_D REG
ON KAD.REGION_ORG_ID = REG.REGION_ORG_ID
AND REG.SCD_ACTIVE_IND = 1
AND REG.DEL_FLAG = 'N'
LEFT JOIN dmsalesw.dm_mcr_meetingsummary_f M --"_SYS_BIC"."com.huawei.ltc.DWR_MCR/CA_DWR_MCR_MEETINGSUMMARY_V" m
ON m.type_id = KAD.KAD_CODE
AND M.DEL_FLAG = 'N'
LEFT JOIN dwrdimw.dwr_dim_account_period_d PER --LTC.DWR_DIM_ACCOUNT_PERIOD_D PER
ON 1 = 1
AND PER.PERIOD_TYPE = 'D'
AND SUBSTR(PER.PERIOD_ID, 1, 6) >= '202301' --时间筛选
AND SUBSTR(PER.PERIOD_ID, 1, 6) <= '202310'
AND TO_CHAR(PER.PERIOD_ID) = TO_CHAR(date(M.release_date), 'YYYYMMDD') --AND TO_CHAR(PER.PERIOD_ID,'YYYYMM') >= '202301' --入参 P_START_MONTH 202201
AND M.release_date IS NOT NULL
...如上所示，仅仅改变了PER表的join位置。修改后的执行计划如下：
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs
----+------------------------------------------------------------------------------------------------------+--------------------+--------+--------+------------+----------------+--------------+-----------+---------+-----------
1 | -> Row Adapter | 717.028 | 8 | 2 | | 289KB | | | 90 | 115365.22
2 | -> Vector Streaming (type: GATHER) | 716.997 | 8 | 2 | | 568KB | | | 90 | 115365.22
3 | -> Vector Subquery Scan on t | [50.232, 515.802] | 8 | 2 | | [504KB, 1MB] | 1MB | | 90 | 115331.22
4 | -> Vector Sonic Hash Join (5,49) | [50.220, 515.717] | 8 | 2 | | [6MB, 6MB] | 16MB | | 862 | 115331.19
5 | -> Vector Sonic Hash Join (6,19) | [318.203, 332.499] | 8 | 13 | 1 | [10MB, 10MB] | 16MB | | 2514 | 86199.08
6 | -> Vector Sort Aggregate | [137.803, 182.484] | 8 | 848 | 50 | [2MB, 2MB] | 16MB | | 86 | 29128.50
7 | -> Vector Sort | [67.699, 178.151] | 225042 | 232128 | | [4MB, 51MB] | 29MB(9726MB) | [331,348] | 78 | 27822.25
8 | -> Vector Streaming(type: REDISTRIBUTE) | [12.861, 175.315] | 225042 | 232129 | | [696KB, 889KB] | 2MB | | 78 | 26783.15
9 | -> Vector Hash Left Join (10, 17) | [11.297, 28.970] | 225042 | 232129 | | [1MB, 1MB] | 16MB | | 78 | 25940.44
10 | -> Vector Hash Right Join (11, 13) | [2.038, 20.261] | 225042 | 232129 | 2912 | [353KB, 356KB] | 16MB | | 78 | 23788.89
11 | -> Vector Streaming(type: REDISTRIBUTE) | [0.909, 19.193] | 232215 | 232199 | 36 | [521KB, 537KB] | 2MB | | 20 | 16519.37
12 | -> CStore Scan on dmsalesw.dm_mcr_meetingsummary_f m | [1.050, 1.464] | 232215 | 232199 | | [1MB, 1MB] | 1MB | | 20 | 14609.96
13 | -> Vector Streaming(type: REDISTRIBUTE) | [0.107, 0.230] | 2866 | 2345 | 147 | [328KB, 328KB] | 2MB | [145,148] | 70 | 6988.30
14 | -> Vector Sonic Hash Join (15,16) | [2.064, 4.093] | 2866 | 2345 | | [1MB, 1MB] | 16MB | | 70 | 6976.18
15 | -> CStore Scan on dmsalesw.dm_sale_kad_d kad | [1.501, 3.332] | 3119 | 2345 | 4 | [2MB, 2MB] | 1MB | | 19 | 6722.02
16 | -> CStore Scan on dwrdimw.dwr_dim_hrms_region_d reg | [0.256, 0.424] | 605 | 843 | 53 | [2MB, 2MB] | 1MB | | 64 | 251.47
17 | -> Vector Streaming(type: BROADCAST) | [0.147, 0.272] | 4864 | 2688 | 168 | [280KB, 280KB] | 2MB | [30,30] | 6 | 2104.80
18 | -> CStore Scan on dwrdimw.dwr_dim_account_period_d per | [1.063, 4.651] | 304 | 168 | | [1MB, 1MB] | 1MB | | 6 | 2079.60
19 | -> Vector Sonic Hash Join (20,33) | [132.068, 185.993] | 8 | 107 | 7 | [6MB, 6MB] | 16MB | | 1676 | 57065.70
20 | -> Vector Sort Aggregate | [129.610, 183.054] | 8 | 848 | 50 | [2MB, 2MB] | 16MB | | 86 | 29019.69
21 | -> Vector Sort | [71.043, 122.354] | 225042 | 232128 | | [4MB, 51MB] | 29MB(8929MB) | [330,348] | 78 | 27822.25
22 | -> Vector Streaming(type: REDISTRIBUTE) | [12.953, 119.049] | 225042 | 232129 | | [696KB, 889KB] | 2MB | | 78 | 26783.15
23 | -> Vector Hash Left Join (24, 31) | [10.476, 50.579] | 225042 | 232129 | | [1MB, 1MB] | 16MB | | 78 | 25940.44
24 | -> Vector Hash Right Join (25, 27) | [1.812, 39.539] | 225042 | 232129 | 2912 | [353KB, 356KB] | 16MB | | 78 | 23788.89
25 | -> Vector Streaming(type: REDISTRIBUTE) | [0.825, 38.434] | 232215 | 232199 | 36 | [521KB, 537KB] | 2MB | | 20 | 16519.37
26 | -> CStore Scan on dmsalesw.dm_mcr_meetingsummary_f m | [0.903, 1.781] | 232215 | 232199 | | [1MB, 1MB] | 1MB | | 20 | 14609.96
27 | -> Vector Streaming(type: REDISTRIBUTE) | [0.081, 0.231] | 2866 | 2345 | 147 | [328KB, 328KB] | 2MB | [145,148] | 70 | 6988.30
28 | -> Vector Sonic Hash Join (29,30) | [2.104, 4.196] | 2866 | 2345 | | [1MB, 1MB] | 16MB | | 70 | 6976.18
29 | -> CStore Scan on dmsalesw.dm_sale_kad_d kad | [1.476, 3.146] | 3119 | 2345 | 4 | [2MB, 2MB] | 1MB | | 19 | 6722.02
30 | -> CStore Scan on dwrdimw.dwr_dim_hrms_region_d reg | [0.236, 0.558] | 605 | 843 | 53 | [2MB, 2MB] | 1MB | | 64 | 251.47
31 | -> Vector Streaming(type: BROADCAST) | [0.108, 0.222] | 4864 | 2688 | 168 | [280KB, 280KB] | 2MB | [30,30] | 6 | 2104.80
32 | -> CStore Scan on dwrdimw.dwr_dim_account_period_d per | [1.065, 1.981] | 304 | 168 | | [1MB, 1MB] | 1MB | | 6 | 2079.60
33 | -> Vector Subquery Scan on subquery | [0.408, 0.560] | 8 | 848 | 50 | [504KB, 504KB] | 1MB | | 838 | 28037.16
34 | -> Vector Sort Aggregate | [0.400, 0.552] | 8 | 848 | | [2MB, 2MB] | 16MB | | 78 | 28036.63
35 | -> Vector Sort | [0.291, 0.327] | 118 | 848 | | [816KB, 848KB] | 16MB | [306,323] | 78 | 28036.63
36 | -> Vector Streaming(type: REDISTRIBUTE) | [0.168, 0.217] | 118 | 848 | | [568KB, 568KB] | 2MB | | 78 | 28034.98
37 | -> Vector Sort Aggregate | [35.232, 99.094] | 118 | 848 | | [2MB, 2MB] | 16MB | | 78 | 28031.90
38 | -> Vector Sort | [31.779, 95.718] | 225042 | 232128 | | [8MB, 17MB] | 28MB(6091MB) | [328,337] | 70 | 26979.54
39 | -> Vector Hash Left Join (40, 47) | [23.682, 88.628] | 225042 | 232129 | | [1MB, 1MB] | 16MB | | 70 | 25940.44
40 | -> Vector Hash Right Join (41, 43) | [14.531, 78.612] | 225042 | 232129 | 2912 | [353KB, 356KB] | 16MB | | 78 | 23788.89
41 | -> Vector Streaming(type: REDISTRIBUTE) | [13.643, 77.562] | 232215 | 232199 | 36 | [521KB, 537KB] | 2MB | | 20 | 16519.37
42 | -> CStore Scan on dmsalesw.dm_mcr_meetingsummary_f m | [1.048, 1.540] | 232215 | 232199 | | [1MB, 1MB] | 1MB | | 20 | 14609.96
43 | -> Vector Streaming(type: REDISTRIBUTE) | [0.072, 0.199] | 2866 | 2345 | 147 | [328KB, 328KB] | 2MB | [145,148] | 70 | 6988.30
44 | -> Vector Sonic Hash Join (45,46) | [1.964, 3.943] | 2866 | 2345 | | [1MB, 1MB] | 16MB | | 70 | 6976.18
45 | -> CStore Scan on dmsalesw.dm_sale_kad_d kad | [1.457, 3.134] | 3119 | 2345 | 4 | [2MB, 2MB] | 1MB | | 19 | 6722.02
46 | -> CStore Scan on dwrdimw.dwr_dim_hrms_region_d reg | [0.245, 0.533] | 605 | 843 | 53 | [2MB, 2MB] | 1MB | | 64 | 251.47
47 | -> Vector Streaming(type: BROADCAST) | [0.076, 0.187] | 4864 | 2688 | 168 | [280KB, 280KB] | 2MB | [30,30] | 6 | 2104.80
48 | -> CStore Scan on dwrdimw.dwr_dim_account_period_d per | [1.073, 1.913] | 304 | 168 | | [1MB, 1MB] | 1MB | | 6 | 2079.60
49 | -> Vector Subquery Scan on subquery | [35.363, 189.744] | 8 | 848 | 50 | [504KB, 504KB] | 1MB | | 838 | 29092.76
50 | -> Vector Sort Aggregate | [35.356, 189.735] | 8 | 848 | | [2MB, 2MB] | 16MB | | 86 | 29092.23
51 | -> Vector Sort | [30.844, 120.134] | 225042 | 232128 | | [768KB, 51MB] | 29MB(6404MB) | [0,348] | 78 | 27822.25
52 | -> Vector Streaming(type: REDISTRIBUTE) | [27.401, 119.753] | 225042 | 232129 | | [520KB, 889KB] | 2MB | | 78 | 26783.15
53 | -> Vector Hash Left Join (54, 61) | [8.951, 37.006] | 225042 | 232129 | | [1MB, 1MB] | 16MB | | 78 | 25940.44
54 | -> Vector Hash Right Join (55, 57) | [1.591, 29.398] | 225042 | 232129 | 2912 | [353KB, 356KB] | 16MB | | 78 | 23788.89
55 | -> Vector Streaming(type: REDISTRIBUTE) | [0.721, 28.630] | 232215 | 232199 | 36 | [521KB, 537KB] | 2MB | | 20 | 16519.37
56 | -> CStore Scan on dmsalesw.dm_mcr_meetingsummary_f m | [1.146, 1.570] | 232215 | 232199 | | [1MB, 1MB] | 1MB | | 20 | 14609.96
57 | -> Vector Streaming(type: REDISTRIBUTE) | [0.079, 0.203] | 2866 | 2345 | 147 | [328KB, 328KB] | 2MB | [145,148] | 70 | 6988.30
58 | -> Vector Sonic Hash Join (59,60) | [2.134, 4.300] | 2866 | 2345 | | [1MB, 1MB] | 16MB | | 70 | 6976.18
59 | -> CStore Scan on dmsalesw.dm_sale_kad_d kad | [1.627, 3.108] | 3119 | 2345 | 4 | [2MB, 2MB] | 1MB | | 19 | 6722.02
60 | -> CStore Scan on dwrdimw.dwr_dim_hrms_region_d reg | [0.278, 0.557] | 605 | 843 | 53 | [2MB, 2MB] | 1MB | | 64 | 251.47
61 | -> Vector Streaming(type: BROADCAST) | [0.101, 0.223] | 4864 | 2688 | 168 | [280KB, 280KB] | 2MB | [30,30] | 6 | 2104.80
62 | -> CStore Scan on dwrdimw.dwr_dim_account_period_d per | [1.539, 2.181] | 304 | 168 | | [1MB, 1MB] | 1MB | | 6 | 2079.60 从计划中可以看出，修改后SQL的计划中消去了Nest Loop，使得中间结果集为之前的1/4。
接下来定位计划中4个相似逻辑的问题。通过注释掉原SQL的一些列，我们发现这些相似逻辑来源于IFNULL函数，暂时无法消去。
4、【调优总结】
在本案例中，在无法通过hint消除的情景下，通过调整SQL消除了预期外的Nest Loop，减轻了Sort和Sort Agg算子的瓶颈。
遗留问题：主查询中的IFNULL()函数导致的4个相似逻辑没法消除。
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）