Oracle查询性能调优：如何稳定使用高效Execution Plan？

问题背景 #

有一条Oracle查询语句，在部分环境中偶尔会生成性能极差的执行计划，出现Table Access Full（全表扫描）和Merge Join Cartesian（笛卡尔积合并），相关表的统计数据已更新，需要找出SQL中的低效点并给出调优方案，确保持续使用高效执行计划。

原查询语句：

select 
 'RECEIVE-DONE' OP_TYPE,   
 sbmt.SUBMIT_NO, 
 wsr.USE_LUMP_PROC, 
 sf.* 
from ( 
 select 
 my.flow_id myflow_id, 
 done.flow_id flow_id, 
 done.status SFSTATUS, 
 from submit_flow my , submit_flow done 
  where my.submit_id=done.submit_id 
  and my.check_ord=done.check_ord 
  and my.emp_id = '1'
  and done.status
  in (1,2,3,4) 
  and ( (done.mainflow=1 and done.check_ord > 0) or (done.emp_id ='1' and done.type in (1,2,3) and done.type = my.type ) )  
  ) sf,  
  submit_list sbmt, wfserveroute wsr, emp emp 
where  
 sbmt.EMP_ID = emp.EMP_ID 
 and sf.SUBMIT_ID = sbmt.SUBMIT_ID 
 and ((sf.TYPE = 1 and sbmt.COMPLETED = 1 and sbmt.status = 2) or (sf.TYPE <> 1)) 
 and sbmt.FLOW_SCHEM  = wsr.SCHEM   
 and sbmt.SERVICENAME = wsr.FULLNAME 
 and wsr.CASE = 0 
 and not exists( 
 SELECT 1 FROM service_mst smst 
 WHERE   (smst.namespace||'.'||smst.name) = sbmt.SERVICENAME
 and  smst.not_view_at_inouttray = 1  
 )  order by sf.prc_date DESC

一、SQL中的明显低效/冗余点 #

• 自关联条件冗余：submit_flow自关联时，my.check_ord=done.check_ord已被包含在后续分支条件中，重复关联可能干扰优化器对数据量的判断。
• 字符串拼接导致索引失效：not exists子查询中使用smst.namespace||'.'||smst.name = sbmt.SERVICENAME，拼接操作会阻止Oracle使用smst.namespace或smst.name上的索引，强制触发全表扫描。
• OR条件破坏索引有效性：主查询的((sf.TYPE = 1 and sbmt.COMPLETED = 1 and sbmt.status = 2) or (sf.TYPE <> 1))分支，让优化器难以选择合适索引，易触发全表扫描。
• 隐式关联导致笛卡尔积风险：使用逗号分隔表的旧风格关联，未显式指定JOIN类型，优化器可能误判表数据量，选择笛卡尔积合并。

二、具体调优步骤 #

1. 优化自关联子查询，简化条件 #

将submit_flow自关联改为显式INNER JOIN，去除重复条件，只保留外层需要的字段，减少冗余计算：

select 
 my.flow_id myflow_id, 
 done.flow_id flow_id, 
 done.status SFSTATUS,
 done.submit_id, done.type, done.prc_date
from submit_flow my
inner join submit_flow done 
  on my.submit_id = done.submit_id
  and my.check_ord = done.check_ord
  and my.emp_id = '1'
where done.status in (1,2,3,4)
  and (
    (done.mainflow = 1 and done.check_ord > 0) 
    or (done.emp_id = '1' and done.type in (1,2,3) and done.type = my.type)
  )

2. 修复字符串拼接导致的索引失效 #

方案一：创建基于函数的索引

create index idx_smst_namespace_name on service_mst (namespace||'.'||name, not_view_at_inouttray);

方案二：新增冗余字段+普通索引（推荐，性能更优）

alter table service_mst add full_service_name varchar2(200);
update service_mst set full_service_name = namespace||'.'||name;
create index idx_smst_fullname on service_mst (full_service_name, not_view_at_inouttray);

