PLSQL_性能优化系列04_Oracle Optimizer优化器

2014-09-25 Created By
BaoXinjian

一、摘要


1. Oracle优化器介绍

正文讲述了Oracle优化器的定义、工作规律同动用办法,兼顾了Oracle8i、9i以及最新的10g老三个本子。理解本文将有助于你再也好之再度管用的进行SQL优化办事。

2. RBO优化器

RBO是一律种植基于规则的优化器,随着CBO优化器的日趋前行和全面,在新式的10g版本中Oracle已经到头废弃了RBO。

方下Oracle8i或9i的众人要多或丢失的还见面逢RBO,因此于事无巨细介绍CBO之前,我们发必要简单回顾一下古的RBO优化器。

以RBO中Oracle根据可用之拜访路径和做客路径的号来选择执行计划,等级越强之访路径通常运行SQL越慢,如果一个告知句有差不多单途径而活动,Oracle总是挑三拣四等级比逊色的拜会路径。

3. CBO优化器结构

CBO是依据成本的优化器,它根据可用的顾路径、对象的统计信息、嵌入的Hints来挑选一个本钱低的实践计划。

 

二、RBO优化器


RBO是如出一辙栽基于规则之优化器,随着CBO优化器的逐渐发展和周,在最新的10g版本中Oracle已经彻底废弃了RBO。

1级:用Rowid定位单行

当WHERE子句被一直坐Rowid时,RBO走是路。Oracle不引进直接引用Rowid,Rowid可能会见出于本的更动而别,行迁移、行链接、EXP/IMP也会使Rowid发生变化。

2级:用Cluster Join定位单行

个别个表做等值连接,一着的接连字段是Cluster
Key,且WHERE中设有好保证该语句仅返回一行记录之准时,RBO走这个路。

3级:用带用唯一约束还是做主键的Hash Cluster
Key定位单行

4级:用唯一约束之字段或做主键的字段来恒定单行

5级:Cluster Join

6级:使用Hash Cluster Key

7级:使用索引Cluster Key

8级:使用复合索引

9级:使用单字段索引

10级:用索引进行有度限制的检索

如,column >[=] expr AND column
<[=] expr或column BETWEEN expr AND expr

或column LIKE ‘c%’

11级:用索引字段进展任界限的搜索

如,WHERE column >[=] expr 或 WHERE
column <[=] expr

12级:排序合并连接

13级:对索引字段使用MAX或MIN函数

14层:ORDER BY索引字段

15级:全表扫描

 

设若得以行使索引RBO会尽可能的去用索引而休是全表扫描,但是以下列一些场面RBO只能用全表扫描:

要是column1和column2是和一个阐明的字段,含有条件column1
< column2或column1 > column2或column1 <= column2或column1 >=
column2,RBO会用全表扫描。

如采取column IS NULL或column IS NOT
NULL或column NOT IN或column != expr或column LIKE
‘%ABC’时,不论column有无索引,RBO都动全表扫描。

如果expr =
expr2,expr表达式作用了一个字段上,无论该字段有无索引,RBO都见面全表扫描。

只要NOT
EXISTS子查询和当视图中以ROWNUM,也会招RBO进行全表扫描。

 

如上就是RBO的布满可用访问路径。

RBO优化器死板的因规则来选执行计划一目了然不够利索,在RBO中也束手无策采取物化视图等Oracle提供的初特征,在Oracle8i时CBO已经基本成熟,因此Oracle强烈建议改用CBO优化器。

 

三、CBO优化器


CBO是基于成本的优化器,它根据可用之拜会路径、对象的统计信息、嵌入的Hints来选择一个股本低于的履计划。

CBO主要涵盖以下组件:

  • 询问转换器(Query
    Transformer)
  • 评估器(Estimator)
  • 计划生成器(Plan Generator)

 

  1. 询问转换器

查询语句之款式会潜移默化所起的施行计划,查询转换器的打算就是是改查询语词之花样以发于好的尽计划。

从今Oracle
8i开始便生出四种易技术:视图合并(View Merging)、谓词推进(Predicate
Pushing)、非嵌套子查询(Subquery Unnesting)和物化视图的查询重写(Query
Rewrite with Materialized Views)。

1.1 视图合并

倘SQL语句被含有视图,经分析后会见将视图放在独立的“视图查询块”中,每个视图会发一个视图子计划,当为全方位讲话产生执行计划时,视图子计划会给直将来采取要无见面照顾到讲话的整体性,这样即便生爱造成不良执行计划之转。视图合并就是以错开丢“视图查询块”,将视图合并到一个完完全全的询问块被,这样即使不见面产生探望图子计划有,执行计划的优良性得到提升。

