客户的一套重要业务数据库(版本12.1.0.2),偶尔会出现CPU比较高的情况(下面信息是从一个长间隔AWR报告截取),最高时候的CPU使用率是正常时段的15倍以上:
再取其中一段CPU使用率较高的短时AWR, 发现比正常时段多了几个类似的TOP SQL,消耗了大量的CPU资源, SQL执行时间从几分钟到几小时不等.
事后了解到,这是个统计业务,使用频率较低, 业务人员在使用时发现SQL执行时间长也没有反馈,而且执行时间长短跟统计的时间间隔大小有关,统计一两天也能在几十分钟内完成, 统计一个月可能就要几个小时.
查看TOP SQL的sql monitor信息, 发现下图标记1位置的优化器估值行数与实际行数偏差过大,导致执行计划错误的选择了Nested Loop,执行时间就变得不可接受了:
看一下对应的SQL代码段, 是一个使用了row_number()分析函数的inline view:
在相同版本的环境进行模拟,错误能够重现:
相同的SQL,在11.2.0.3 版本和12.2.0.1 版本,都不会出现这种估值错误的情况, 因此可以断定这是个bug.
到MOS检索相关信息(关键字: wrong Cardinality row_number) ,找到已知bug信息,Doc ID. 21971099.8 : Bug 21971099 - 12c wrong cardinality from SQL analytic windows functions
可以通过升级或打patch解决
临时解决方法:
set "_fix_control"='14826303:off'
sql级别: 加hint
select *+ OPT_PARAM('_fix_control' '14826303:off') */......
session级别:
alter session set "_fix_control"='14826303:off';
系统级别: 改参数(可不用重启,立即生效)
alter system set "_fix_control"='14826303:off';
总结:
类似的隐患我相信在很多系统都存在, 不同的版本, 不同的SQL,遇到的bug可能都不一样, 多看看AWR, 多分析一下消耗资源多,执行时间长的SQL, 就能够把这些隐患找出来, 解决了这些隐患, 数据库才能够健康稳定的运行.
新版本带来了很多新特性, 但也无一例外的引入了一些新的bug,与bug做斗争,是技术人员自身价值的一种体现.
