PLSQL_性能优化种类04_Oracle Optimizer优化器

2014-09-25 Created By
BaoXinjian

Oracle 1一、摘要


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来选拔一个基金低于的实施部署。

 

Oracle 2二、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优化器。

 

Oracle 3三、CBO优化器


CBO是基于费用的优化器,它根据可用的走访路径、对象的总计音信、嵌入的Hints来抉择一个花费最低的实践安排。

CBO紧要含有以下组件:

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

 

Oracle 4

  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的原故之一。

 

Oracle 5四、访问路径


走访路径就是从数据库里找找数据的不二法门。

优化器首先检查WHERE子句和FROM子句的标准化,确定有哪些访问路径是可用的。

接下来优化器使用这一个访问路径或各访问路径的一路,爆发一组或者存在的实施布署,再通过索引、字段、表的总计音信评估每个安排的资金,最终优化器选拔资金低于的举行布署所对应的访问路径。

假诺SQL语句的FROM子句无SAMPLE或SAMPLE
BLOCK,优化器在增选访问路径的时候会预先考虑语句中的HINTS。

优化器可用的造访路径如下:

  • 全表扫描(Full Table Scans)
  • Rowid扫描(Rowid Scans)
  • 索引围观(Index Scans)
  • 簇扫描(Cluster Scans)
  • 散列扫描(Hash Scans)
  • 表取样扫描(山姆(Sam)ple Table
    Scans)
  1. 全表扫描

全表扫描将读取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可能会错误的觉得表含有及少的数据块。
  • Oracle,6)
    在言语中置放了全表扫描的HINTS。
  1. 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. 拍卖连接

影响一个连连语句执行布置的八个根本元素是:访问路径、连接格局、连接种种和财力评估。

 

Oracle 6五、影响优化器的初阶化参数


  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

Oracle 7

相关文章