Oracle数据库优化培训

ORACLE数据库

SQL优化培训软件一部2008年9月培训内容主要介绍与SQL调整有关的内容，内容涉及多个方面：1.SQL语句执行的过程2.ORACLE优化器3.表之间的关联4.如何得到SQL执行计划5.如何分析执行计划等内容背景知识

在调整之前我们需要了解一些背景知识，只有知道这些背景知识，我们才能更好的调整sql语句SQL语句处理的基本过程，主要包括：◆查询语句处理◆DML语句处理(insert,update,delete)◆DDL语句处理(create..,drop..,alter..,)◆事务控制(commit,rollback)优化原理作为关系数据库管理系统，要解决的一个重要问题就是如何提高查询的效率，即所谓的查询优化。为什么会出现这个问题呢？我们知道SQL语言是一种非过程化的语言，即它只要用户指出“做什么”，而不需指出“如何做”，这样对用户来说确实方便了，但系统的负担就重了。系统要完成“如何做”，就必然有个选择、比较、权衡的过程，即如何选择最佳的存取途径和实现算法。什么是优化器查询优化对提高查询效率是至关重要的，在任何一个商品化的RDBMS中，都必须有一个专门负责查询语句优化的程序，称为优化器；是SQL之前分析语句的工具。

优化器的优化方式：基于规则（RBO）：优化器遵循Oracle内部预定的规则基于代价（CBO）：依据语句执行的代价，主要指对CPU和内存的占用。优化器在判断是否使用CBO时，要参照表和索引的统计信息。统计信息要在对表做analyze后才会有。优化器的优化模式

Oracle优化器的优化模式主要有四种：Rule：基于规则；Choose：默认模式。根据表或索引的统计信息，如果有统计信息，则使用CBO方式；如果没有统计信息，相应列有索引，则使用RBO方式。Firstrows：与Choose类似。不同的是如果表有统计信息，它将以最快的方式返回查询的前几行，以获得最佳响应时间。Allrows：即完全基于Cost的模式。当一个表有统计信息时，以最快方式返回表所有行，以获得最大吞吐量。没有统计信息则使用RBO方式。

优化模式的选择指定优化模式（CBO/RBO）优化模式为默认选择模式（Choose）情况下，怎么样才能知道SQL是基于代价（CBO）查询，还是基于规则（RBO）查询。COST无值说明使用基于规则（RBO）模式COST有值说明使用基于代价（CBO）模式COST说明什么COST是什么？cost属性的值是一个在oracle内部用来比较各个执行计划所耗费(IO\CPU)的代价的值，从而使优化器可以选择最好的执行计划。不同语句的cost值不具有可比性，只能对同一个语句的不同执行计划的cost值进行比较，越小越好。程序员的要求ORACLE系统中的优化器做的比较简单，因此这就要求用户要有较强的优化意识。程序员要想获得较优的查询性能，就必须对表的大小、索引的选择率以及更新和存取操作的频度等统计信息了如指掌。ORACLE优化对程序员的要求：优化原理优化通常有两方面的内容，即逻辑优化和物理优化。逻辑优化包括：选择运算尽早执行投影与选择运算同时进行，以避免重复扫描文件公共子表达式预处理谓词的简化处理表达式的恒等变换等

