Oracle数据库12c release 2优化器详解

  序言:优化器是Oracle数据库最引人入胜的部件之一,因为它对每一个SQL语句的处理都必不可少。优化器为每个SQL语句确定最有效的执行计划,这是基于给定的查询的结构,可用的关于底层对象的统计信息,以及所有与优化器和执行相关的特性。

  本文来自Oracle 白皮书翻译(译者:苏旭辉 newkid),介绍了在Oracle数据库12c第二版中与优化器和统计信息相关的所有新特性并且提供了简单的,可再现的例子,使得你能够更容易地熟悉它们,尤其是当你从早先的版本进行迁移的时候。它还概括了已有的功能是如何被增强以改善性能和易管理性。

  有些Oracle优化器特性已经被从本文移除,并在它们自己的文章中被讨论。具体来说,它们是:

优化统计信息和优化统计信息顾问

SQL计划管理

近似查询处理

  为了得知Oracle优化器的全貌,我们建议你结合参考文献部分列出的相关文件来阅读本文。

  一、自适应查询优化

  到目前为止,Oracle 12c数据库中最大的变化是自适应查询优化。自适应查询优化是这样的一组功能,它们使得优化器能够对执行计划进行实时调整,并且发现能够导致更佳的统计信息的额外信息。当现有的统计信息不足以产生一个最佳的计划,这种新方法是极其有用的。自适应查询优化包括两个方面:

  自适应计划,它着重于改善一个查询的执行;

  自适应统计信息,它利用额外的信息来改善查询执行计划。

  

Oracle数据库12c release 2优化器详解

  (图1:新的自适应查询优化功能的构成组件)

  Oracle数据库12c第二版默认启用的自适应功能与Oracle数据库12c第一版不同。详细信息请参见下面的“初始化参数”部分。

  二、自适应计划

  优化器在某些条件下会选择自适应计划; 例如,当查询包括连接和复杂谓词,使得准确地估计基数变得很困难。自适应计划使得优化器能够把一个语句的计划推迟到执行的时候才确定。优化器在它所选择的计划(缺省计划)中植入统计收集器,从而在运行的时候,它能够判断基数估算与计划的操作所实际看到的行数是否有很大的偏差。如果有显著的区别,那么这个计划或者计划的一部分就会被自动调整,以避免不理想的性能。

  三、自适应的连接方式

  通过为计划中的某些分支预先确定多个子计划,优化器能够实时调整连接方式。例如,在图2中优化器的缺省计划为orders(订单)和 products(产品)表之间的连接选定的是嵌套循环连接,通过对products表的索引读取。另一个可选的子计划也同时被确定,它允许优化器将连接方式切换到哈希连接。在候选计划中products是通过全表扫描来读取的。

  在初次执行的时候,统计收集器收集了关于这次执行的信息,并且将一部分进入到子计划的数据行缓存起来。

  优化器会确定要收集哪些统计信息,以及如何根据统计的不同值来确定计划。它会算出一个“拐点”,两个计划选项在这个值是一样好的。

  例如,如果当orders表的扫描产生的行数少于10行,则嵌套循环连接是最佳,当 orders 表的扫描产生的行数多于10行,则哈希连接是最佳,那么这两个计划的拐点就是10。优化器会算出这个值,并且配置一个缓存统计收集器,使得它缓存并且计数至10行为止。如果扫描产生了至少10行,那么连接方式就被确定为哈希连接;否则,它就被确定为嵌套循环连接。在图2中,统计信息收集器正在监控和缓存来自orders表全扫描的数据行。基于从统计信息收集器中看到的信息,优化器会决定使用哪个子计划。

  在这个例子中,哈希连接被选中,因为来自orders表的行数大于优化器最初的估计。

  

Oracle数据库12c release 2优化器详解

  (图2:orders 和 products 表之间的连接的自适应执行计划。左边是缺省计划,右边是选中的计划)

  优化器能够从嵌套连接切换到哈希连接,反之亦然。可是,如果初始选中的连接方法是排序合并连接,则自适应不会发生。

  在缺省情况下,explain plan命令只会显示优化器选定的初始(缺省)计划。而DBMS_XPLAN.DISPLAY_CURSOR函数显示的是查询实际使用的计划。

  

Oracle数据库12c release 2优化器详解

  (图3: Explain plan 和 DBMS_XPLAN.DISPLAY_CURSOR 为图2所示的情形所输出的计划)

  为了看到自适应计划中所有的操作,包括统计收集器的位置,你必须在DBMS_XPLAN函数中指定额外的格式参数'adaptive'。在这个模式下,计划的id栏会出现一个额外的“-”记号,指明在计划中未被采用(非激活)的操作。

  

Oracle数据库12c release 2优化器详解

  (图4: 在DBMS_XPLAN.DISPLAY_CURSOR中使用'ADAPTIVE'格式参数得到的完整自适应计划)