问题概述
如下所示的一条SQL（对相关对象信息做了脱敏处理），出现性能退化。
select *
from (select status,
odate,
ccode,
manabcode,
bcode,
wcode,
jcode,
ordicodex,
ordcodex as occyordcodex,
null as gcode,
null as statsgcode,
null as staname,
null as codecategory1,
null as prjcode,
htype,
:"SYS_B_00" as fcy,
:"SYS_B_01" as rcy1,
:"SYS_B_02" as shipfcy,
:"SYS_B_03" as shiprcy1,
:"SYS_B_04" as lrcegfcy,
:"SYS_B_05" as expfcy,
:"SYS_B_06" as zyfcy,
:"SYS_B_07" as zyrcy1,
:"SYS_B_08" as operationrcy1,
dc,
ccodetrust,
fcode,
null as ccodekind,
null as codecategory,
null as tccode,
null as seriesoflimit,
settlementremark,
frerate1,
sheetcode,
:"SYS_B_09" as puroperationrcy1,
:"SYS_B_10" as saloperationrcy1,
:"SYS_B_11" as recfcy,
null as prjicode,
corpbcode,
:"SYS_B_12" as oprecfcy,
:"SYS_B_13" as exprcy1,
null as statsgtype,
null as jcodercy1,
tccodetype,
:"SYS_B_14" as pafcy,
null as tgtmanabcode,
:"SYS_B_15" as ysbflag
from (select - :"SYS_B_16" dc,
p.odate,
p.purordicodex as ordicodex,
p.purordcodex as ordcodex,
p.ccode,
p.manabcode,
p.bcode,
p.wcode,
p.jcode,
p.corpbcode,
p.ccodetrust,
p.fcode,
p.htype,
p.settlementremark,
p.frerate1,
p.status,
p.sheetcode,
p.oriratifydate,
p.predate,
case
when st.tccode = sc.tccode then
:"SYS_B_17"
else
:"SYS_B_18"
end tccodetype
from p
left join (select max(tccode) tccode, sccode
from rconfig
where rtype = :"SYS_B_19"
group by sccode) sc
on sc.sccode = p.ccode
left join (select max(tccode) tccode, sccode
from rconfig
where rtype = :"SYS_B_20"
group by sccode) st
on st.sccode = p.ccodetrust
where p.status in (:"SYS_B_21", :"SYS_B_22")
and (p.ccodetrust in
(select sccode
from rconfig
where tccode in (select tccode
from rconfig
where rtype = :"SYS_B_23"
and sccode in (:"SYS_B_24")
group by tccode)
and rtype = :"SYS_B_25"
union all
select ccode
from cde
where ccode in (:"SYS_B_26")))
and nvl(p.htype, :"SYS_B_27") <> :"SYS_B_28"
and :"SYS_B_29" = :"SYS_B_30"and
p.manabcode = :"SYS_B_31"
and exists
(select :"SYS_B_32"
from cde
where nvl(isinnerccode, :"SYS_B_33") = :"SYS_B_34"
and ccode.ccode = p.ccode)
and p.ccodetrust is not null
and ((st.tccode <> sc.tccode and p.psmode = :"SYS_B_35") or
(st.tccode = sc.tccode))
union all (select :"SYS_B_36" as dc,
p.odate,
p.salordicodex as ordicodex,
p.salordcodex as ordcodex,
p.ccode,
p.manabcode,
p.bcode,
p.wcode,
p.jcode,
p.corpbcode,
p.ccodetrust,
p.fcode,
p.htype,
p.settlementremark,
p.frerate1,
p.status,
p.sheetcode,
p.oriratifydate,
p.predate,
:"SYS_B_37" as tccodetype
from p
where p.status in (:"SYS_B_38", :"SYS_B_39")
and nvl(p.htype, :"SYS_B_40") <> :"SYS_B_41"
and p.salordicodex = p.salordicode
and :"SYS_B_42" = :"SYS_B_43"and
p.manabcode = :"SYS_B_44"
and exists
(select :"SYS_B_45"
from cde
where nvl(isinnerccode, :"SYS_B_46") =
:"SYS_B_47"
and ccode.ccode = p.ccode)
and (p.ccodetrust is not null and
(exists
(select :"SYS_B_48"
from pg
left join pp
on pp.purordicodex = pg.purordicode
left join sg
on sg.prjicode = pg.prjicode
left join (select max(tccode) tccode,
sccode
from rconfig
where rtype = :"SYS_B_49"
group by sccode) sc
on sc.sccode = pp.ccode
left join (select max(tccode) tccode,
sccode
from rconfig
where rtype = :"SYS_B_50"
group by sccode) st
on st.sccode = pp.ccodetrust
where sg.salordicodex = p.salordicodex
and (nvl(st.tccode, :"SYS_B_51") <>
sc.tccode)) or
(exists
(select :"SYS_B_52"
from sg
where (sg.prjicode is null or
(prjicode is not null and
not exists
(select :"SYS_B_53"
from pg
where pg.prjicode = sg.prjicode)))
and p.salordicodex = sg.salordicodex))) and
(p.ccodetrust in
(select sccode
from rconfig
where tccode in
(select tccode
from rconfig
where rtype = :"SYS_B_54"
and sccode in (:"SYS_B_55")
group by tccode)
and rtype = :"SYS_B_56"
union all
select ccode
from cde
where ccode in (:"SYS_B_57"))))
union
select :"SYS_B_58" as dc,
p.odate,
p.salordicodex as ordicodex,
p.salordcodex as ordcodex,
p.ccode,
p.manabcode,
p.bcode,
p.wcode,
p.jcode,
p.corpbcode,
p.ccodetrust,
p.fcode,
p.htype,
p.settlementremark,
p.frerate1,
p.status,
p.sheetcode,
p.oriratifydate,
p.predate,
:"SYS_B_59" as tccodetype
from p
where p.status in (:"SYS_B_60", :"SYS_B_61")
and nvl(p.htype, :"SYS_B_62") <> :"SYS_B_63"
and p.salordicodex = p.salordicode
and :"SYS_B_64" = :"SYS_B_65"and
p.manabcode = :"SYS_B_66"
and exists
(select :"SYS_B_67"
from cde
where nvl(isinnerccode, :"SYS_B_68") =
:"SYS_B_69"
and ccode.ccode = p.ccode)
and (p.ccodetrust is null and
(p.ccode in
(select scde
from rconfig
where tccode in
(select tccode
from rconfig
where rtype = :"SYS_B_70"
and sccode in (:"SYS_B_71")
group by tccode)
and rtype = :"SYS_B_72"
union all
select ccode
from cde
where ccode in (:"SYS_B_73")))))) m
where (manabcode not in (:"SYS_B_74",
:"SYS_B_75",
:"SYS_B_76",
:"SYS_B_77",
:"SYS_B_78"))
and (manabcode = :"SYS_B_79" and
predate between TO_DATE(:"SYS_B_80", :"SYS_B_81") and
TO_DATE(:"SYS_B_82", :"SYS_B_83")))
where rownum <= :1问题原因
通过分析该SQL的SQLHC报告，我们发现其主要耗时在如下几步（对相关信息做了脱敏处理）：
对应的执行计划，如下图所示（对相关信息做了脱敏处理）：
上图中红框的几步，正是耗时是主要耗时的步骤。
其对应的SQL代码是如下这一段（对相关信息做了脱敏处理）：
(exists
(select :"SYS_B_48"
from pg
left join pp
on pp.purordicodex = pg.purordicode
left join sg
on sg.prjicode = pg.prjicode
left join (select max(tccode) tccode,
sccode
from rconfig
where rtype = :"SYS_B_49"
group by sccode) sc
on sc.sccode = pp.ccode
left join (select max(tccode) tccode,
sccode
from rconfig
where rtype = :"SYS_B_50"
group by sccode) st
on st.sccode = pp.ccodetrust
where sg.salordicodex = p.salordicodex
and (nvl(st.tccode, :"SYS_B_51") <>
sc.tccode))进一步分析，确认在两个做group by 的子查询上，其与外部表的连接条件列SCCODE上，有很好的过滤性。且该列上有索引存在。
因此，如果这里采用将连接条件推入子查询内，先用该条件过滤出极少量的记录，再进行group by的效率应该会较高。但使用谓词推入，目标子查询中的表就要做为Nested Loop连接的被驱动表。如果驱动表上产生的中间结果集很多，那么其就会被执行很多次，导致整体的资源开效可能更差。但经过检查，确认驱动表的结果并不多。
而优化器没有主动选择进行连接谓词推入，目前怀疑是优化器在估算不进行连接谓词推入的情况下，成本较低所致。如下图所示（对相关信息做了脱敏处理）：
如上图所示，我们可以看到父步骤的成本是475和645。而子步骤的成本已经上千了。正常情况下，父步骤的成本应该是包含子步骤的。怀疑是个BUG，但到Oracle的官方支持网站上，一时并没有找到有相应的信息。
解决方案
为了尽快解决该问题，我们建议采用添加HINT的方式，来引导优化器使用连接谓词推入的执行计划。即，在以下代码出添加push_pred的提示（对相关信息做了脱敏处理）：
(exists
(select /*+ push_pred(sc) push_pred(st) */ :"SYS_B_48"
from pg
left join pp
on pp.purordicodex = pg.purordicode
left join sg
on sg.prjicode = pg.prjicode
left join (select max(tccode) tccode,
sccode
from rconfig
where rtype = :"SYS_B_49"
group by sccode) sc
on sc.sccode = pp.ccode
left join (select max(tccode) tccode,
sccode
from rconfig
where rtype = :"SYS_B_50"
group by sccode) st
on st.sccode = pp.ccodetrust
where sg.salordicodex = p.salordicodex
and (nvl(st.tccode, :"SYS_B_51") <>
sc.tccode))添加后，其相应的执行计划如下图所示（对相关信息做了脱敏处理）：
整体执行时间下降到原来的约十分之一。
标签：ccode,pred,sccode,SYS,tccode,sql,push,where,select From： https://blog.51cto.com/u_13482808/7614532