内存占用率忽高忽低怎么解决

内存占用率忽高忽低怎么解决,(9)

详细执行计划中，虽然维度表进行了分区剪枝，但由于使用了 left join，导致关联条件中维度表的常量period_id不能直接赋值给主表period_id，主表关联后的结果重分布时将period_id作为了分布键之一，这会影响优化器的倾斜优化。

可以将f.period_id = 维度表.period_id这一关联条件删掉，对sql进行如下改写

FROM dmdp.dm_dpc_inv_m_dtl_f_TEM_A LT1 LEFT JOIN dmcommon.dm_dim_prod_key_r LT2 ON LT1.prod_key = LT2.old_key AND LT2.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_reg_key_r LT3 ON LT1.geo_pc_key = LT3.old_key AND LT3.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_cus_key_r LT4 ON LT1.account_dept_cust_key = LT4.old_key AND LT4.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_proj_key_r LT5 ON LT1.proj_key = LT5.old_key AND LT5.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_cus_key_r LT6 ON LT1.enterprise_cust_key = LT6.old_key AND LT6.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_rep_key_r LT7 ON LT1.report_item_id = LT7.old_key AND LT7.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_supply_center_key_r LT8 ON LT1.supply_center_key = LT8.old_key AND LT8.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_inv_key_r LT9 ON LT1.inventory_class_key = LT9.old_key AND LT9.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_bus_key_r LT10 ON LT1.business_status_key = LT10.old_key AND LT10.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_hisi_key_r LT11 ON LT1.hisi_prod_key = LT11.old_key AND LT11.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_inv_org_key_r LT12 ON LT1.inventory_org_key = LT12.old_key AND LT12.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_cus_key_r LT13 ON LT1.end_cust_key = LT13.old_key AND LT13.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_cus_key_r LT14 ON LT1.sign_cust_key = LT14.old_key AND LT14.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_cus_key_r LT15 ON LT1.agent_distribution_cust_key = LT15.old_key AND LT15.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_com_key_r LT16 ON LT1.company_key = LT16.old_key AND LT16.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_con_key_r LT17 ON LT1.contract_key = LT17.old_key AND LT17.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_con_key_r LT18 ON LT1.loan_contract_key = LT18.old_key AND LT18.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_supply_center_key_r LT19 ON LT1.target_supply_center_key = LT19.old_key AND LT19.PERIOD_ID = 202406 LEFT JOIN dmcommon.dm_dim_subinventory_key_r LT20 ON LT1.subinventory_key = LT20.old_key AND LT20.PERIOD_ID = 202406 WHERE 1 = 1 AND partition_value IN ( 0, 1 )

改写后，执行计划如下所示

内存占用率忽高忽低怎么解决,(10)