1.2 谓词推进

切莫是具的视图都能吃合并,对于那些无可知给统一之视图Oracle会将相应的叫做词推进暨视图查询块被,这些名词一般是可索引的要是过滤性较强之。

1.3 非嵌套子查询

子查询以及视图一样啊是吃放于独立查询块被之,查询转换器会将绝大多数子查询转换为连日来从而合并为同查询块,少量不能够叫撤换为连的子查询,会拿它们的子计划安照一个快速的点子排列。

1.4 物化视图的查询重写

当query_rewrite_enabled=true时,查询转换器寻找与该查询语句子相关联的物化视图,并就此物化视图改写该查询语句。

 

2.
关于“窥视”(Peeking)

当Oracle9i中为查询转换器增加了一个意义,就是当用户用绑定变量时,查询转换器可以“偷窥”绑定变量的实际值。

我们领略用绑定变量虽然可使得之回落“硬分析”,但它带动的负面影响是优化器无法根据实际的数据分布来优化SQL,很有或按可以走索引的SQL却做了全表扫描。

“窥视”正是为解决这个题材,但是它们并从未彻底的缓解,Oracle只同意第一潮调整用时开展“窥视”,接下的调用即使绑定变量的价值发生了变更,也还是使用第一糟糕变的履计划,这便导致了一个破绽百出的行计划会给反复使用,10g中之“窥视”也是这样。

 

  1. 评估器

评估器通过计算三个价来评估计划的总体资产:选择性(Selectivity)、基数(Cardinality)、成本(Cost)。

3.1 选择性:

举凡一个大于0稍稍为1之频繁,0表示不曾记录被选定,1象征拥有记录都吃选定。

统计信息与直方图涉及及选择性值的准头。

如:name=’Davis’,

一经非设有统计信息评估器将基于所用的叫词来指定一个少省的取舍性值,此时评估器会始终认为等式谓词的选择性比未等式谓词略;

假如在统计信息而未存直方图,此时选择性值为1/count(distinct
name);

要是在统计信息为有直方图,选择性值则也count(name)where
name=’Davis’ / count(name)where name is not null。

3.2 基数:

一般性表中的行数称为“基础基数”(Base
cardinality);

当用WHERE中之标准过滤后剩下的行数称为“有效基数”(Effective
cardinality);

连续操作后有的结果集行数名“连接基数”(Join
cardinality);

一个字段DISTINCT之后的行数称为“DISTINCT基数”;

“GROUP基数”(Group
cardinality)比较新鲜,它同功底基数和DISTINCT基数有关,例如:group by
colx则GROUP基数就相当基础基数,但是group by
colx,coly的GROUP基数则超过max ( distinct cardinality of colx , distinct
cardinality of coly )且低于min ( (distinct cardinality of colx *
distinct cardinality of coly) , base cardinality)。

3.3 成本:

纵使是胸襟资源消耗的单位。可以领略吧履行表扫描、索引围观、连接、排序等操作所消耗I/O、CPU、内存的多寡。

 

  1. 计划生成器

计划生成器的打算就是是变大量之履计划,然后择中整资产低的一个。

由不同的顾路径、连接方式与连各个可以随意组合,虽然因为不同的法子访同处理数据,但是得有相同的结果,因此一个SQL可能在大量例外的实行计划。

只是实际计划生成器很少会考试所有的也许在的尽计划,如果其发现时行计划之资本已非常没有了,它用终止试验,相反当前计划的本金只要大高,它用持续考其他执行计划,因此只要会而计划生成器一开始便找到本大没有的尽计划,则会大方减小所耗费的岁月,这为正是我们怎么用HINTS来优化SQL的因由之一。

 

季、访问路径


拜路径就是是于数据库里寻找数据的办法。

优化器首先检查WHERE子句和FROM子句的准绳,确定出什么访问路径是可用之。

下一场优化器使用这些访问路径或每访问路径的共同,产生同样组或者是的尽计划,再经过索引、字段、表底统计信息评估每个计划之血本,最后优化器选择资金最低的履行计划所对应之拜访路径。

只要SQL语句的FROM子句无SAMPLE或SAMPLE
BLOCK,优化器在选访问路径的下会优先考虑语句被的HINTS。

