Oracle版本升级过程中，SQL性能下降的案例一则

╰半橙微兮° 2023-06-11 09:28 23阅读 0赞

**问题描述：**

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

一个业务系统，升级(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个字段都起到索引作用：

![640?wx\_fmt=png][640_wx_fmt_png]

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

![640?wx\_fmt=png][640_wx_fmt_png 1]

现场尝试设置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级处理。

从上面整个过程来看，找到SYS\_B\_n的线索-猜测-测试(11203、11204、12102、12201、18300几个主流版本上)-结论(11204到12201这几个版本，有bug，如果函数索引里面包含常量，无法被使用)-MOS的验证(Function Based Index on SUBSTR Not Used When Cursor\_Sharing=Force)，在了解这个问题的同时，应该学习这种判断问题的思路，这才是更重要的。

[640_wx_fmt_png]: /images/20230531/ef76a417024a405982d2a1c1aa33876f.png
[640_wx_fmt_png 1]: /images/20230531/25d30a7658d64d76b3a28182b53722e6.png