row_number()分析函数在12c版本的bug

客户的一套重要业务数据库(版本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做斗争,是技术人员自身价值的一种体现.