Oracle OFE (OPTIMIZER_FEATURES_ENABLE) 参数说明 -- cnDBA.cn

Oracle 数据库版本一直在不断迭代，其中一些重要的特性也在不断的引入，包括数据库SQL引擎也在不断的优化。http://www.cndba.cn/dave/article/4352

Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)，其中OFE (OPTIMIZER_FEATURES_ENABLE) 优化器参数就一个与版本新特性非常相关的重要的参数，该参数控制着优化器的行为。 http://www.cndba.cn/dave/article/4352

官方手册里对这个参数也有说明，如下：http://www.cndba.cn/dave/article/4352

https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_FEATURES_ENABLE.html#GUID-E193EC9E-B642-4C01-99EC-24E04AEA1A2C

OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.http://www.cndba.cn/dave/article/4352http://www.cndba.cn/dave/article/4352

For example, if you upgrade your database from release 11.1 to release 12.1, but you want to keep the release 11.1 optimizer behavior, you can do so by setting this parameter to 11.1.0.6. At a later time, you can try the enhancements introduced in releases up to and including release 12.1 by setting the parameter to 12.1.0.2.

在19c 中该参数的默认值是19.1.0. 我们可以在syste 和 session 级别进行修改。http://www.cndba.cn/dave/article/4352

官方手册对这个参数的描述也很清楚，这个参数的存在实际上也是解决我们在数据库升级过程中带来的性能问题。因为不同的Oracle 版本引擎特性不一样，比如我们原来的生产环境是在Oracle 11g的环境中开发和运行的。后来升级到19c 之后，SQL性能出现问题，或者SQL 语法执行报错。那么这很可能就是新特性导致的问题。 http://www.cndba.cn/dave/article/4352

所以生产环境在升级版本之前，都会进行大量的测试工作，以检查原环境能否在新版本中正常运行。其实从数据迁移的角度来看，迁移数据或者升级版本都是很短的过程，其中的风险也相对较小。升级版本带来的最大风险就是原SQL 在新版本中运行有问题。http://www.cndba.cn/dave/article/4352

如果遇到这种情况，我们一般要针对具体的SQL进行分析，比如新环境的统计信息是否准确，必要时也可以借助辅助工具，比如SQLHC 对SQL 进行分析，通过对比找出新版本和旧版本之间的差异，解决SQL的问题。如果短时间内无法正常运行，并且又影响到了系统的运行，那么就可以修改本篇所讲的参数：OFE (OPTIMIZER_FEATURES_ENABLE)。

该参数可以指定为如下版本：

OPTIMIZER_FEATURES_ENABLE = { 8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 | 9.2.0.8 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.1.0.5 | 10.2.0.1 | 10.2.0.2 | 10.2.0.3 | 10.2.0.4 | 10.2.0.5 | 11.1.0.6 | 11.1.0.7 | 11.2.0.1 | 11.2.0.2 | 11.2.0.3 | 11.2.0.4 | 12.1.0.1 | 12.1.0.2 | 12.2.0.1 | 18.1.0 | 19.1.0 | 20.1.0 | 21.1.0 }

设置之后，数据库就会使用对应版本的优化器来执行SQL语句。比如在19c 的实例中将参数设置为11.2.0.4，即使软件是19c，但优化器采用的行为确实11.2.0.4的，这样可以临时解决了性能问题。

注意降低该参数版本，也会禁止掉新版本的优化器特性，从而无法发挥新版本的优势，所以该参数仅用来临时解决问题，或者用来对比不同数据库版本的差异。 http://www.cndba.cn/dave/article/4352

修改OFE (OPTIMIZER_FEATURES_ENABLE) 的两种方法如下：

1. 使用SQL 修改：

SQL>alter system set optimizer_features_enable='11.2.0.4' scope=spfile;
SQL>alter session set optimizer_features_enable='11.2.0.4';

2. 在SQL 语句中添加hint：

SQL>SELECT /*+ optimizer_features_enable('11.2.0.4') */ NAME FROM cndba;

优化器相关的参数可以通过如下SQL 查询：http://www.cndba.cn/dave/article/4352

select x.ksppinm name,
       y.ksppstvl value,
       y.ksppstdf isdefault,
       decode(bitand(y.ksppstvf, 7),
              1,
              'MODIFIED',
              4,
              'SYSTEM_MOD',
              'FALSE') ismod,
       decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj
  from sys.x$ksppi x, sys.x$ksppcv y
 where x.inst_id = userenv('Instance')
   and y.inst_id = userenv('Instance')
   and x.indx = y.indx
   and x.ksppinm like '%opt%';

签到成功

CNDBA社区