case1828.md

现象

执行 union select 聚合查询出现 OOM 。

环境信息收集

版本

v2.1.0-rc.3-5-g99c4a15

部署情况

• SQL 与 执行计划、表结构详见 test.sql

分析步骤

• 抓取火焰图，获取内存使用情况。
• 重新收集相关表的统计信息，发现依然会发生 OOM 的情况。
• sql 是一个 union 连接了两个 hashagg，然后再对这个 union 执行 hashagg 。 OOM 可能发生在 union 并行执行的时候，也可能发生在最外面的 hashagg 。所以先在 union 外面加了个 order by，把最外面的 agg 改成 streamagg 试一下，看会不会 OOM 。修改 SQL 如下，再进行测试。结果还是出现 OOM 。

   SELECT tmp_amt.channel_id channel_id, tmp_amt.sub_channel sub_channel, tmp_amt.activity_info activity_info,tmp_amt.product_code product_code,
  0 uv_num, 0 register_num, 0 complete_num_newu, 0 complete_num_oldu, 0 aps_num_newu, 0 aps_amt_newu, 0 aps_num_newu, 0 aps_amt_newu, 0 draw_num_newu, 0 draw_num_oldu,
  0 loan_num_newu, 0 loan_num_oldu, 0 loan_amt_newu, 0 loan_amt_oldu, 0 loan_num_sj, 0 loan_amt_sj, 0 loan_num_fj, 0 loan_amt_fj,
  SUM(tmp_amt.accu_loan_amt) accu_loan_amt, SUM(tmp_amt.acct_repay_amt) acct_repay_amt,
  0 loan_user_sj,0 loan_amt_sj_xxx,0 loan_user_newu,0 loan_user_oldu,0 first_complete_num_newu, 0 first_complete_num_oldu FROM
  (
  SELECT *
  (
      select uu.channel_id channel_id, coalesce(uu.sub_channel, '') sub_channel, coalesce(uu.activity_info, '') activity_info,
      case when aae.appl_no is not null and aa.product_code in ('JT', 'JX', 'MA') then aa.product_code||'_BJ' else aa.product_code end as product_code,
      0 accu_loan_amt, sum(COALESCE(flow.trans_prin, 0)) AS acct_repay_amt
      from u_user uu
      join (select aa.user_no, aa.appl_no, aa.contract_no, aa.product_code from ap_appl aa where aa.appl_state='APS' and aa.contract_no is not null  
          AND aa.product_code in ('JT', 'YJ', 'JX', 'MA') group by aa.user_no, aa.appl_no, aa.contract_no, aa.product_code) aa on uu.user_no = aa.user_no    
      join tran_proc_rp flow on aa.contract_no = flow.contract_no
      join ln_loan loan on flow.loan_no = loan.loan_no
      left join ap_appl_ext aae on aa.appl_no = aae.appl_no and aae.credit_ident='Y'
      where flow.date_rp < '2018-10-11 00:00:00' AND flow.status = '03'
      GROUP BY uu.channel_id, coalesce(uu.sub_channel, ''), coalesce(uu.activity_info, ''),
      case when aae.appl_no is not null and aa.product_code in ('JT', 'JX', 'MA') then aa.product_code||'_BJ' else aa.product_code end
      UNION ALL
      select uu.channel_id channel_id, coalesce(uu.sub_channel, '') sub_channel, coalesce(uu.activity_info, '') activity_info,
      case when aae.appl_no is not null and aa.product_code in ('JT', 'JX', 'MA') then aa.product_code||'_BJ' else aa.product_code end as product_code,
      sum(COALESCE(loan.loan_amt, 0)) AS accu_loan_amt,0 acct_repay_amt
      from u_user uu
      join (select aa.user_no, aa.appl_no, aa.contract_no, aa.product_code from ap_appl aa where aa.appl_state='APS' and aa.contract_no is not null  
          AND aa.product_code in ('JT', 'YJ', 'JX', 'MA') group by aa.user_no, aa.appl_no, aa.contract_no, aa.product_code) aa on uu.user_no = aa.user_no     
      join ln_loan loan on aa.contract_no = loan.contract_no
      left join ap_appl_ext aae on aa.appl_no = aae.appl_no and aae.credit_ident='Y'
      where loan.date_created < '2018-10-11 00:00:00'
      GROUP BY uu.channel_id, coalesce(uu.sub_channel, ''), coalesce(uu.activity_info, ''),
      case when aae.appl_no is not null and aa.product_code in ('JT', 'JX', 'MA') then aa.product_code||'_BJ' else aa.product_code end
  ) tmp_amt
  order by tmp_amt.channel_id, tmp_amt.sub_channel, tmp_amt.activity_info, tmp_amt.product_code ) tmp_amt --在 union 外面套了层 order by，走 stream agg
  GROUP BY tmp_amt.channel_id, tmp_amt.sub_channel, tmp_amt.activity_info, tmp_amt.product_code 

• 把最外层的 agg 去掉，直接 select * from( union all)， 仍会出现 OOM 的问题。
• 单独执行 union all 里面的每一个 select 并不会引起 OOM 。

结论

Union 并行执行 select 引起的 OOM 问题。