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级处理。

（完）