CBO-Query transformations

一直想写一篇关于CBO的总结，但是我比较懒也不擅长写文章，每次只需要1秒钟就打消了这个念头，这次趁着西区培训的机会，咬咬牙写一写。。。

CBO基于成本的优化器，简单来说作用就是通过对成本的估算选择最佳的执行计划，如果要深入去研究CBO，那么10053肯定是唯一的利器，10053详细的描述了一个sql生成执行计划的过程，这玩意实战性不强，因为sql优化基本是以结果为导向的，只要sql执行变快了就行，优化方法实在太多了(运气好随便试试也能ok），但是如果是去研究CBO那么10053就非常合适了，如果是要确定sql性能差的具体原因或者CBO相关(查询转换、成本估算)的bug的话，10053也非常合适。

从10053观察CBO生成执行计划基本流程：

• 1.QUERY BLOCK SIGNATURE(物理查询块注册),根据sql的结构划分出对应的查询块进行注册
• 2.OPTIMIZER Environment，该部分信息存储在子游标的heap 0中，主要是sql运行的环境信息(优化器各种参数以及PSU版本的fix_control)
  • V$SYS_OPTIMIZER_ENV
  • V$SES_OPTIMIZER_ENV
  • V$SQL_OPTIMIZER_ENV
• V$SYSTEM_FIX_CONTROL
• V$SESSION_FIX_CONTROL
• 3.Query transformations -> Final query after transformations
• 4.QUERY BLOCK SIGNATURE(查询转换后的逻辑查询块注册)
• 5.OPTIMIZER STATISTICS AND COMPUTATIONS
  • SYSTEM STATISTICS INFORMATION
  • BASE STATISTICAL INFORMATION
• 6.GENERAL PLANS -> CHOOSE BEST PLAN

个人认为其中最重要的部分就是Query transformations和OPTIMIZER STATISTICS AND COMPUTATIONS。所以本次文章以及后续的重心也在这两部分内容。

Query Transformation

查询转换是CBO非常核心的一部分，它的作用主要是不改变sql逻辑的情况下，改变查询块之间的关系，尽可能的获得最佳的执行计划。

查询转换主要分为启发式查询转换和非启发式查询转换。非启发式查询转换由参数_optimizer_cost_based_transformation控制，并由参数_optimizer_cbqt_factor控制查询转换的成本因子，作用应该和optimizer_index_cost_adj类似

• 启发式查询转换-rule based，常见的如simple view merging,order by elimination，count(col) to count(*) transformation等等
• 非启发式查询转换-cost based，常见的如join predicate push-down，complex view merging，or-expansion等等，非启发式查询转换要求转换后cost小于未转换cost。

_optimizer_cost_based_transformation可选项two_pass, off, on, linear, iterative, exhaustive，除了off，其他都是开启基于成本的查询转换，不同之处可能是评估查询转换成本的方式不同，这是我的猜测，因为这方面的资料实在是没有找到。

以JPPD为例，通过10053可以发现，11.1.0.7版本虽然_optimizer_cost_based_transformation也是LINEAR，但是对于JPPD实际采用的是two_pass，而11gr2版本之后都是linear
11.1.0.7
JPPD: Using search type: two pass
11.2.0.4
JPPD: Using search type: linear

常见的查询转换种类(19C)：

• JPPD – join predicate push-down
• OJPPD – old-style (non-cost-based) JPPD
• FPD – filter push-down
• PM – predicate move-around
• CVM – complex view merging
• SJC – set join conversion
• SU – subquery unnesting
• OBYE – order by elimination
• OST – old style star transformation
• ST – new (cbqt) star transformation
• CNT – count(col) to count(*) transformation
• JE – Join Elimination
• JF – join factorization
• DP – distinct placement
• VT – vector transformation
• AAT – Approximate Aggregate Transformation
• ORE – CBQT OR-Expansion
• LORE – Legacy OR-Expansion

对于每一种查询转换都会在解析的时候调用对应的driver入口，进行查询转换，当遇到查询转换解析异常时，可以通过short_stack去进行深入分析。

• kkqctdrv kernel compile query cost based query transformation driver
• kkqctdrvBJ kernel compile query cost based query transformation driver bushy join
• kkqctdrvCVM kernel compile query cost based query transformation driver complex view merging
• kkqctdrvDP kernel compile query cost based query transformation driver distinct placement
• kkqctdrvGBP kernel compile query cost based query transformation driver group by placement
• kkqctdrvIT kernel compile query cost based query transformation driver initiate cost-based transformation
• kkqctdrvJF kernel compile query cost based query transformation driver join filter
• kkqctdrvJPPD kernel compile query cost based query transformation driver Join Predicate Push Down
• kkqctdrvORE kernel compile query cost based query transformation driver or-expansion
• kkqctdrvPU kernel compile query cost based query transformation driver PU
• kkqctdrvST kernel compile query cost based query transformation driver star transformation
• kkqctdrvSU kernel compile query cost based query transformation driver subquery unnesting
• kkqctdrvTE kernel compile query cost based query transformation driver table expansion
• kkqctdrvTD kernel compile query cost based query transformation driver transformation directives
• kkqctdrvVM kernel compile query cost based query transformation driver view merging
• kkqctdrvVT kernel compile query cost based query transformation driver vector transformation
• kkqoredrv kernel compile query or-expansion driver
• kkqojedrv kernel compile query outer join elimination driver
• kkqtedrv kernel compile query table expansion driver

个人认为还是非常有必要对CBO各种查询转换深入学习，并且融会贯通，才能对sql优化水平有质的飞跃。后续的文章会分别围绕一些查询转换展开。