QL是描述性的语言，只是告诉数据库你期望的结果。SQL真正如何执行是执行计划决定的，而执行计划通过优化器来动态生成。优化器是数据库软件的内置算法，用于确定语句访问数据的行之有效的方法。优化器的优劣直接决定了数据库的处理能力的强弱。

什么是优化器

查询优化是关系型数据库管理系统和某些其他类型数据库（例如 NoSQL 和图形数据库）的一个功能。 查询优化器尝试通过考虑各种可能的查询计划来确定执行给定查询的优化方法。

一般来说，查询优化器是内置算法，不能被用户直接访问，一旦查询被提交到数据库服务器，并被解析器解析，它们就会被传递到查询优化器进行优化。

查询是对数据库信息的请求。它可以像“查找电话号码为 123-4567-8900 的人的地址”这样的简单需求，也可以更复杂，例如“查找深圳市 30 至 39 岁之间收入较他们的配偶更低的所有已就业已婚男性的平均工资”。 查询的结果是通过以获取所请求信息的方式处理数据库中的行来生成的。 由于数据库结构在大多数情况下都很复杂，尤其是对于复杂的查询，因此可以通过以不同的方式、通过不同的数据结构，或者以不同的顺序访问数据库来从数据库中获得查询所需的数据。 每种不同的方式通常需要不同的处理时间。 同一查询的处理时间也可能会有很大差异，从几分之一秒到几小时，具体取决于所选方法。 查询优化是一个自动化过程，其目的是找到在尽量短的时间内处理给定查询的方法。 时间上可能存在的巨大差异说明执行查询优化的必要性，在所有可能性中找到确切的合适查询计划通常非常复杂，本身很耗时，成本可能太高，而且通常实际上是不可能的。 因此，查询优化通常尝试通过比较几种常识性替代方案来近似得到上佳值，以在合理的时间内提供“足够好”的计划，该计划通常不会偏离上佳的可能结果太多。

Oracle优化器包含三个组件：查询转换器（Query Transformer）、成本估计器（Estimator）和计划生成器（Plan Generator）。下图说明了这些组件的关系：

SQL写法对优化器有多大的影响？

在实际工作中，经常碰到的一个需求是希望我们提供SQL写法的规范文档。查看Oracle的官方在线帮助文档，你会发现其实文档里是没有任何章节来描述如何编写优化的SQL的。Oracle优化器的一个目标就是不依赖于SQL的写法，任何写法的SQL都能提供一个优化的执行计划，这个就要归功于优化器的查询重写和查询转换功能。我们首先就以坊间被炒了好多年的NOT IN 和 NOT EXISTS 孰优孰劣的问题来简单说明一下。Oracle对查询重写和查询转换的支持，我们后面还会再详细介绍。

我们以下面两个表Dobj和Obj_Type为例，来说明不同的SQL写法在Oracle 19c中的表现：

我们构造的SQL的目的是查询在Dobj表中存在，但是在Obj_Type表中不存在的记录。首先使用两个表的外连接的方式，使用Oracle的语法，其SQL如下:

该SQL的执行计划是这样的：

接下来使用大家希望的NOT EXISTS来实现同样的业务逻辑，得到下面的SQL：

你会发现其执行计划和外连接的写法一摸一样，使用的HASH JOIN RIGHT ANTI的表连接来执行（注意其中的Plan Hash Value的值是一样的）：

最后我们用大家排斥的NOT IN的写法来实现同样的业务逻辑，其SQL如下：

其执行计划和上诉两种写法并没有质的变化，依然是使用了HASH JOIN RIGHT ANTI的表连接（注意其中第二行的HASH JOIN RIGHT ANTI NA中的NA，开动你的脑筋想一想为啥会出现呢？）：

上面三种不同写法的SQL，其执行计划和你想象中的是不是不一样呢？通过上述的简单对比我们即可发现，Oracle可以很好的处理不同的SQL语句，通过查询重写/转换让不同的SQL写法使用更优化的执行计划，从而保证执行效率。

