一直想写一篇关于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。
1 2 3 4 5 6 7 8 9 10 11 |
SQL> @sp based_transformation -- show parameter by sp -- show hidden parameter by sp old 3: where x.indx=y.indx and ksppinm like '_%&p%' new 3: where x.indx=y.indx and ksppinm like '_%based_transformation%' NAME VALUE DESC ---------------------------------------- ---------- ------------------------------------------------------------------------------------------ _optimizer_cost_based_transformation LINEAR enables cost-based query transformation |
_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
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> @sp cbqt_factor -- show parameter by sp -- show hidden parameter by sp old 3: where x.indx=y.indx and ksppinm like '_%&p%' new 3: where x.indx=y.indx and ksppinm like '_%cbqt_factor%' NAME VALUE DESC ---------------------------------------- ---------- ------------------------------------------------------------------------------------------ _optimizer_cbqt_factor 50 cost factor for cost-based query transformation |
常见的查询转换种类(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优化水平有质的飞跃。后续的文章会分别围绕一些查询转换展开。