项目场景:
Oracle版本:Oracle 11g
问题描述:
在开发产品需求的过程中,开发人员反馈执行SQL存在,首次查询速度快,第二次进行查询时,速度很慢,一直在查询,直到超时。
项目中的查询SQL如下:
select nvl(sum(sumcosts), 0) sumcosts,
nvl(sum(habitcosts), 0) habitcosts,
case
when nvl(sum(sumcosts), 0) = 0 then
0
else
nvl(sum(habitcosts), 0) / nvl(sum(sumcosts), 0)
end percentage
from (select sum(costs) sumcosts,
sum(case
when is_habitforming = 1 and is_psychotic = 1 then
costs
else
0
end) habitcosts
from peaas.outp_bill_items o
left join pdss.drug_map m
on 1 = 1
and o.item_code = m.drug_no_local
and o.drug_spec = m.drug_spec
and o.firm_id = m.manufacturer
where 1 = 1
and o.item_name is not null
and o.item_class in ('A', 'B', 'C')
and o.visit_date >= to_date('2019-03-01', 'YYYY-MM-DD')
and o.visit_date < to_date('2019-05-10', 'YYYY-MM-DD') + 1
union all
select sum(costs) sumcosts,
sum(case
when is_habitforming = 1 and is_psychotic = 1 then
costs
else
0
end) habitcosts
from mre.inp_bill_detail i
left join pdss.drug_map m
on 1 = 1
and i.item_code = m.drug_no_local
and i.drug_spec = m.drug_spec
and i.firm_id = m.manufacturer
where 1 = 1
and i.item_name is not null
and i.item_class_code in ('A', 'B', 'C')
and i.billing_date_time >= to_date('2019-03-01', 'YYYY-MM-DD')
and i.billing_date_time < to_date('2019-05-10', 'YYYY-MM-DD') + 1) t;
经过测试,此问题并不是所有服务器上都会出现。
原因分析:
经过查看SQL的执行过程,
explain plan for 【上述SQL】
select * from table(dbms_xplan.display());
并未发现异常,经过查阅资料,
发现与Oracle新特性【基数反馈】有关。
基数反馈(Cardinality Feedback )是 Oracle 11.2 中引入的关于 SQL 性能优化的新特性,该特性主要针对统计信息陈旧、无直方图或虽然有直方图但仍基数计算不准确的情况,Cardinality 基数的计算直接影响到后续的 JOIN COST 等重要的成本计算评估,造成 CBO 选择不当的执行计划。但是该参数存在不稳定因素,可能会带来执行效率的问题,建议关闭优化器反馈。
解决方案:
关闭 Oracle11g【基数反馈】特性
--查看参数
show parameter _optimizer_use_feedback
--关闭基数反馈
alter system set "_optimizer_use_feedback"=FALSE
亲测有效
相关资料参照:
https://blog.csdn.net/he3912377/article/details/52671523
http://blog.itpub.net/29785807/viewspace-2678238/