Oracle优化器提供了各种不同的自动化优化技术来帮助SQL的执行，包括首次执行、执行过程中，以及后续执行的优化。下面我们就来看看Oracle都提供了哪些黑科技吧。

Oracle对统计信息的自动维护

统计信息是优化器生成执行计划的基础，统计信息的准确与否直接决定了执行计划的优劣。Oracle通过多种自动化技术来完善统计信息的维护，以减轻对应用开发和DBA的依赖。

自动统计信息收集作业

从版本10g开始，Oracle利用自动化任务和调度配置了一个自动统计信息收集的定时作业，该作业在维护窗口期（比如工作日的晚上）自动执行。该作业仅仅针对统计信息缺失或者过时了的对象做收集，Oracle通过追踪插入、删除、更新或者截断等操作不断监控DML操作影响的数据，从而确定统计信息过时的比例。调度程序会依赖对象的统计信息过时比例和对象的大小来确定需要收集统计信息的对象的优先级。

对于有数据倾斜的列，需要直方图来记录数据的实际分布。Oracle也可以自动决定哪些列需要生成直方图信息。Oracle会实时追踪列上使用的过滤条件（比如 = 和 < 等）来确定表上列的使用情况，只有列被使用做为过滤条件并且数据有倾斜的时候，才会被收集直方图。

在自动统计信息收集任务运行期间，通过滚动的方式让游标失效以便减少对整体系统的影响。

在线统计信息收集

在使用CTAS（Create Table As Select）或者IAS（Insert As Select）方式进行直接路径装载的时候，统计信息会被自动收集。在直接路径装载完成之后统计信息直接就有了，不需要再做一次表扫描来完成统计信息的收集。对于使用CTAS方式实现的维护操作将直接受益。而对于IAS，在18c之前的版本，只有目标表或者目标分区（指定分区的Insert）为空时才支持自动统计信息收集，而从18c开始，对于非空的表也支持。

有了在线统计信息收集，在大量装载数据之后，就可以立即进行查询操作了，省去了一次统计信息收集的工作。

实时统计信息收集

Oracle 19c 引入了实时统计信息，将在线统计信息收集扩展到传统的 DML 语句。 由于统计信息在调用DBMS_STATS 作业之间可能会过时，因此实时统计信息可以帮助优化器获得数据的实时信息从而帮助优化器生成更优化的执行计划。

考虑这样一个场景：事务当前正在向 OE.Orders 表不断的插入成千上万行数据，实时统计信息收集会跟踪插入行时不断增加的行数。 如果优化器对该表上的新查询执行硬解析，则优化器可以使用实时统计信息来获得更准确的成本估计。

实时统计信息功能通过参数OPTIMIZER_REAL_TIME_STATISTICS来控制，默认值是FALSE，即关闭实时统计信息收集功能。

在即将发布的Oracle 23c中，实时统计信息更是引入了更新的机器学习算法，即提高了实时统计信息收集的效率，而且也让实时统计信息更加准确。

自适应的统计信息

为了实时发现统计信息的问题并自动修正，Oracle引入多种不同的自适应的统计信息自动修复技术，以便解决潜在的执行计划问题。

动态统计信息

动态统计信息原来称为动态采样，在硬解析期间，优化器决定统计信息是否足以生成良好的计划，如果对象的统计信息丢失、陈旧或不完整，就会触发动态统计信息收集。它有助于为表扫描、索引访问、表连接和分组聚合等操作提供更准确的成本估算。

从 Oracle 12c 开始，优化器自动决定动态统计信息是否有用以及对所有 SQL 语句使用哪个采样的样本大小。决定使用动态统计信息收集的主要因素是可用的统计信息是否足以生成上佳执行计划。 如果统计信息不足，优化器将自动使用动态统计信息。

