暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

66-11.2.0.3版本升级到18c之前的各版本,可能遇到严重性能问题


正文开始


问题描述:

    前几天有个学员向我咨询一个问题:

一个业务系统,升级(11.2.0.3升级到11.2.0.4)+ 迁移(AIX  到 Linux)到后,sql执行计划发生了较大的变化:类似nvl(xx_status, '0')这样的函数索引,无法使用了,系统里面有多个类似SQL,造成了非常严重的性能问题。11204版本上已经打了1907比较新的patch,排除了很久仍未解决。


    其中一个索引是这样建的,4字段联合索引,第二个字段用到了nvl函数:

create index IDX_name_xxx on t_ord_order(ORDER_STATUS,nvl(waybill_status, 0'),ACCEPT_ORG_CODE,ORDER_CREATE_TIME);


    11203版本执行计划,4个字段都起到索引作用:


    11204版本的执行计划,只有首字段起到索引作用,其他几个字段只能起到过滤作用(使用nvl函数的字段只能在回表时起到过滤作用):


    现场尝试设置optimizer_features_enable="11.2.0.3" ,不生效。用sql profile可以让执行计划选择回归正常,但是,因为涉及的sql比较多,用sql profile这种方法也不太好操作,需要找出根本原因和解决方案。

  

分析与解决


    从上面的执行计划中我们可以看到,有很多类似:SYS_B_n这种字符串,这是因为数据库初始化参数设置了 cursor_sharing=force (默认是exact;还有一个similiar,有很多问题,非常不推荐),根据这些信息,我们做一个简单的test case:


建个表,不需要插入数据:

create table t_force (id number,status number);

创建函数索引:

create index idx_t_force on t_force(nvl(status,0));

模拟故障现场的参数设置:

alter session set cursor_sharing=force;

执行SQL:

select id from t_force where nvl(status,0)=0;

查看执行计划:

select * from table(dbms_xplan.display_cursor());


把上面步骤分别在11203、11204、12102、12201、18300几个主流版本上分别执行,发现只有11203和18300两个版本正确的使用了函数索引,其他几个版本都无法使用索引,只能使用全表扫描的执行计划。


    根据这些结果,我们就可以得出一个结论,11204到12201这几个版本,有bug,如果函数索引里面包含常量,无法被使用


    按照找bug的思路,到MOS里面检索,找到Doc.ID 2276684.1 :

Function Based Index on SUBSTR Not Used When Cursor_Sharing=Force

 文章给出的两个解决方案是:

1、 打patch 25575369

2、alter system set cursor_sharing=exact;


总结与建议:


    cursor_sharing参数,强烈建议保持默认值exact,除非应用SQL代码没有使用绑定变量,为了避免大量硬解析对性能的影响,被逼无奈才需要把参数改成force。这个问题的源头还是在开发人员没有遵守开发规范,OLTP系统使用绑定变量是数据库开发的一个基本常识


    如果cursor_sharing是默认的exact,就没有这次的性能问题发生。Oracle的大规模测试,大部分是在默认参数情况下完成,除非遇到影响范围较大的bug,不建议在全局范围内修改参数(特别是隐含参数)。触发bug的情况都一些比较特殊的情况,一般可以在sql级处理。


(完)


上海站培训还有两天就要开始,需要提升自身技能的DBA或开发人员,建议不要错过这次机会。讲师介绍、部分培训材料和相关工具已经放在QQ群:16778072  , 欢迎取阅。参与技术讨论还可以加微信群(先加ora_service,再进群)。


 



最后修改时间:2020-05-08 00:14:07
文章转载自老虎刘谈oracle性能优化,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论