实践实战：在PoC中的Oracle 12c优化器参数推荐

最近，Oracle数据库优化器的产品经理 Nigel Bayliss 发布了一篇文档，介绍：Setting up the Oracle Optimizer for PoCs - 在PoC测试中优化器参数的设置和调节。优化器是 Oracle 数据库的核心组件，我们一起来看一看 12c 有哪些优化器的变化。

首先，作者描述了 POC 测试的基本原则，遵循 KISS 原则（ Keep it Simple Stupid）,从一个尽可能简单的基线开始；优先考虑稳定性和一致性；通过测试掌控变化；持续向前：

首先，在Oracle 12cR1中，Oracle 引入了一个重要的新特性：自适应查询优化器 - Adaptive Query Optimization，该特性的主要功能有两个：

• 对SQL的执行计划进行运行时（run-time）调整,（也就是在SQL执行过程中，具备动态改变执行计划的能力）;
• 在SQL执行过程中，动态统计和发现新的统计信息，以实现更佳的执行计划；

通过这个特性的描述，我们可以知道，当现有统计数据不足以生成最佳计划时，自适应查询优化器会很有用；当然相反方向是，如果我们数据库中执行计划是稳定的、优化的、满足需要的，那么这个新的特性对我们就基本不需要。

下图展示了这个新特性的两个路径：自适应执行计划、自适应统计信息。在12.1版本中，是否启用自适应优化器参数由初始化参数 optimizer_adaptive_features 决定。

基于在执行过程中获得的真实统计信息，优化器动态调整执行计划的能力可以极大地提高查询性能。

下图展示了一个最常见的场景，基于静态统计信息，Oracle选择了 Nest Loop的执行计划，当执行中动态统计信息（自适应统计信息）被收集之后，SQL的执行计划自动变更为 Hash Join 的执行方式。

在 Oracle 官方文档中，有这样一个举例，可以更清晰的看到这个过程和含义：

其动态变化过程如下图所示:

在使用这个新特性获取收益时，你必须记住以下这些可能带来的负面影响：

•优化器随着时间的推移而改变执行计划 - SQL执行计划可能随时间而改变 - 运行时性能可能不一致 •自适应统计数据在硬解析率较高的系统中有额外开销 - 高硬解析率不是最佳实践，但现实中太常见； - 比 Oracle 11g 更多的动态采样查询； - 可能会观察到 Library Cache 竞争； - 12cR1（在12cR2版中修复）中的'RC latch'（结果缓存）争用；

接下来进入 12.2 版本，在这个版本中 optimizer_adaptive_features 这个参数被废弃了，自适应优化器 的两部分，自适应计划（adaptive plans）和自适应统计信息（adaptive statistics）由2个独自的参数进行控制，这两个参数是：

optimizer_adaptive_plans 默认值为TRUE optimizer_adaptive_statistics 默认值为FALSE

在 Oracle 12.2 和 18c 中，推荐的缺省行为都已改变，自适应的执行计划，缺省未开启的自适应统计信息：

好了，有了这些基础知识之后，我们看看在 PoC 过程中，为了性能稳定，推荐的配置。

在 Oracle 12cR1 中，除了推荐安装必要的补丁修正外，剩下的主要推荐就是设置初始化参数，去除 optimizer_adaptive_features 设置，按照 12.2 和 18c 的缺省设置来选择 optimizer_adaptive_plans 和 optimizer_adaptive_statistics 参数。

最后一个推荐是：

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_STAT_EXTENSIONS','OFF')

此外，有了12.1的实践之后，看起来12.2 和 18c 的推荐就简单了许多：

可是经过了这么多讨论之后，我们发现 Adaptive Statistics 完全没有了用武之地，那么这个特性的场景是什么呢？

在 DSS/BI 的工作负载环境下，这个特性可能会发挥其优势：

除了自适应的优化器特性之外，还有很多新版本中需要注意的事项，比如SQL PLAN管理。

在 12c 和 18c 中，SQL计划管理的演变是自动化的：

- 如果您正在使用SQL计划管理（SPM），则替代计划可能会自动演变并被接受

- 您可能想要禁用自动优化作业或防止接受新计划...

总之，这些建议不仅仅适用于测试环境，在生产环境下，我们尤其需要关注这些新特性可能带来的新烦恼。