优化器可用之拜访路径如下:

  • 全表扫描(Full Table Scans)
  • Rowid扫描(Rowid Scans)
  • 探寻引围观(Index Scans)
  • 簇扫描(Cluster Scans)
  • 散列扫描(Hash Scans)
  • 表取样扫描(Sample Table
    Scans)

  • 全表扫描

全表扫描将读取HWM之下的具有数据块,所有执行还使经WHERE子句过滤看是不是满足条件。当Oracle执行全表扫描时见面依照顺序读取每个片都只有读一不行,如果能够平等不善读博多独片,可以中之提高效率,初始化参数DB_FILE_MULTIBLOCK_READ_COUNT用来安装于相同坏I/O中可以读取多少只数据块。
通常我们觉得该避免全表扫描,但是在物色大量数目经常全表扫描优于索引围观,这多亏以全表扫描可以以一如既往潮I/O中读也大都只片,从而减少了I/O的次数。在使用全表扫描的而为堪运用并行来提高扫描的快慢。

CBO优化器何时会选择全表扫描:

  • 1) 无合适的目。
  • 2) 检索表中大部分之数目。
  • 3)
    表非常小。比如,表中的片小于DB_FILE_MULTIBLOCK_READ_COUNT,只需要一浅I/O。如果这么的表被频繁使用相应alter
    table table_name storage(buffer_pool keep)。
  • 4)
    高并行度。如果当表级设置了于高的连行度,如alter table table_name
    parallel(degree
    10),通常会使CBO选择全表扫描。通常建议在语句级用HINTS来贯彻相互之间,如/*+full(table_name)
    parallel(table_name degree)*/。
  • 5)
    太老的统计数据。如果表没有开展过分析或深遥远没有重新分析,CBO可能会见误的道表含有与丢的数据块。
  • 6)
    在说话中放到了全表扫描的HINTS。

  • Rowid扫描

Rowid代表行以数据块被的具体位置,Rowid是找具体行的尽抢方式。可以在WHERE子句被描绘入Rowid,但是不推荐这么做。通常都是由此索引来取得Rowid,但如若叫搜寻的行都包含在目录中常,直接看索引就能够取所待的多少则不会见使Rowid。

  1. 寻引围观

目录不仅包含被索引的字段值,还蕴含行的岗位标识Rowid,如果告诉句只检索索引字段,Oracle将直从索引中读取而非需要经过Rowid去访问表,如果告诉句通过索引检索其他字段值,则Oracle通过索引获得Rowid从而迅速找到切实可行的推行。

寻引围观类型:

  • 1) 唯一索引围观(Index Unique
    Scans)
  • 2) 索引范围扫描(Index Range
    Scans)
  • 3) 索引降序范围扫描(Index Range
    Scans Descending)
  • 4) 跳跃式索引围观(Index Skip
    Scans)
  • 5) 全索引围观(Full Index
    Scans)
  • 6) 快速全索引围观(Fast Full Index
    Scans)
  • 7) 索引连接(Index Joins)

3.1 唯一索引围观

于运用一个主键字段或含唯一约束的字段选择一行记录时,通常有唯一索引围观。

3.2 索引范围扫描

目范围扫描返回的数据返照索引字段值升序排列,值相同的按Rowid升序排列。如果以言辞中使了ORDER
BY ASC子句,而且排序字段是寻觅引字段时Oracle不见面指向ORDER
BY再次排序。

3.3 索引降序范围扫描

假定以order
by中指定了目录是降序排列的,或者以了INDEX_DESC提示,优化器会动索引降序范围扫描。

3.4 跳跃式索引围观

跳跃式索引围观是因此来增强复合索引效率的,通常当复合索引的首先个索引字段非以说话中指定时凡无力回天用复合索引的,此时一经复合索引的第一个索引字段DISTINCT值非常小,而复合索引的别索引字段DISTINCT值非常很时,可以采用跳跃式索引围观来跳了该复合索引的第一个索引字段。跳跃式扫描会如复合索引在逻辑上分裂成N个比小之目,N值等于复合索引的率先个索引字段的DISTINCT值。

3.5 全索引围观

当查问涉及的字段都富含在目录中,如果WHERE子句中谓词非第一个索引字段,或无WHERE子句子但是被索引字段受到最少有一个非空属性时,通常会举行全索引围观。全索引围观结果集按索引字段排序。

3.6 快速全索引围观

当查问涉及的字段都含有在目中,且被寻找引字段被足足有一个非空属性时,可以动用INDEX_FFS(table_name
index_name)来而报告词做快速全索引围观。快速全索引围观不同让全索引围观,它以多片读取的章程来读全部搜索引块,而且可以行使并行读取。快速全索引围观的结果集不会见排序。位图索引不可知使用快全索引围观。

