12c中的SQL Plan Directive与Adaptive Execution Plan

郑全

SQL Plan Directive

在Oracle 12c中，数据库可以使用一个称之为sql plan directive的特性，该特性可以为优化器提供额外的信息并帮助优化器生成更好的执行计划。例如，某一个sql plan directive可以告知优化器收集缺失的统计信息，为某些列组创建扩展统计信息，或者是执行动态采样。在sql编译或者是执行期间，数据库会对那些缺失统计信息或者错估某些对象的cardinality的查询表达式（query expression）进行分析，从而生成sql plan directive。

当优化器生成一个执行计划后，该directive就可以为优化器提供与计划中的对象相关的额外信息。

需要注意的是，sql plan directive并不与某一具体的sql语句或者是SQL_ID绑定。其实际上是基于查询表达式的。例如，directive可以帮助优化器来处理具有相同模式的查询，像基于网页的查询，这些查询除了select部分不同之外，其他内容都是一样的。

数据库将sql plan directive的内容永久存储在SYSAUX表空间中。一旦生成执行计划，优化器就可以使用sql plan directive来获取计划中相关对象的更多信息。

这些directive信息将会被数据库自动维护，并按需创建，如果在创建之后一年内都未曾使用，oracle会将其清除。

这些directive可以通过DBA_SQL_PLAN_DIR_OBJECTS进行查询或者监控。通过在SYSAUX表空间持久保存与编译和执行相关的统计信息，sql plan directive可以提升执行计划的生成精度，并通过优化器将其应用到多条sql语句上。

Adaptive Execution Plan

也可以称之为自适应执行计划。该特性允许优化器在运行时能够自动调整一个性能较差的执行计划，并在接下来的执行中阻止优化器选择性能低下的执行计划。优化器使用所选定的执行计划，并在运行期间，如果优化器的评估不够理想时，它就能够检测到这一点。然后该执行计划就会被自动调整，并更符合实际条件。一个自适应执行计划，也就是一个当优化器评估的信息不够准确时，会被进行调整并发生改变的执行计划。

优化器能够基于在sql语句执行的过程中所收集的统计信息，来调整其执行计划。这种自适应机制所执行的真实计划，往往与在硬解析阶段就确定的执行计划不同。这样就提升了查询处理引擎（编译与执行）的功能，并使其能够生成更好的执行计划。

这里有两种自适应执行计划技术：

• dynamic plan（动态计划）：在sql语句执行期间，可以在子计划中选择动态计划。对于动态计划，优化器必须决定哪些子计划中包含动态计划，这里需要对选定的子计划收集统计信息，并为之启用线程。
• re-optimization（重优化）：与动态计划相反，重优化技术指的是，在当前计划执行完毕之后，优化器将会为下次执行重新生成执行计划。此时，优化器需要确定哪些统计信息需要收集，以及何时进行重优化。
  
  
  

简单点说，动态计划，是优化器在sql执行期间就能够通过收集统计信息来动态调整执行计划；而重优化，则是在下次执行之前，重新收集统计信息并确定新的执行计划。

需要注意的是，要想使用自适应执行计划这一特性，要么

optimizer_features_enable参数为12.1.0.1或者以上；

要么optimizer_adaptive_reporting_only参数为其默认值，即false。

optimizer_adaptive_reporting_only参数控制着自适应优化的报告模式。如果设置为true，则自适应优化将运行在reporting-only模式下，也就是说，会生成与自适应优化相关的信息，但是不采取任何动作来改变已有的执行计划。

当然，这里还有一个相关的参数：optimizer_adaptive_plans。该参数自12.2.0.1开始引入，用于控制优化器是否在sql运行时，基于收集的统计信息，来生成可选的执行计划。如果将其设置为false，则会关闭如下自适应特性：

• Nested loop join/hash join selection
• Star transformation bitmap pruning
• Adaptive parallel distribution method