PL/SQL游标使用UNION ALL出现性能下降 打开结果时程序冻结

第一步确认执行计划偏差问题 #

你遇到的「单独跑SQL快、存储过程中跑慢」是典型的执行计划不匹配场景：PL/SQL窗口直接执行时用的是字面量参数，优化器可以根据真实参数值生成最优计划；存储过程中用的是绑定变量，默认开启绑定变量窥探的情况下，首次生成的计划适配性差，加上UNION ALL双分支的统计信息预估偏差，很容易出现执行计划选错的问题。你可以先在SQL窗口用绑定变量方式重写查询（参数用:p_date_to这类占位符，不要直接填值），验证执行计划和性能是否和存储过程中一致。

第二步检查统计信息准确性 #

你提供的执行计划里预估总行数只有2行，明显和实际业务数据量偏差极大，直接导致优化器选择了嵌套循环作为关联方式，实际如果数据量过万，嵌套循环的开销会指数级上涨。需要检查以下两张核心事务表的统计信息是否过期：

• 分区表F003_BILL、F007_RTL_TXN的分区级统计信息
• 关联表DET_SALES_PPT_DWH、T_OP_MOTIVATION_RATE_MYRTK的表级统计信息

第三步定位隐式转换问题 #

从执行计划的谓词信息可以看到两处明显的隐式转换，都会导致索引失效、关联效率暴跌：

1. DET.PERIOD是数字类型，SI.SALE_PERIOD是to_char生成的字符串，关联时触发了TO_NUMBER("SI"."SALE_PERIOD")隐式转换
2. 关联条件中使用ltrim(si.operator_id,'0') = ltrim(det.tab_num,'0')，函数包裹后无法直接使用字段上的普通索引

临时快速修复方案 #

• 语句级禁用绑定变量窥探：在查询开头加Hint /*+ opt_param('_optim_peek_user_binds' 'false') */，强制优化器不根据首次参数值生成固定计划
• 强制关联方式：加Hint /*+ use_hash(det mr) */ 强制使用哈希关联替代嵌套循环，适配大数据量关联场景

永久根治方案 #

1. 修复隐式类型转换
   • 把UNION ALL两个分支中sale_period的生成逻辑改成to_number(to_char(b.businessday, 'yyyymm'))，和det.period的数字类型对齐
   • 去掉关联条件中的ltrim函数，数据入库时统一清洗掉operator_id、tab_num的前导0，或者创建函数索引适配现有逻辑：

   create index idx_sales_details_ltrim_tabnum on scheme.sales_details(ltrim(tab_num,'0'));
   

2. 下推分页逻辑减少数据处理量
   现有逻辑是将UNION ALL所有数据关联完成后再分页，实际可以把分页逻辑下推到两个子查询内部，先各自按时间排序取前(p_page_num+1)*p_page_size行，再合并关联后做二次分页，能减少90%以上的关联数据量，示例逻辑如下：

open outcur for
  select *
    from (select -- 原查询字段保留
                 si.item_full_name
               , si.final_price
               , si.full_price
               , si.receipt_num
               , si.receipt_date
               , si.vendor_code
               , case when det.br_summary is null and mr.motiv_rate_value is not null then mr.motiv_rate_value
                      when det.br_summary is not null then det.br_summary
                  end personal_bonus_amount
               , case when det.br_summary is null and mr.motiv_rate_value is not null then 1
                      when det.br_summary is not null then det.cross_sale_kt
                  end personal_bonus_koeff
               , case when det.br_summary is null and mr.motiv_rate_value is not null then 'approximate'
                      when det.br_summary is not null then 'definite'
                  end personal_bonus_type
               , coalesce(det.sale_stream, mr.sale_stream, 'Not defined') item_group_name
               , si.operation_type
               , si.src
               , row_number() over (order by si.receipt_date desc) rn 
            from (-- 分页下推：当日数据先取前N行
                  select * from (
                    select b.cost final_price
                         , case when b.discount = 0 then null else b.price end full_price
                         , b.doc_number receipt_num
                         , b.receipt_date receipt_date
                         , i.item_code vendor_code
                         , i.full_name item_full_name
                         , b.subsite code_op
                         , b.operator_id
                         , to_number(to_char(b.businessday, 'yyyymm')) sale_period
                         , b.oper_type operation_type
                         , 'bill' src
                      from scheme.bills b
                      join scheme.items i on i.item_code = b.item
                     where b.businessday = trunc(p_date_to)
                       and b.subsite = p_office_id
                       and b.operator_id = p_emp_id
                     order by b.receipt_date desc
                  ) where rownum <= (p_page_num + 1) * p_page_size
                  union all
                  -- 分页下推：历史数据先取前N行
                  select * from (
                    select l.txn_amount final_price
                         , case when l.disc = 0 then null else l.price end full_price
                         , t.receipt_num receipt_num
                         , t.ts receipt_date
                         , i.item_code vendor_code
                         , i.full_name item_full_name
                         , s.office_code code_op
                         , e.emp_code operator_id
                         , to_number(to_char(l.dt,'yyyymm')) sale_period
                         , l.txn_type operation_type
                         , 'txn' src
                      from scheme.txn t
                      join scheme.txn_lines l on t.rtl_txn_id = l.rtl_txn_id
                      join scheme.items i on l.item_id = i.item_id
                      join scheme.offices s on t.subsite_id = s.subsite_id
                      join scheme.employees e on t.employee_id = e.employee_id
                     where t.ts between trunc(p_date_from) and trunc(p_date_to)
                       and t.subsite_id = v_op_id
                       and t.employee_id = v_emp_id
                     order by t.ts desc
                  ) where rownum <= (p_page_num + 1) * p_page_size
             ) si
             left join scheme.sales_details det on si.sale_period = det.period
                                               and si.code_op = det.op_code
                                               and si.operator_id = det.tab_num
                                               and si.receipt_num = det.rcpt_num
                                               and si.vendor_code = det.item_article
             left join scheme.rates mr on si.sale_period = mr.motiv_rate_period
                                      and si.code_op = mr.code_op
                                      and si.vendor_code = mr.code_1c
            where si.final_price between nvl(p_price_from, si.final_price) and nvl(p_price_to, si.final_price)
              and (item_group_cnt = 0 or coalesce(det.sale_stream, mr.sale_stream, 'Not defined') in (select * from table(p_item_group)))
              and si.receipt_num = nvl(p_receipt_num, si.receipt_num)                                        
         )
   where rn between p_page_num * p_page_size + 1 and (p_page_num + 1) * p_page_size;

3. 更新统计信息
   定期收集核心业务表的统计信息，确保优化器预估行数准确：

begin
  dbms_stats.gather_table_stats(ownname => 'SCHEME', tabname => 'F003_BILL', cascade => true, estimate_percent => 100);
  dbms_stats.gather_table_stats(ownname => 'SCHEME', tabname => 'F007_RTL_TXN', cascade => true, estimate_percent => 100);
  dbms_stats.gather_table_stats(ownname => 'SCHEME', tabname => 'DET_SALES_PPT_DWH', cascade => true);
end;
/

内容的提问来源于stack exchange，提问作者John Doe