oracle嵌套子查询优化,升级到12c遇到的性能问题(一):标量子查询嵌套,看上去挺美...

前段时间一个客户做系统迁移,顺便把数据库从11gR2升级到了12c(具体小版本未知,这里也不重要),升级后发现某个重要业务执行非常慢,一个使用db link的查询(客户当时的关注点是db link),执行计划发生了改变,尝试收集统计信息,使用各种hint组合,折腾了一个上午,还是没有搞定.

下午客户联系我,说通过设置

alter session set optimizer_features_enable='11.2.0.3'; 然后执行SQL就能恢复正常的执行计划,但是不知如何通过hint实现. 我告诉客户这种情况可以使用/*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.3') */ 这个hint 搞定,客户最终使用了/*+ OPT_PARAM('OPTIMIZER_FEATURES_ENABLE','11.2.0.3') */  hint 恢复到了升级前版本正常的执行计划(客户在使用第一个hint时可能没写正确,实际上都可以实现同样的目的).

然后客户把sql代码和升级前后的执行计划截图发给了我,我马上就知道了原因:这个sql使用了12c的标量子查询嵌套的新特性(Scalar Subquery Unnest),在2014年的一个内部技术交流中,我还着重讲了这个12c的新特性.于是,我很快给出了以下建议:

影响执行计划的真正参数是_optimizer_unnest_scalar_sq,可以通过/*+ OPT_PARAM('_optimizer_unnest_scalar_sq' 'false') */的hint来修正,或者在标量子查询的select部分使用/*+ no_unnest */ ,都能解决问题. 如果有很多类似SQL,则建议在系统级关闭:alter system set "_optimizer_unnest_scalar_sq"=false; (因为是升级的系统,这个改动不会有任何影响,原来的版本就没有这个功能).

针对标量子查询的优化,Oracle优化器做得并不是特别好,只有部分满足条件的sql可以做标量子查询嵌套的转换,而且在某些情况转换后可能性能更差(上面客户遇到的就是一个真实的案例). 下面用一个例子来说明这个问题:

在12c环境,先创建两个表(暂时不建索引):

create table test_o as select * from dba_objects;

create table test_u as select * from dba_users;

SQL:

SELECT   u.username,

(SELECT MAX(created) FROM test_o o WHERE o.owner = u.username) as max_created

FROM test_u u;

默认启用功能:_optimizer_unnest_scalar_sq=true  1654buffers

禁用功能: _optimizer_unnest_scalar_sq=false     77604buffers,两者相差40倍以上(数据量越大,性能差距会更大),

说明这个参数对提升当前SQL性能还是有很大的帮助.

如果建个索引,还是上面sql,看看是啥情况:

create index idx_to on test_o(owner,created);

启用功能:  351 buffer

禁用功能: 106 buffer

建了索引后,新特性的效率反而变差了.

如果是下面sql,让主查询返回的记录数再减少一些:

SELECT   u.username,

(SELECT MAX(created) FROM test_o o WHERE o.owner = u.username) as max_created

FROM test_u u

where username like'SY%';

启用功能:  156 buffer

禁用功能:  16 buffer

客户现场的SQL就跟上面的sql有点类似,升级前没啥问题,升级后效率下降了很多. 为什么会有这么大的差别? 应该是优化器没有更好地做cost评估,把不该unnest的执行计划,强行做了unnest.

上面的测试我没有贴出执行计划,有兴趣的朋友可以自己动手试试.你也可以收集一下统计信息,最好把直方图信息也搞出来,看看会不会得出不一样的结论.

总结:

数据库版本升级,做好升级前的测试很重要,测试时发现了问题有充足的时间去分析处理.匆忙升级上线,谁都不知道会遇到啥问题.每家的sql都不同,遇到的问题也是各不相同的.