逻辑优化往往是一种等价变换，它的优化会对查询带来绝对好处，这部分优化与用户无关，完全由优化器负责，故我们不关心这部分优化。优化原理物理优化包包括：选择有效的的存取路径径选择合适的的操作序列列提供较优的的操作实现现方法物理优化则往往是对对动态情况况的一种权权衡。物理理优化目前前采用的方方法有：启启发式的、、基于统计计信息的。。智能式的的ORACLE的优优化器是启启发式的，，它的核心心是一些从从经验中得得到的准则则，它的好好处是系统统代价小，，但不利的的方面是用用户的负担担太重。什么是索引引索引使用索引可可快速访问问数据库表表中的特定定信息。索索引是对数数据库表中中一列或多多列的值进进行排序的的一种结构构，提供指指向存储在在表的指定定列中的数数据值的指指针，然后后根据指定定的排序顺顺序对这些些指针排序序。索引列可以基于数数据库表中中的单列或或多列创建建索引。多多列索引可可以区分其其中一列可可能有相同同值的行。。索引类型A）唯一索索引唯一索引是是不允许其其中任何两两行具有相相同索引值值的索引。。B）主键索索引在数据库中中为表定义义主键将自自动创建主主键索引，，主键索引引是唯一索索引的特定定类型。该该索引要求求主键中的的每个值都都唯一。C）聚散索索引在聚集索引引中，表中中行的物理理顺序与键键值的逻辑辑（索引））顺序相同同。一个表表只能包含含一个聚集集索引。与与非聚集索索引相比，，聚集索引引通常提供供更快的数数据访问速速度。查询优化实实例及分析析优化化准准则则使用ROWID的查查询效率最最高；使用唯一索索引的查询询要快于使使用非唯一一索引的查查询；使用完整说说明索引的的查询快于于使用部分分说明索引引的查询；；等条件的查查询快于范范围条件或或不等条件件的查询；；ISNULL条件件的查询不不能使用索索引；尽量避免查查询中出现现全表扫描描。单表查询的的优化ORACLE中实现现单表查询询的途径有有三种：使用ROWID（ACCESSBYROWID）索引扫描（（INDEXSCAN）全表扫描（（ACCESSFULLTABLE）单表查询优优化的目的的：如何选择一一个最佳存存取路径，，显然使用用ROWID的查询询效果最高高，索引扫扫描次之，，全表扫描描效率最低低。在一个个单表查询询中，若上上述三种方方法都可用用，显然要要选择使用用ROWID的方法法，下面要要介绍的是是如何在多多个索引中中选择一个个最佳的。。在一个查询询中，若有有多个索引引可用，ORACLE优化器器做如下工工作：选择可利用用的索引，，即判别索索引与谓词词的相容性性；选择查询的的驱动索引引：若有唯一索索引出现，，则以此索索引为入口口，其他索索引不用。。若有多个非非唯一索引引，且查询询谓词为相相等谓词，，则采用索索引合并的的算法。单表查询的的优化索引与谓词词有如下的的优化关系系：（1）唯一一索引列＝＝常数（2）非唯唯一索引列列＝常数（3）索引引列>常数数说明：出现(1)时，(3)中的索索引不用；；出现(2)时，(3)的索索引不用。。索引合并的的处理方法法只用在表表中有多个个索引上，，最多合并并索引数目目不超过五五个。当且仅当只只有(3)情况下的的索引时，，优化器任任选其一使使用。备注：比索索引合并更更有效的方方法是建立立组合索引引组合索引有有两种使用用方式：全全部说明方方式和部分分说明方式式。全部说明方方式：组合合索引列全全部出现在在查询中。。部分说明方方式：组合合索引列前前面一部分分出现在查查询中。单表查询的的优化例子：SERV表表建有组合合索引：SERV_PLACE_X_IDX(PLACE_S_NODE,INTEGRAL,STATE)SERV表表建有单索索引：SERV_PLACE_S_NODE_IDX(PLACE_S_NODE)SERV_INTEGRAL_IDX(INTEGRAL)SERV_STATE_IDX(STATE)SQL语句句：A)SELECT*FROMSERVTWHERET.PLACE_S_NODE=600488ANDT.INTEGRAL=594000248ANDT.STATE='A';B)SELECT*FROMSERVTWHERET.PLACE_S_NODE=600488ANDT.INTEGRAL=594000248;C)SELECT*FROMSERVTWHERET.PLACE_S_NODE=600488ANDT.STATE='A';说明：例子A、例例子B优优化器选用用的组合索索引SERV_PLACE_X_IDX进行索索引扫描；；例子A采采用全部说说明方式，，例子B采采用部分说说明方式。。例子C优化化器则选用用SERV_PLACE_S_NODE_IDX、SERV_STATE_IDX进行索引引合并，而而不能使用用组合SERV_PLACE_X_IDX，因因为PLACE_S_NODE、、STATE不是组组合索引列列PLACE_S_NODE、INTEGRAL、STATE的的前部多表连接的的优化处理理——连接方式多表连接的的基础是两两表连接，，连接优化化的主要工工作有：1、有关连连接方式的的选择排序合并连连接(SortMergeJoin(SMJ))连接属性上上都建有索索引，则可可利用索引引已有的排排序作合并并连接。但但在连接属属性上没有有索引时，，则要首先先对两表在在连接属性性上排序，，对排序结结果再作连连接。SELECTA.MDSE_ID,B.NAME