当 OPTIMIZER_DYNAMIC_SAMPLING 初始化参数未设置为 0 时，会启用自动动态统计信息收集。默认情况下，动态统计级别设置为 2。通常，优化器使用对象的默认统计信息而不是动态统计信息来计算表、索引和列优化期间所需的统计信息。优化器根据几个因素决定是否使用动态统计信息，比如过滤条件的复杂度、现有的统计信息和执行时间等。动态统计信息可以在多个查询间共享。

Cardinality Feedback

在SQL执行结束时，优化器会将估计值与实际执行的统计数据进行比较，如果估计值和实际值差异很大，则将强制使用前次实际执行的数据再次进行硬解析，这可能会产生不同的或者更好的执行计划。Cardinality Feedback在 11.2 中首次引入，优化器会收集有关执行期间得到的数据量的统计数据，如果执行统计数据变化很大，则语句将在下次执行时使用前次实际执行的统计数据进行硬解析。该信息仅存储在游标中，如果游标过时，信息将丢失。

很明显，Cardinality Feedback将会让由于统计信息过时等原因造成性能问题的SQL的后续执行直接获益。

SQL 计划指令

SQL 计划指令是优化器可用来生成更优化计划的附加信息和指令。该指令是优化器的“自我提示”，表明它错误地估计了某些类型谓词的基数，同时也提醒 DBMS_STATS 收集所需的统计信息，以便将来纠正错误估计。例如，当两个做连接的表在其连接列中存在数据倾斜的时候，SQL 计划指令可以指示优化器使用动态统计信息来获得更准确的连接基数估计。

优化器在查询表达式上定义 SQL 计划指令，例如，一起使用的两个列上的过滤谓词。指令不与特定的 SQL 语句或 SQL ID 绑定。 因此，优化器可以对不相同的语句使用同一个指令。指令可以帮助优化器处理使用类似模式的查询，例如除了数值之外其他部分完全相同的查询。

指令是在查询表达式上收集的，而不是在语句级别上收集的。现在执行计划的“注释”部分会显示用于语句的 SQL 计划指令的数量。

查询转换

SQL 是一种非过程语言，因此优化器可以自由地以任何顺序合并、重组和处理SQL的不同部分。数据库根据所访问对象所收集的统计信息来优化每个 SQL 语句。 优化器通过检查多种访问方法（例如全表扫描或索引扫描）、不同的连接方法（例如Nested Loops Join和Hash Join）、不同的连接顺序以及可能的转换来确定 SQL 语句的上佳计划。

什么是查询转换？

对于某些语句，查询转换器会确定将原始 SQL 语句重写为语义等效且成本较低的 SQL 语句是否有利。当存在可行的替代方案时，数据库单独计算替代方案的成本并选择成本更低的替代方案。

Oracle支持种类繁多的查询转换，主要查询转换包括以下类型：

• Filter predicate pushdown
• View merging
• Subquery unnesting
• Join predicate pushdown
• Group-by/distinct placement
• Join elimination
• Predicate pull-up
• Predicate Move-around
• Window function conversion
• Star transformation
• OR expansion
• …
• 优化器根据成本决定是否使用可用的查询转换。由于多种原因，包括提示或缺乏约束，优化器可能无法使用查询转换。 例如，Subquery Unnesting等转换不适用于混合分区表，因为混合分区表包含不支持约束的外部分区。由此可见，数据库的Shema设计，包括物理和逻辑设计都非常重要，只有做好了Schema设计，创建了合适的约束，数据库就有更多的查询转化和自动优化选项。

基于成本的查询转换

Oracle有两种类型的查询转换：启发式和基于成本。启发式查询转换是指如果基于某些规则可能会产生更优化的形式，则执行转换；基于成本的查询转换从Oracle 10g开始引入，指使用物理优化器比较各种查询形式的成本，选择成本更低的选项。

基于成本的查询转换是基于查询块进行的，依据自下而上（内部查询块在外部查询块之前优化）原则，其目标是：

• 访问路径选择
• 表连接顺序枚举
• 表连接方式选择

在查询转换过程中尝试的每个替代方案（访问路径、表连接顺序、表连接方法）均需计算成本，每个操作的成本由两个部分组成：I/O 和 CPU