之后将not exists子查询条件改为smst.full_service_name = sbmt.SERVICENAME。

3. 拆分OR条件，用UNION ALL替代 #

OR条件是优化器的常见陷阱，将查询拆分为两个逻辑分支，用UNION ALL合并（确保分支无重复数据）：

-- 分支1：sf.TYPE = 1的场景
select 
 'RECEIVE-DONE' OP_TYPE,   
 sbmt.SUBMIT_NO, 
 wsr.USE_LUMP_PROC, 
 sf.* 
from (
 -- 引用优化后的自关联子查询
) sf
inner join submit_list sbmt on sf.SUBMIT_ID = sbmt.SUBMIT_ID
inner join emp emp on sbmt.EMP_ID = emp.EMP_ID
inner join wfserveroute wsr 
  on sbmt.FLOW_SCHEM = wsr.SCHEM 
  and sbmt.SERVICENAME = wsr.FULLNAME
where sf.TYPE = 1 
  and sbmt.COMPLETED = 1 
  and sbmt.status = 2
  and wsr.CASE = 0
  and not exists(
    SELECT 1 FROM service_mst smst 
    WHERE smst.full_service_name = sbmt.SERVICENAME
    and smst.not_view_at_inouttray = 1  
  )

union all

-- 分支2：sf.TYPE <> 1的场景
select 
 'RECEIVE-DONE' OP_TYPE,   
 sbmt.SUBMIT_NO, 
 wsr.USE_LUMP_PROC, 
 sf.* 
from (
 -- 引用优化后的自关联子查询
) sf
inner join submit_list sbmt on sf.SUBMIT_ID = sbmt.SUBMIT_ID
inner join emp emp on sbmt.EMP_ID = emp.EMP_ID
inner join wfserveroute wsr 
  on sbmt.FLOW_SCHEM = wsr.SCHEM 
  and sbmt.SERVICENAME = wsr.FULLNAME
where sf.TYPE <> 1
  and wsr.CASE = 0
  and not exists(
    SELECT 1 FROM service_mst smst 
    WHERE smst.full_service_name = sbmt.SERVICENAME
    and smst.not_view_at_inouttray = 1  
  )

order by prc_date DESC

拆分后每个分支条件明确，优化器可选择对应索引，避免全表扫描。

4. 显式指定JOIN类型，规避笛卡尔积 #

将原查询的逗号分隔表关联改为显式INNER JOIN，明确表之间的依赖关系，让优化器清晰判断关联逻辑：

from (优化后的自关联子查询) sf
inner join submit_list sbmt on sf.SUBMIT_ID = sbmt.SUBMIT_ID
inner join emp emp on sbmt.EMP_ID = emp.EMP_ID
inner join wfserveroute wsr 
  on sbmt.FLOW_SCHEM = wsr.SCHEM 
  and sbmt.SERVICENAME = wsr.FULLNAME

5. 创建关键复合索引 #

为以下字段创建复合索引，覆盖过滤、关联和排序需求：

• submit_flow：(emp_id, submit_id, check_ord, status, mainflow, type, prc_date)
• submit_list：(SUBMIT_ID, TYPE, EMP_ID, COMPLETED, status, FLOW_SCHEM, SERVICENAME, SUBMIT_NO)
• wfserveroute：(SCHEM, FULLNAME, CASE, USE_LUMP_PROC)
• emp：确保EMP_ID有唯一索引（若主键不是该字段）

6. 锁定高效执行计划（应对环境差异导致的计划漂移） #

如果部分环境仍出现执行计划波动，可通过以下方式锁定计划：

• 使用DBMS_SQLTUNE.CREATE_SQL_PROFILE生成SQL Profile并绑定到目标查询，强制优化器选择高效计划。
• 使用/*+ USE_PLAN(...) */提示，直接指定高效执行计划的XML格式（需先获取目标计划的XML）。

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