FROMMDSEA,DIM_MDSE_SPECB

WHEREA.MDSE_SPEC_ID=B.MDSE_SPEC_IDSELECTMDSE_ID,MDSE_SPEC_ID

FROMMDSEORDERBYMDSE_ID,MDSE_SPEC_IDSELECTNAME,MDSE_SPEC_ID

FROMDIM_MDSE_SPECORDERBYNAME,MDSE_SPEC_IDABSELECTA.MDSE_ID,B.NAME

FROMA,B

WHEREA.MDSE_SPEC_ID=B.MDSE_SPEC_ID多表连接的的优化处理理——连接方式嵌套循环(NestedLoops(NL))这个连接方方法有驱动动表(外部部表)的概概念。该连连接过程是是一个2层层嵌套循环环。连接过程：：驱动表的Row1----------------Probe->被驱驱动表的Row驱动表的Row2----------------Probe->被驱驱动表的Row驱动表的Row3----------------Probe->被驱驱动表的Row…….驱动表的Rown----------------Probe->被驱驱动表的Row在嵌套循环环连接中，，Oracle读取取驱动表中的每一行行，然后在在被驱动表中检查是否否有匹配的的行，所有有被匹配的的行都被放放到结果集集中，然后后处理驱动表中的下一行行。这个过过程一直继继续，直到到驱动表中的所有行行都被处理理。这是从从连接操作作中可以得得到第一个个匹配行的的最快的方方法之一，，这种类型型的连接可可以用在需需要快速响响应的语句句中，以响响应速度为为主要目标标。说明：嵌套循环连连接可以先先返回已经经连接的行行，而不必必等待所有有的连接操操作处理完完才返回数数据，这可可以实现快快速的响应应时间。多表连接的的优化处理理——连接方式哈希连接(HashJoin)这种连接是是在Oracle7.3以以后引入的的，从理论论上来说比比NL与SMJ更高高效，而且且只用在CBO优化化器中。连接过程：：1、构建阶阶段：优化化器首先选选择一张小小表做为驱驱动表，运运用哈希函函数对连接接列进行计计算产生一一张哈希表表。通常这这个步骤是是在内存（（hash_area_size）里里面进行的的，因此运运算很快。。2、探测阶阶段：优化化器对被驱驱动表的连连接列运用用同样的哈哈希函数计计算得到的的结果与前前面形成的的哈希表进进行探测返返回符合条条件的记录录。这个阶阶段中如果果被驱动表表的连接列列的值没有有与驱动表表连接列的的值相等的的话，那么么这些记录录将会被丢丢弃而不进进行探测。。说明：哈希连接比比较适用于于返回大数数据量结果果集的连接接，且只适适用于等值值连接。使使用哈希连连接必须是是在CBO模式下，，参数hash_join_enabled设设置为true（缺缺省值），，还要设置hash_area_size参数数，以使哈哈希连接高高效运行，，因为哈希希连接会在在该参数指指定大小的的内存中运运行，过小小的参数会会使哈希连连接的性能能比其他连连接方式还还要低。多表连接的的优化处理理——连接方式总结一下，，在哪种情情况下用哪哪种连接方方法比较好好：A）排序合合并连接(SortMergeJoin,SMJ)：a)对于于非等值连连接，这种种连接方式式的效率是是比较高的的。b)如果果在关联的的列上都有有索引，效效果更好。。c)对于于将2个较较大的表源源做连接，，该连接方方法比NL连接要好好一些。B）嵌套循循环(NestedLoops,NL)：：a)如果果驱动表(外部表)比较小，，并且在被被驱动表(内部表)上有唯一一索引，或或有高选择择性非唯一一索引时，，使用这种种方法可以以得到较好好的效率。。b)嵌套循循环连接有有其它连接接方法没有有的的一个个优点是：：可以先返返回已经连连接的行，，而不必等等待所有的的连接操作作处理完才才返回数据据，这可以以实现快速速的响应时时间。C）哈希连连接(HashJoin,HJ)：a)这种种方法是在在oracle7后后来引入的的，使用了了比较先进进的连接理理论，一般般来说，其其效率应该该好于其它它2种连接接，但是这这种连接只只能用在CBO优化化器中，而而且需要设设置合适的的hash_area_size参数数，才能取取得较好的的性能。b)在2个较大的的表源之间间连接时会会取得相对对较好的效效率，在一一个表源较较小时则能能取得更好好的效率。。c)只能能用于等值值连接中多表连接的的优化处理理——驱动表的选选择多表连接的的基础是两两表连接，，连接优化化的主要工工作有：2、有关连连接次序的的优化———驱动表的的选择（基基于规则的的优化器））ORACLE的解析析器按照从从右到左的顺序处理理FROM子句中的的表名，因因此FROM子句中中写在最后后的表（基基础表drivingtable）将被最最先处理。。在FROM子句中包包含多个表表的情况下下，你必须须选择记录录条数最少少的表作为为基础表。。当ORACLE处处理多个表表时，会会运用排序序及合并的的方式连接接它们。首先，扫描描第一个表表（FROM子句中中最后的那那个表）并并对记录进进行派序。。然后扫描第第二个表（（FROM子句中最最后第二个个表）。最后将所有有从第二个个表中检索索出的记录录与第一个个表中合适适记录进行行合并。。多表连接的的优化处理理——驱动表的选选择例子A例子B例子A：以以SERV表为驱驱动表，与与VIP_INFO表做循环环嵌套，产产生的结果果集与MDSE表再再做循环嵌嵌套。例例子B：以以MDSE表为驱驱动表，与与SERV表做循环环嵌套，产产生的结果果集与VIP_INFO表再再做循环嵌嵌套。where语句的连连接顺序ORACLE采用自下而上的顺序解析析WHERE子句，，根据这个个原理，表表之间的连连接必须写写在其他WHERE条件之前前，那些些可以过滤滤掉最大数数量记录的的条件必须须写在WHERE子子句的末尾尾。例子：查询优化实实例及分析析——SQL共享共享SQL语句为了不重复复解析相同同的SQL语句(因因为解析操操作比较费费资源，会会导致性能能下降)，，在第一次次解析之后后，ORACLE将将SQL语语句及解析析后得到的的执行计划划存放在内内存中。这这块位于系系统全局区区域SGA(systemglobalarea)的共共享池(sharedbufferpool)中的内存存可以被所所有的数据据库用户共共享。因此此，当执行行一个SQL语句(有时被称称为一个游游标)时，，如果该语语句和之前前的执行过过的某一语语句完全相相同，并且且之前执行行的该语句句与其执行行计划仍然然在内存中中存在，则则ORACLE就不不需要再进进行分析，，直接得到到该语句的的执行路径径。ORACLE的的这个功能能大大地提提高了SQL的执行行性能并大大大节省了了内存的使使用。使用这个功功能的关键键是将执行过过的语句尽尽可能放到到内存中，，所以这要要求有大的的共享池(通过设置置sharedbufferpool参数值)和尽可能能的使用绑绑定变量的的方法执行行SQL语语句。查询优化实实例及分析析——SQL共享共享SQL语句条件件当向Oracle提提交一个SQL语句句，首先在在共享内存存中查找是是否有相同同的语句。。注意：Oracle对两者者采取的是是一种严格格匹配，要要达成共享享，SQL语句必须须完全相同同(包括空空格，换行行等)．．共享的三个个条件：A）字符级级的比较当前被执行行的语句和和共享池中中的语句必必须完全相相同（包包括大小写写、空白和和注释）B）两条语语句所指的的对象必须须完全相同同C）两个SQL语句句中绑定变变量的名字字必须相同同查询优化实实例及分析析——类型不一致致查询类型一致与与不一致的的查询（索索引项为数数值型）serv表表serv_idnumber(10)，建建有serv_serv_id_idx(serv_id)索引引查询询优优化化实实例例及及分分析析————类型型不不一一致致查查询询类型型一一致致与与不不一一致致的的查查询询（（索索引引项项为为字字符符型型））serv表表acc_nbrvarchar2(240)，，建建有有serv_acc_nbr_idx(acc_nbr)索索引引查询询优优化化实实例例及及分分析析————类型型不不一一致致查查询询类型型一一致致与与不不一一致致的的查查询询（（总总结结））当索索引引项项为为数数值值型型的的时时候候，，在在谓谓词词条条件件在在类类型型不不一一致致的的情情况况下下查查询询，，ORACLE的的优优化化器器也也可可以以引引用用该该索索引引，，是是因因为为含含有有隐隐式式转转换换。。当索索引引项项为为字字符符型型的的时时候候，，谓谓词词条条件件就就必必须须为为字字符符，，否否则则ORACLE的的优优化化器器不不会会使使用用到到该该索索引引，，而而采采用用全全表表扫扫描描的的方方式式来来执执行行。。说明明：：为了了避避免免ORACLE对对你你的的SQL进进行行隐隐式式的的类类型型转转换换，，最最好好把把类类型型转转换换用用显显式式表表现现出出来来。。注意意：：当字字符符和和数数值值比比较较时时，，ORACLE会会优优先先转转换换数数值值类类型型到到字字符符类类型型。。例如如：：SELECT*FROMSERVWHEREACC_NUBER=2394701;转换换为为下下面面语语句句：：SELECT*FROMSERVWHERETO_NUMBER(ACC_NUBER)=2394701;SELECT*FROMSERVWHERESERV_ID='1800093946';转换换为为下下面面语语句句：：SELECT*FROMSERVWHERESERV_ID=TO_NUMBER('1800093946');查询询优优化化实实例例及及分分析析———操作作符符优优化化不等等条条件件的的查查询询说明明：：对于于不不等等查查询询的的两两种种等等价价的的不不同同形形式式，，执执行行计计划划是是不不一一样样的的．．如如果果serv表表中中acc_nbr=‘‘2394701’’的的记记录录占占总总记记录录的的比比例例小小，，使使用用索索引引效效率率较较快快；；若若比比例例较较大大使使用用索索引引也也是是没没有有什什么么效效果果．．查询询优优化化实实例例及及分分析析———操作作符符优优化化避免免在在索索引引列列上上使使用用ISNULL和和ISNOTNULL避免免在在索索引引中中使使用用任任何何可可以以为为空空的的列列，，ORACLE将将无无法法使使用用该该索索引引。。对对于于单单列列索索引引，，如如果果列列包包含含空空值值，，索索引引中中将将不不存存在在此此记记录录。。对对于于复复合合索索引引，，如如果果每每个个列列都都为为空空，，索索引引中中同同样样不不存存在在此此记记录录。。如如果果至至少少有有一一个个列列不不为为空空，，则则记记录录存存在在于于索索引引中中。。说明明：：ORACLE在在其其索索引引结结构构中中不不存存空空值值，，因因此此对对ISNULL条条件件的的查查询询采采用用的的是是全全表表扫扫描描的的方方式式，，而而不不可可能能使使用用索索引引，，且且对对这这种种条条件件的的查查询询不不存存在在改改写写形形式式。。但但是是对对ISNOTNULL的的条条件件则则可可用用其其它它方方法法代代替替的的。。对于于ISNOTNULL的的条条件件可可以以如如下下的的等等价价写写法法：：对字字符符型型列列名名>‘‘’（（空空格格串串））对数数值值型型列列名名>0查询询优优化化实实例例及及分分析析————操作作符符优优化化IN操操作作用IN写写出出来来的的SQL的的优优点点是是比比较较容容易易写写及及清清晰晰易易懂懂，，但但是是用用IN的的SQL性性能能总总是是比比较较低低的的，，从从ORACLE执执行行的的步步骤骤来来分分析析用用IN的的SQL与与不不用用IN的的SQL是是有有区区别别的的。。ORACLE试试图图将将其其转转换换成成多多个个表表的的连连接接，，如如果果转转换换不不成成功功则则先先执执行行IN里里面面的的子子查查询询，，再再查查询询外外层层的的表表记记录录，，如如果果转转换换成成功功则则直直接接采采用用多多个个表表的的连连接接方方式式查查询询。。一一般般的的SQL都都可可以以转转换换成成功功，，但但对对于于含含有有分分组组统统计计等等方方面面的的SQL就就不不能能转转换换了了。。说明：在业务密密集的SQL当当中尽量量不采用用IN操操作符，，可以用用OR或或EXISTS代替替。NOTIN操操作此操作是是强列推推荐不使使用的，，因为它它不能应应用表的的索引。。说明：可以用NOTEXISTS或（（外连接接+判断断为空））代替。。LIKE操作符符LIKE操作符符可以应应用通配配符查询询，里面面的通配配符组合合可能达达到几乎乎是任意意的查询询，但通通配符（（%）在在搜寻词词首出现现，是不不会使用用索引，，会降低低查询速速度。当当通配符符出现在在字符串串其他位位置时，，优化器器就能利利用索引引。select*fromservwherenamelike‘PHS%’;--使用用范围索索引select*fromservwherenamelike‘%PHS%’;--全全表扫描描查询优化化实例及及分析———操作符优优化>及<操操作符（（大于或或小于操操作符））大于或小小于操作作符一般般情况下下是不用用调整的的，因为为它有索索引就会会采用索索引查找找，有的的情况下下可以对对它进行行优化。。例：表table1共共有100万条条记录，，一个number型型字段A并建索索引。A=030万条A=130万条A=239万条A=31万万条低效率：：SELECT*FROMTABLE1WHEREA>2;高效率：：SELECT*FROMTABLE1WHEREA>=3;执行A>2与A>=3的效果果就有很很大的区区别了，，因为A>2时时ORACLE会先找找出为2的记录录索引再再进行比比较，而而A>=3时ORACLE则则直接找找到=3的记录录索引。。说明：在使用>（（大于））操作时时，应尽尽量的考考虑用>=（大大于等于于）来代代替查询优化化实例及及分析———ORACLE为为什么不不使用索索引1、检查查被索引引的列或或组合索索引的首首列是否否出现在在WHERE条条件中，，这是““执行计计划”能能用到相相关索引引的必要要条件。。2、看采采用了哪哪种类型型的连接接方式。。ORACLE的共有有SortMergeJoin（SMJ）、、HashJoin（HJ）和NestedLoopJoin（NL）。在两张表表连接，，且内表表的目标标列上建建有索引引时，只只有NestedLoop才能有有效地利利用到该该索引。。SMJ即即使相关关列上建建有索引引，最多多只能因因索引的的存在，，避免数数据排序序过程。。HJ由于于须做HASH运算，，索引的的存在对对数据查查询速度度几乎没没有影响响。3、看连连接顺序序是否允允许使用用相关索索引。假假设表STAFF_INFO的DEPT_NODE列列上有索索引，表表DEPT_TREE的列DEPT_NODE上上无索引引，WHERE语句有有STAFF_INFO.DEPT_NODE=DEPT_TREE.DEPT_NODE条件。。在做NL连接时时，STAFF_INFO做为外表表，先被被访问，，由于连连接机制制原因，，外表的的数据访访问方式式是全表表扫描，，STAFF_INFO.DEPT_NODE上上的索引引显然是是用不上上，最多多在其上上做索引引全扫描描或索引引快速全全扫描。。4、是否否用到系系统数据据字典表表或视图图。由于于系统数数据字典典表都未未被分析析过，可可能导致致极差的的“执行行计划””。但是是不要擅擅自对数数据字典典表做分分析，否否则可能能导致死死锁，或或系统性性能下降降。5、索引引列是否否函数的的参数。。如是，，索引在在查询时时用不上上。6、是否否存在潜潜在的数数据类型型转换。。如将字字符型数数据与数数值型数数据比较较，ORACLE会自自动将字字符型用用to_number()函函数进行行转换，，从而导导致上一一种现象象的发生生。7、是否否为表和和相关的的索引搜搜集足够够的统计计数据。。对数据据经常有有增、删删、改的的表最好好定期对对表和索索引进行行分析，，可用SQL语语句“analyzetablexxxxcomputestatisticsforallindexes;”。ORACLE掌掌握了充充分反映映实际的的统计数数据，才才有可能能做出正正确的选选择。8、索引引列的选选择性不不高。9、索引引列值是是否可为为空（NULL）。如如果索引引列值可可以是空空值，在在SQL语句中中那些要要返回NULL值的操操作，将将不会用用到索引引，如COUNT（*），而而是用全全表扫描描。这是是因为索索引中存存储值不不能为全全空。10、