3.7 索引连接

目连接是几乎单目录的散列连接。如果查询的字段上且有索引,可以采用索引连接来避免访问表。

  1. 簇扫描

于被索引的簇中,有着同样簇键值的行存储在相同数据块被。执行簇扫描时,首先通过扫描簇索引获得被检索行的Rowid,然后用Rowid来稳定具体的执行。

  1. 散列扫描

散列扫描就是于一个散列簇中固定数据行。在一个散列簇中,具有同样散列值的行存储在同等之多少块被。在履散列扫描时,首先通过一个散列函数来获取散列值,然后据此散列值当数块被稳定具体行。

  1. 表取样扫描

当FROM子句后含有SAMPLE或SAMPLE
BLOCK时,会实行表取样扫描来随便检索表明中的数量。如:select* from t
sample block (1);

  1. 处理连接

潜移默化一个连连语句执行计划的季只举足轻重元素是:访问路径、连接方式、连接各个和资产评估。

 

五、影响优化器的初始化参数


  1. OPTIMIZER_FEATURES_ENABLE:

每个版本的Oracle优化器特性都非同等,特别是开了本子升级后得要修改是参数才得以应用就给该版支持的优化器特性。可以授予其的价如果:9.2.0、9.0.2、9.0.1、8.1.7、8.1.6抵。

  1. CURSOR_SHARING:

这参数会将SQL语句被的直接量用变量来替换,存在巨额直接量的OLTP系统可以设想启用这个参数。但是只要专注,绑定变量虽然好使大气底SQL重用,减少分析时,但是执行计划恐会见不完美。通常OLTP系统适用于绑定变量,OLTP系统特性是,SQL运行往往且时刻相对比短,SQL的分析时比重比较生。如果以DSS系统中,SQL运行时增长,相比之下分析时微不足道,好的实践计划才是绝关键的,因此DSS系统不建议使用是参数。

  1. HASH_AREA_SIZE:

立马是散列表底存区域,如果采取散列连接这个参数值未克顶小,否则对散列连接属性影响挺充分。如果是9i建议启动工作区自动管理,然后设置PGA_AGGREGATE_TARGET。

  1. SORT_AREA_SIZE:

外存排序区的轻重,如果排序时内存区不敷会刻画副磁盘。9i同样建议启动工作区自动管理,然后设置PGA_AGGREGATE_TARGET。

  1. HASH_JOIN_ENABLED:

单来启用这个参数,CBO以设想连接方式的时段才见面设想散列连接。

  1. OPTIMIZER_INDEX_CACHING:

这个参数表示于缓存的索引块所占有的百分比,可选值的克是0-100。这个值会影响嵌套循环连接,如果是值设得较高,CBO将重新赞成利用嵌套循环。

  1. OPTIMIZER_INDEX_COST_ADJ:

优化器利用这参数(是只比例)把索引围观的老本转移为当价格的全表扫描的本,然后同全表扫描的财力进行比较。缺省值100,表示找引围观成本和全表扫描成本等。可选值范围是0-10000。

  1. OPTIMIZER_MAX_PERMUTATIONS:

是开始参数用来设定优化器最多考虑多少种连接各个,优化器不断的出可能的阐明的接连的排,直到排列数上参数optimizer_max_permutations为止。一旦优化器停止产生新的排,它用会见从中挑选出本不过小之排。

9.
DB_FILE_MULTIBLOCK_READ_COUNT:

本条参数表示以全表扫描或索引快速全扫描时一致不行I/O读的连天数片数量(block#连接,且同蹩脚I/O不可知超过extent)。

  1. OPTIMIZER_MODE:

优化器模式。值吗:RULE、CHOOSE、ALL_ROWS、FIRST_ROWS_n、FIRST_ROWS。

  1. PARTITION_VIEW_ENABLED:

万一安也TRUE,
该优化器将跨了分区视图中无被呼吁的分区,该参数还能改变基于成本的优化程序于基础表统计信息计算分区视图统计信息之法门。

  1. QUERY_REWRITE_ENABLE:

一旦安也TRUE,优化器将祭可用的物化视图来还写SQL。

 

Thanks and Regards

参考:
http://blog.itpub.net/18474/viewspace-1060730/

参考:
http://www.cnblogs.com/dongzhiquan/archive/2012/01/20/2328365.html

相关文章