• I/O 成本与（从表或索引）获取的块数成正比
• CPU 成本是各种操作的评估结果（评估谓词、排序行、哈希列值等）

I/O 和 CPU 成本合并为每个操作的总成本，优化器成本模型考虑了查询预计使用的机器资源。成本是一个内部数字度量，表示计划的估计资源使用情况。该成本特定于优化器环境中的查询，因此不能简单对比不同环境的成本来评估执行计划的好坏。为了估计成本，优化器会考虑以下因素：

• 系统资源，包括估计的 I/O、CPU 和内存
• 估计返回的行数（基数）
• 初始数据集的大小
• 数据分布
• 访问结构

由此可见，为了获得一个好的基于成本的查询转换，合理的物理设计和逻辑设计以及有代表性的统计信息依然非常重要。

Oracle优化器的自治

Oracle优化器的发展方向是实现自治，以减少对开发人员和DBA的依赖。在前面介绍统计信息的章节我们看到了Oracle优化器在统计信息方面实现了很多自治功能，接下来我们来看看在执行计划的生成和维护方面，Oracle有哪些自治功能。

自适应游标共享

自适应游标共享功能使包含绑定变量的单个语句能够使用多个执行计划。游标共享是“自适应的”，因为游标会调整其行为，以便数据库并不总是对每个执行或绑定变量值使用固定的执行计划。

通过绑定变量窥视，优化器会在第一次调用游标时查看用户定义的绑定变量的值。有绑定变量时，优化器通过“窥视”绑定变量带入的实际值来确定WHERE 子句条件的基数。 如果 WHERE 子句中的列上的不同数据值分布有倾斜，则该列上可能存在直方图。 当优化器使用用户定义的绑定变量的值并选择一个执行计划时，该计划可能并不适合所有值。

在自适应游标共享中，数据库不断监视不同绑定值访问的数据量，确保针对特定绑定值选择合适的游标。 例如，优化器可能为绑定值 10 选择一个计划，为绑定值 50 选择不同的计划。游标共享是“自适应”的，因为游标会调整其行为，以便优化器不会总是为每次执行或绑定变量值选择相同的计划。优化器会自动检测语句带入不同的绑定变量的不同执行何时会受益于不同的执行计划。

自适应执行计划

在 Oracle 数据库中，自适应查询优化使优化器能够对执行计划进行运行时调整，并发现可带来更好统计信息的附加信息。当现有统计数据不足以生成优化的计划时，自适应优化会很有帮助。

自适应执行计划使优化器能够在执行期间为语句做出计划决策。它包含多个预先确定的子计划和优化器统计信息收集器。根据执行期间收集的统计信息，在运行时选择更优的执行计划，从而能够在运行时修复某些类别的问题。

自适应执行计划非常有用，因为优化器有时会由于基数错误估计而选择次优的默认计划。 优化器能够根据实际执行统计信息在运行时选择不同的计划，从而产生更优化的最终计划。 选择最终计划后，优化器将其用于后续执行，从而确保次优计划不会被重用。

目前自适应执行计划的功能默认是打开的，它能够动态处理以下三种情形的SQL执行计划：

• 两个表的关联方式，动态确定使用Hash Join还是Nested Loops Join
• 并行执行时数据分发方式，动态决定使用Hash方式分发还是Broadcast方式分发
• 星型转换查询时位图索引的选择，会自动选择正确的位图索引组合以尽可能有效地减少相关的 RowID 集从而减少需要回表的数据量。

结语

Oracle数据库优化器持之以恒地在不断改进优化器的算法，一方面可以获得更佳的执行效率，另一方面可以极大的减少对编写SQL的开发人员的依赖。从自动化到自适应，再到自治，Oracle一直都在引领数据库优化器的发展方向，完全诠释了Oracle 数据库的愿景：让开发和运行任何规模的任何应用和分析都非常容易。

想了解更多考试相关

扫码添加上智启元官方客服微信👇