Oracle优化器架构变化和特定行为

SQLplusDB

发布于 2020-03-26 10:05:35

7650

发布于 2020-03-26 10:05:35

  "知史以明鉴,查古以至今"

概述

优化器（Optimizer ）是Oracle数据库最重要的部件之一，随着Oracle数据库每个新版本的发布，优化器都会得到增强并追加一些新功能，本文将针对各个版本出现的新特性背景和发展进行简单介绍.

优化器的进化

关于优化器的进化，是一个不断自我学习和加强的过程。如同人类的进化，通过在解决实践中遇到的各种问题的过程中，不断改进和推陈出新，得到发展和完善。

如上图所示，Oracle数据库不断地自我完善着：

Oracle数据库从9i版本开始，为了使SQL文能够更好的共享，引进了用于游标共享的CURSOR_SHARING参数。而为了使选择条件中包含绑定变量的SQL能够更准确的估算选择基数（cardinality ），引入了绑定变量窥视(Bind Peek)功能。

为了解决因为统计信息缺失或者统计不够准确而引起的问题，在紧接着推出的9iR2的版本上，Oracle又推出了动态采样（Dynamic Sampling）功能，使SQL文在硬解析过程中动态地收集统计信息。 —该功能在以后的版本上得到更进一步的增强，从11.2.0.4版本改称为动态统计（Dynamic Statistics ）。

由于绑定变量窥视(Bind Peek)只有在硬解析（Hard Parse）时，才会代入绑定变量的值来估算选择基数（cardinality ），所以通过绑定变量窥视(Bind Peek)功能做成的执行计划，有时候因为数据分布不均或者数据倾斜，针对某些变量值的执行可能不是最优的，甚至可能引起很严重的性能问题，因此，在11g版本上，Oracle推出了自适应游标共享(Adaptive Cursor Sharing）功能，使包含绑定变量的同一条SQL语句在多次执行时，能够根据绑定变量值和执行过程中收集信息的反馈，可以使用多个不同执行计划，实现共享游标[Cursor sharing]能够“Adaptive”（自我调节）。

在11gR2的版本上，推出了基数反馈（Cardinality Feedback 以后简称CFB）功能，进一步扩展了执行过程中反馈机制，使一些没有绑定变量，包含复杂条件等SQL语句也能够获益。通过基数反馈（CFB）功能，在SQL执行过程中同时收集中间结果信息，如果CBO根据统计信息估算出的基数(Computed cardinality) 和SQL执行时的实际值差距很大的情况发生时，在SQL下次执行时，根据实际值调整基数，重新生成执行计划。 —该功能在12c版本上得到更进一步的增强，改称为统计反馈（Statistics Feedback）。

12C的版本开始，数据库把优化器的功能更推上了一个台阶，追加了自适应计划等功能并整合了之前版本的各个功能，形成一套完整自适应查询优化（Adaptive Query Optimization ）功能集合。通过这个功能集合使优化器能够包括在初次做成执行计划过程中也能够实时地调整执行计划和为后续执行提供更加准确的输入统计信息。

自适应查询优化功能集合主要包括两个方面：・用于初次做成执行计划过程中实时地调整执行计划的自适应计划功能（Adaptive Plans）；・为后续执行提供更加准确的输入信息的自适应统计信息功能（Adaptive Statistics）。

优化器的架构变化

优化器能够产生最优的执行计划，主要取决于代价模型（Cost Model）本身和用于代价模型进行加工的输入信息（如对象统计信息和系统统计信息）。优化器的架构的发展也是基于这两方面，不断提供更加准确，有效的能反映出真实数据分布的输入统计信息；改进代价模型（Cost Model）本身架构和算法。

11g之前版本的架构

11g之前的版本，SQL在解析过程中主要经过语法分析，语义分析，查询转化，代价分析，估算执行计划，生成最优执行计划和游标，执行游标。在这个过程中，一旦选择了执行计划，在后来的执行过程中，如果不发生硬解析，就会一直使用这个执行计划。为了使优化器正确估算出执行计划及其操作代价，我们需要通过定期收集统计信息，动态采样和绑定变量窥视等提供更加准确的对象统计信息和系统统计信息。

11g版本的架构

在11g之后的版本，在传统的处理流程基础上，优化器又通过以下的处理流程增加了反馈机制：

    1.在SQL的执行过程中或执行后，收集一些实际的统计信息，并把这些信息更新到游标信息中。
    2.在下次SQL解析过程中，使用收集的实际统计信息，更具需要生成新的执行计划。

通过以上反馈机制，能够使统计信息更加准确，更加能发映出真实数据情况，在下次SQL执行时优化器选择出最优的执行计划。基于这种架构的特性主要包括：实现自适应游标共享(Adaptive Cursor Sharing）和基数反馈（Cardinality Feedback）功能。

12C版本的架构

在12c的版本上，又有了以下非常重要的改进：

0.SQL文初次执行时，优化器在做成的执行计划中会植入统计收集器（statistics collectors），预设临界值,当收集的统计超过临界值时，调整连接方式或者并行分配方式。
3.把反馈机制的信息，通过指令的形式存储下来，以供下次解析使用。

基于这种架构的特性主要包括：自适应连接方法（Adaptive Join Methods）和自适应并行分配方法（Adaptive Parallel Distribution Methods）；自动重新优化（Automatic Reoptimization）以及SQL计划指令（SQL Plan Directives）。另外，在12c上还对以前版本的各个功能的进行了增强和改进，形成一套更加智能和有效的执行计划优化机制。

12C 自适应查询优化(Adaptive Query Optimization)概述

我们知道，在12c版本之前，优化器根据统计信息先决定执行计划，然后根据决定的执行计划执行SQL，在SQL执行期间一般不会改变执行计划。但如果由于某些原因（如统计信息不准确，CBO估算错误等）导致执行计划不是最优的，SQL执行时就可能产生性能问题。 Oracle 数据库在12c版本上对此进行了增强，使优化器能够在某些SQL运行时也能自己改变执行计划，并同时收集运行时的一些额外信息帮助产生更优的执行计划。

以下是对12c的自适应查询优化功能集合的总体图。

FeatureSets for adaptive query optimization

通过上面的图，我们可以看到在12c中的自适应查询优化功能集合，主要是包括2部分：

左侧：在SQL第一次执行时实时进行优化的自适应执行计划(Adaptive Plans)

右侧：在SQL执行前提供更准确输入统计信息的自适应统计信息(Adaptive Statistics)

更详细的分类如下：

自适应执行计划(Adaptive Plans)包括
      -自适应连接方法（Adaptive Join Methods）
      -自适应并行分布方法（Adaptive Parallel Distribution Methods）。
自适应统计信息(Adaptive Statistics) 包括
      -动态统计（Dynamic Statistics）
      -自动重新优化（Automatic Reoptimization）
        --统计反馈（Statistics Feedback）
        --性能反馈（Performance Feedback）
      -SQL计划指令（SQL Plan Directives）

其中，红边框的部分是12c架构变化后新追加功能：自适应执行计划(Adaptive Plans)和SQL计划指令（SQL Plan Directives）。

其他为对以前版本的扩展和增强，如动态统计（Dynamic Statistics）对以前版本的动态采样（Dynamic Sampling）进行了扩展。自动重新优化（Automatic Reoptimization）的统计反馈（Statistics Feedback）是对以前版本的基数反馈（Cardinality Feedback）进行了扩展。

在后续的文章中，将进一步介绍上面各个版本的优化器功能和一些最佳实践以及案例。