崔华_Oracle数据库优化器探秘

Oracle数据库优化器探秘崔华（dbsnake)AboutMe•中航信工程师•OracleACE•ACOUG成员什么是Oracle里的优化器•优化器（Optimizer）是Oracle数据库中内置的一个核心子系统，你也可以把它理解成是Oracle数据库中的一个核心模块或者一个核心功能组件，优化器的目的是按照一定的判断原则来得到它认为目标SQL在当前情形下最高效的执行路径（AccessPath），也就是说优化器的目的就是为了得到目标SQL的执行计划•依据选择执行计划时所用判断原则的不同，Oracle数据库里的优化器又分为RBO和CBO这两种类型Oracle数据库里SQL语句的执行过程Oracle数据库里SQL语句的执行过程-解析•语法检查•语义检查•查找SharedCursor查找SharedCursor的过程Oracle数据库里SQL语句的执行过程-如果找到了SharedCursor，优化器就可以滚粗了Oracle数据库里SQL语句的执行过程-如果找不到SharedCursor，优化器这才粉墨登场RBO的缺陷•选择执行计划时没有考虑目标SQL中所涉及到的各个对象的数据量、数据分布，只根据内置规则来决定执行计划——一招鲜、吃遍天•执行计划一旦出了问题，我们很难对其做调整•目标SQL的写法、甚至是目标SQL中所涉及到的各个对象在该SQL文本中出现的先后顺序和这些对象在数据字典缓存中的缓存顺序都可能会影响RBO对于该SQL执行计划的选择•Oracle数据库中很多很好的特性、功能均不能在RBO下使用，因为它们均不被RBO所支持（例如哈希连接、函数索引等）CBO的局限•CBO会默认认为目标SQL语句where条件中出现的各个列之间是独立的，是没有关联关系的•CBO会假设所有的目标SQL都是单独执行、并且是互不干扰的•CBO对直方图统计信息有诸多限制：－在Oracle12c之前，Frequency类型的直方图所对应的Bucket的数量不能超过254－在Oracle数据库里，如果针对文本型的字段收集直方图统计信息，则Oracle只会将该文本型字段的文本值的头32个byte给取出来（实际上只取头15个byte）并将其转换成一个浮点数，然后将该浮点数作为上述文本型字段的直方图统计信息存储在数据字典里•CBO在解析多表关联的目标SQL时，可能会漏选正确的执行计划CBO的优化方向•数据库的升级、统计信息的变更、CBO某些成本计算公式的先天不足等因素都会导致目标SQL执行计划的变更——这通常需要DBA来手工调整执行计划（手工调整执行计划太麻烦了，常常是按下了葫芦起了瓢），我们希望Oracle数据库能自适应的自动调整不理想的执行计划，而且最好是一旦发现苗头不对，马上调整！•自适应查询优化•突破现有限制、持续优化已有功能Oracle12c的自适应查询优化AutomaticReoptimization包括Statisticsfeedback和Performancefeedback自适应执行计划之AdaptiveJoinMethods•初始表连接方法还是和原先一样：取决于现有的统计信息•最后采用的表连接方法直到目标SQL执行过程中才会确定•执行计划中多了一个组件：Statisticcollector，最后采用的表连接方法依赖于目标SQL执行过程中statisticscollector收集到的运行时统计信息•需要在一定程度上缓存目标SQL的执行结果，不能像原先的嵌套循环连接那样马上把执行结果返回给用户Demo:AdaptiveJoinMethods自适应执行计划之AdaptiveParallelDistributionMethods•哈希（Hash）•广播（Broadcast）•分区（Partition，包括Partition-WiseJoin和PartialPartition-WiseJoin）•排序（Order）•随机－QC(RANDOM)•HYBRID-HASH-执行计划中多了一个组件：Statisticcollector，最后采用的数据传递方法依赖于目标SQL执行过程中statisticscollector收集到的运行时统计信息，如果待传递的数据量小于2*DOP，则数据传递方法会从HASH变为BroadcastDemo:AdaptiveParallelDistributionMethodsDemo:AdaptiveParallelDistributionMethodsDemo:AdaptiveParallelDistributionMethodsDemo:AdaptiveParallelDistributionMethods自适应统计信息之Dynamicstatistics•以前叫Dynamicsampling，现在叫Dynamicstatistics•Dynamicstatistics会缓存在SGA中，它是基于目标SQL语句的where条件的，这意味着Dynamicstatistics可以被可以被不同的SQL语句所共享•Dynamicstatistics可以作为纠正统计信息缺失、不准的一种补充手段•优化器会根据目标SQL语句本身、已有统计信息和目标SQL的执行时间来决定是否采用DynamicstatisticsDemo:DynamicstatisticsDemo:Dynamicstatistics自适应统计信息之Statisticsfeedback•以前叫Cardinalityfeedback，现在叫Statisticsfeedback•在目标SQL执行过程中，如果执行计划中的预估统计信息和该SQL实际执行时的运行时统计信息存在巨大差异，那么该SQL下次执行时会硬解析，并且此时生成执行计划的依据会是之前执行时已经收集好的运行时统计信息•Cardinalityfeedback只会收集单表和groupby的运行时统计信息，Statisticsfeedback还会额外收集表连接的运行时统计信息•Cardinalityfeedback所对应的运行时统计信息存储在SharedCursor里，不能持久化；而Statisticsfeedback所对应的运行时统计信息虽然也不能持久化，但此时可以用SQLPlanDirectives（SPD)来优雅的缓解运行时统计信息不能持久化的问题，并且SPD可以持久化Demo:StatisticsfeedbackDemo:StatisticsfeedbackDemo:Statisticsfeedback自适应统计信息之SQLPlanDirectives•SQLPlanDirectives（SPD)用于优雅的缓解运行时统计信息不能持久化的问题，SPD可以持久化•针对目标SQL语句的where条件而不是像Statisticsfeedback那样针对整个目标SQL语句，这意味着SPD可以被不同的SQL语句所共享•SPD持久化存储在SYSAUX里，Oracle会自动管理，我们也可以使用DBMS_SPD来手工管理Demo:SQLPlanDirectivesDemo:SQLPlanDirectives这里STATE为“NEW”，其含义是说如果下次目标SQL的where条件包含了列OBJECT_ID和OBJECT_NAME且对这两列施加的是等值查询，则此时应该用DynamicstatisticsDemo:SQLPlanDirectivesDemo:SQLPlanDirectives这里STATE为“MISSING_STATS”，其含义是说这里列OBJECT_ID和OBJECT_NAME的统计信息缺失，Oracle可能会自动对列OBJECT_ID和OBJECT_NAME创建多列统计信息Demo:SQLPlanDirectivesDemo:SQLPlanDirectivesDemo:SQLPlanDirectives这里STATE为“HAS_STATS”，其含义是说这里列OBJECT_ID和OBJECT_NAME已经有了多列统计信息，后续用到这两列且对这两列施加等值查询的目标SQL将不再用上述SPD，而是会用已经创建的多列统计信息突破现有限制，持续优化已有功能之两种新类型的直方图统计信息突破现有限制，持续优化已有功能之两种新类型的直方图统计信息突破现有限制，持续优化已有功能之两种新类型的直方图统计信息突破现有限制，持续优化已有功能之两种新类型的直方图统计信息突破现有限制，持续优化已有功能之Onlinestatisticsgathering突破现有限制，持续优化已有功能之Onlinestatisticsgathering突破现有限制，持续优化已有功能之其他•PartialJoinEvaluation•Nullacceptingsemi-joins•ScalarSubqueryUnnesting•Multi-TableLeftOuterJoin•……Demo:PartialJoinEvaluationDemo:PartialJoinEvaluationDemo:Nullacceptingsemi-joinsDemo:Nullacceptingsemi-joinsDemo:ScalarSubqueryUnnestingDemo:ScalarSubqueryUnnestingDemo:Multi-TableLeftOuterJoinDemo:Multi-TableLeftOuterJoin总结：Oracle数据库优化器的进化史Thanks!