Oracle数据库优化培训

1、ORACLE数据库SQL优化培训软件一部2008年9月培训内容容主要介绍绍与SQL调整整有关的的内容，内容涉涉及多个个方面：1.SQL语句句执行的的过程2.ORACLE优化化器3.表之之间的关关联4.如何何得到SQL执执行计划划5.如何何分析执执行计划划等内容容背景知识识在调整之之前我们们需要了了解一些些背景知知识，只只有知道道这些背背景知识识，我们们才能更更好的调调整sql语句句SQL语语句处理理的基本本过程，主要包包括：查询语语句处理理 DML语句句处理(insert,update, delete) DDL语语句处理理(create., drop.,alter., )事务控控制(commi

2、t, rollback)优化原理理作为关系系数据库库管理系系统，要要解决的的一个重重要问题题就是如如何提高高查询的的效率，即所谓谓的查询询优化。为什么么会出现现这个问问题呢？我们知知道SQL语言言是一种种非过程程化的语语言，即即它只要要用户指指出“做做什么”，而不不需指出出“如何何做”，这样对对用户来来说确实实方便了了，但系系统的负负担就重重了。系系统要完完成“如如何做”，就必必然有个个选择、比较、权衡的的过程，即如何何选择最最佳的存存取途径径和实现现算法。什么是优优化器查询优化化对提高高查询效效率是至至关重要要的，在在任何一一个商品品化的RDBMS中，都必须须有一个个专门负负责查询询语句优优

3、化的程程序，称称为优化化器；是是SQL之前分分析语句句的工具具 。优化器的的优化方方式：基于规则则（RBO）：优化器遵遵循Oracle内部部预定的的规则基于代价价（CBO）：依据语句句执行的的代价，主要指指对CPU和内内存的占占用。优优化器在在判断是是否使用用CBO时，要要参照表表和索引引的统计计信息。统计信信息要在在对表做做analyze后才才会有。优化器的的优化模模式Oracle优优化器的的优化模模式主要要有四种种：Rule：基于于规则；Choose：默认模模式。根根据表或或索引的的统计信信息，如如果有统统计信息息，则使使用CBO方式式；如果果没有统统计信息息，相应应列有索索引，则则使用R

4、BO方方式。Firstrows：与Choose类类似。不不同的是是如果表表有统计计信息，它将以以最快的的方式返返回查询询的前几几行，以以获得最最佳响应应时间。Allrows：即即完全基基于Cost的的模式。当一个个表有统统计信息息时，以以最快方方式返回回表所有有行，以以获得最最大吞吐吐量。没没有统计计信息则则使用RBO方方式。优化模式式的选择择指定优化化模式（CBO/RBO）优化模式式为默认认选择模模式（Choose ）情情况下，怎么样样才能知知道SQL是基基于代价价（CBO）查查询，还还是基于于规则（RBO）查询询。COST无值说明使用用基于规则（RBO）模式式COST有值说明使用用基于代价

5、（CBO）模式式COST 说明明什么COST是什么么？cost属性的的值是一一个在oracle内内部用来来比较各各个执行行计划所所耗费(IOCPU)的代代价的值值，从而而使优化化器可以以选择最最好的执执行计划划。不同同语句的的cost值不不具有可可比性，只能对对同一个个语句的的不同执执行计划划的cost值值进行比比较，越越小越好好。程序员的的要求ORACLE系系统中的的优化器器做的比比较简单单，因此此这就要要求用户户要有较较强的优优化意识识。程序序员要想想获得较较优的查查询性能能，就必必须对表的大小小、索引的选选择率以及更新和存取操作作的频度度等统计信信息了如如指掌。ORACLE优优化对程程序

6、员的的要求：优化原理理优化通常常有两方方面的内内容，即即逻辑优优化和物物理优化化。逻辑优化化包括：选择运算算尽早执执行投影与选选择运算算同时进进行，以以避免重重复扫描描文件公共子表表达式预预处理谓词的简简化处理理表达式的的恒等变变换等逻辑优化化往往是一一种等价价变换，它的优优化会对对查询带带来绝对对好处，这部分分优化与与用户无无关，完完全由优优化器负负责，故故我们不不关心这这部分优优化。优化原理理物理优化化包括：选择有效效的存取取路径选择合适适的操作作序列提供较优优的操作作实现方方法 物理优化化则往往是是对动态态情况的的一种权权衡。物物理优化化目前采采用的方方法有：启发式式的、基基于统计计信息

7、的的。智能能式的ORACLE的的优化器器是启发发式的，它的核核心是一一些从经经验中得得到的准准则，它它的好处处是系统统代价小小，但不不利的方方面是用用户的负负担太重重。什么是索索引索引 使用索引引可快速速访问数数据库表表中的特特定信息息。索引引是对数数据库表表中一列列或多列列的值进进行排序序的一种种结构，提供指指向存储储在表的的指定列列中的数数据值的的指针，然后根根据指定定的排序序顺序对对这些指指针排序序。索引列可以基于于数据库库表中的的单列或或多列创创建索引引。多列列索引可可以区分分其中一一列可能能有相同同值的行行。索引类型型A）唯一一索引唯一索引引是不允允许其中中任何两两行具有有相同索索引

8、值的的索引。B）主键键索引在数据库库中为表表定义主主键将自自动创建建主键索索引，主主键索引引是唯一一索引的的特定类类型。该该索引要要求主键键中的每每个值都都唯一。C）聚散散索引在聚集索索引中，表中行行的物理理顺序与与键值的的逻辑（索引）顺序相相同。一一个表只只能包含含一个聚聚集索引引。与非非聚集索索引相比比，聚集集索引通通常提供供更快的的数据访访问速度度。查询优化化实例及及分析优化化准准则则使用ROWID的查询询效率最最高；使用唯一一索引的的查询要要快于使使用非唯唯一索引引的查询询；使用完整整说明索索引的查查询快于于使用部部分说明明索引的的查询；等条件的的查询快快于范围围条件或或不等条条件的查

9、查询；ISNULL条件的的查询不不能使用用索引；尽量避免免查询中中出现全全表扫描描。单表查询询的优化化ORACLE中中实现单单表查询询的途径径有三种种：使用ROWID（ACCESSBYROWID）索引扫描描（INDEX SCAN）全表扫描描（ACCESSFULL TABLE）单表查询询优化的的目的：如何选择择一个最最佳存取取路径，显然使使用ROWID的查询询效果最最高，索索引扫描描次之，全表扫扫描效率率最低。在一个个单表查查询中，若上述述三种方方法都可可用，显显然要选选择使用用ROWID的的方法，下面要要介绍的的是如何何在多个个索引中中选择一一个最佳佳的。在一个查查询中，若有多多个索引引可用，

10、ORACLE优化器器做如下下工作：选择可利利用的索索引，即即判别索索引与谓谓词的相相容性；选择查询询的驱动动索引：若有唯一一索引出出现，则则以此索索引为入入口，其其他索引引不用。若有多个个非唯一一索引，且查询询谓词为为相等谓谓词，则则采用索索引合并并的算法法。单表查询询的优化化索引与谓谓词有如如下的优优化关系系：（1）唯唯一索引引列常常数（2）非非唯一索索引列常数（3）索索引列常数说明：出现(1)时，(3)中的索索引不用用；出现现(2)时，(3)的的索引不不用。索引合并并的处理理方法只只用在表表中有多多个索引引上，最最多合并并索引数数目不超超过五个个。当且仅当当只有(3)情情况下的的索引时时，

11、优化化器任选选其一使使用。备注：比比索引合合并更有有效的方方法是建建立组合合索引组合索引引有两种种使用方方式：全全部说明明方式和和部分说说明方式式。全部说明明方式：组合索索引列全全部出现现在查询询中。部分说明明方式：组合索索引列前前面一部部分出现现在查询询中。单表查询询的优化化例子：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)SQ

12、L语语句：A)SELECT*FROMSERV TWHERET.PLACE_S_NODE=600488ANDT.INTEGRAL =594000248ANDT.STATE =A;B)SELECT*FROMSERV TWHERET.PLACE_S_NODE=600488ANDT.INTEGRAL =594000248;C)SELECT*FROMSERV TWHERET.PLACE_S_NODE=600488ANDT.STATE =A;说明：例子A、 例子子B优化化器选用用的组合合索引SERV_PLACE_X_IDX进行索索引扫描描；例子子A采用用全部说说明方式式，例子子B采用用部分说说明方式式。例

13、子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) )连接属性性上都建建有索引引，则可可利用索索引已有有的排序序作合并并连接。但在连连接属性性上没有有索引时时，则要要首先对对两表在在连接属属性

14、上排排序，对对排序结结果再作作连接。SELECT A.MDSE_ID, B.NAME FROM MDSE A, DIM_MDSE_SPEC B WHERE A.MDSE_SPEC_ID = B.MDSE_SPEC_IDSELECT MDSE_ID, MDSE_SPEC_ID FROM MDSE ORDER BY MDSE_ID, MDSE_SPEC_IDSELECT NAME,MDSE_SPEC_ID FROM DIM_MDSE_SPEC ORDER BY NAME, MDSE_SPEC_IDABSELECT A.MDSE_ID, B.NAME FROM A, B WHERE A.MDSE_S

15、PEC_ID = B.MDSE_SPEC_ID多表连接接的优化化处理连接方式式嵌套循环环(NestedLoops(NL) )这个连接接方法有有驱动表表(外部部表)的的概念。该连接接过程是是一个2层嵌套套循环。连接过程程：驱动表的的Row 1-Probe-被驱驱动表的的Row驱动表的的Row 2-Probe-被驱驱动表的的Row驱动表的的Row 3-Probe-被驱驱动表的的Row.驱动表的的Row n-Probe-被驱驱动表的的Row在嵌套循循环连接接中，Oracle读读取驱动表中的每一一行，然然后在被驱动表表中检查是是否有匹匹配的行行，所有有被匹配配的行都都被放到到结果集集中，然然后处理理驱

16、动表中的下一一行。这这个过程程一直继继续，直直到驱动表中的所有有行都被被处理。这是从从连接操操作中可可以得到到第一个个匹配行行的最快快的方法法之一，这种类类型的连连接可以以用在需需要快速速响应的的语句中中，以响响应速度度为主要要目标。说明：嵌套循环环连接可可以先返返回已经经连接的的行，而而不必等等待所有有的连接接操作处处理完才才返回数数据，这这可以实实现快速速的响应应时间。多表连接接的优化化处理连接方式式哈希连接接(HashJoin)这种连接接是在Oracle7.3以后引引入的，从理论论上来说说比NL与SMJ更高高效，而而且只用用在CBO优化化器中。连接过程程：1、构建建阶段：优化器器首先选选

17、择一张张小表做做为驱动动表，运运用哈希希函数对对连接列列进行计计算产生生一张哈哈希表。通常这这个步骤骤是在内内存（hash_area_size）里里面进行行的，因因此运算算很快。2、探测测阶段：优化器器对被驱驱动表的的连接列列运用同同样的哈哈希函数数计算得得到的结结果与前前面形成成的哈希希表进行行探测返返回符合合条件的的记录。这个阶阶段中如如果被驱驱动表的的连接列列的值没没有与驱驱动表连连接列的的值相等等的话，那么这这些记录录将会被被丢弃而而不进行行探测。 说明：哈希连接接比较适适用于返返回大数数据量结结果集的的连接，且只适适用于等等值连接接。使用用哈希连连接必须须是在CBO模模式下，参数ha

18、sh_join_enabled设置置为true（缺省值值），还要设置置 hash_area_size参数，以使哈哈希连接接高效运运行，因因为哈希希连接会会在该参参数指定定大小的的内存中中运行，过小的的参数会会使哈希希连接的的性能比比其他连连接方式式还要低低。多表连接接的优化化处理连接方式式总结一下下，在哪哪种情况况下用哪哪种连接接方法比比较好：A）排序序合并连连接(Sort Merge Join, SMJ)：a)对对于非等等值连接接，这种种连接方方式的效效率是比比较高的的。b)如如果在关关联的列列上都有有索引，效果更更好。c)对对于将2个较大大的表源源做连接接，该连连接方法法比NL连接要要好一

19、些些。B）嵌套套循环(Nested Loops,NL)：a)如如果驱动动表(外外部表)比较小小，并且且在被驱驱动表(内部表表)上有有唯一索索引，或或有高选选择性非非唯一索索引时，使用这这种方法法可以得得到较好好的效率率。b)嵌套套循环连连接有其其它连接接方法没没有的的的一个优优点是：可以先先返回已已经连接接的行，而不必必等待所所有的连连接操作作处理完完才返回回数据，这可以以实现快快速的响响应时间间。C）哈希希连接(HashJoin,HJ)：a)这这种方法法是在oracle7后来引引入的，使用了了比较先先进的连连接理论论，一般般来说，其效率率应该好好于其它它2种连连接，但但是这种种连接只只能用在

20、在CBO优化器器中，而而且需要要设置合合适的hash_area_size参数数，才能能取得较较好的性性能。b)在在2个较较大的表表源之间间连接时时会取得得相对较较好的效效率，在在一个表表源较小小时则能能取得更更好的效效率。c)只只能用于于等值连连接中多表连接接的优化化处理驱动表的的选择多表连接接的基础础是两表表连接，连接优优化的主主要工作作有：2、有关关连接次次序的优优化驱动表表的选择择（基于于规则的的优化器器）ORACLE的的解析器器按照从从右到左的顺序处处理FROM子子句中的的表名，因此FROM子句中中写在最最后的表表（基础础表driving table）将被被最先处处理。在FROM子句句

21、中包含含多个表表的情况况下，你你必须选选择记录录条数最最少的表表作为基基础表。当ORACLE处理理多个表表时，会会运用用排序及及合并的的方式连连接它们们。首先，扫扫描第一一个表（FROM子句句中最后后的那个个表）并并对记录录进行派派序。然后扫描描第二个个表（FROM子句中中最后第第二个表表）。最后将所所有从第第二个表表中检索索出的记记录与第第一个表表中合适适记录进进行合并并 。多表连接接的优化化处理驱动表的的选择例子A例子B例子A：以以SERV表为为驱动表表，与VIP_INFO表做做循环嵌嵌套，产产生的结结果集与与MDSE表再再做循环环嵌套。例子子B：以以MDSE表为驱驱动表，与SERV表表做

22、循环环嵌套，产生的的结果集集与VIP_INFO表再做做循环嵌嵌套。where语句句的连接接顺序ORACLE采采用自下而上上的顺序解解析WHERE子句，根据这这个原理理，表之之间的连连接必须须写在其其他WHERE条件之之前，那那些可可以过滤滤掉最大大数量记记录的条条件必须须写在WHERE子句句的末尾尾。例子：查询优化化实例及及分析SQL共享共享SQL语句句为了不重重复解析析相同的的SQL语句(因为解解析操作作比较费费资源，会导致致性能下下降)，在第一一次解析析之后，ORACLE将SQL语句句及解析析后得到到的执行行计划存存放在内内存中。这块位位于系统统全局区区域SGA(systemglobala

23、rea)的共共享池(sharedbufferpool)中中的内存存可以被被所有的的数据库库用户共共享。因因此，当当执行一一个SQL语句句(有时时被称为为一个游游标)时时，如果果该语句句和之前前的执行行过的某某一语句句完全相相同，并并且之前前执行的的该语句句与其执执行计划划仍然在在内存中中存在，则ORACLE就不不需要再再进行分分析，直直接得到到该语句句的执行行路径。ORACLE的这个个功能大大大地提提高了SQL的的执行性性能并大大大节省省了内存存的使用用。使用这个个功能的的关键是将执行行过的语语句尽可可能放到到内存中中，所以以这要求求有大的的共享池池(通过过设置sharedbufferpool

24、参数值值)和尽尽可能的的使用绑绑定变量量的方法法执行SQL语语句。查询优化化实例及及分析SQL共享共享SQL语句句条件当向Oracle提交交一个SQL语语句，首首先在共共享内存存中查找找是否有有相同的的语句。注意：Oracle对对两者采采取的是是一种严严格匹配配，要达达成共享享，SQL语句句必须完完全相同同(包括括空格，换行等等)共享的三三个条件件：A）字符符级的比比较当前被执执行的语语句和共共享池中中的语句句必须完完全相同同 （包包括大小小写、空空白和注注释）B）两条条语句所所指的对对象必须须完全相相同C）两个个SQL语句中中绑定变变量的名名字必须须相同查询优化化实例及及分析类型不一一致查询

25、询类型一致致与不一一致的查查询（索索引项为为数值型型）serv表serv_idnumber(10)，建建有serv_serv_id_idx(serv_id)索索引查询优化化实例及及分析类型不一一致查询询类型一致致与不一一致的查查询（索索引项为为字符型型）serv表acc_nbr varchar2(240)，建有有serv_acc_nbr_idx(acc_nbr)索引引查询优化化实例及及分析类型不一一致查询询类型一致致与不一一致的查查询（总总结）当索引项项为数值值型的时时候，在在谓词条条件在类类型不一一致的情情况下查查询，ORACLE的的优化器器也可以以引用该该索引，是因为为含有隐隐式转换换。当

26、索引项项为字符符型的时时候，谓谓词条件件就必须须为字符符，否则则ORACLE的优化化器不会会使用到到该索引引，而采采用全表表扫描的的方式来来执行。说明：为了避免免ORACLE对你的的SQL进行隐隐式的类类型转换换，最最好把类类型转换换用显式式表现出出来。注意：当字符和和数值比比较时， ORACLE会优优先转换换数值类类型到字字符类型型。例如：SELECT*FROMSERVWHEREACC_NUBER=2394701;转换为下下面语句句：SELECT*FROMSERVWHERETO_NUMBER(ACC_NUBER)=2394701;SELECT*FROMSERVWHERESERV_ID =18

27、00093946;转换为下下面语句句：SELECT*FROMSERVWHERESERV_ID =TO_NUMBER(1800093946); 查询优化化实例及及分析操作符优优化不等条件件的查询询说明：对于不等等查询的的两种等等价的不不同形式式，执行行计划是是不一样样的如如果serv表表中acc_nbr=2394701的记录录占总记记录的比比例小，使用索索引效率率较快；若比例例较大使使用索引引也是没没有什么么效果查询优化化实例及及分析操作符优优化避免在索索引列上上使用ISNULL和IS NOTNULL避免在索索引中使使用任何何可以为为空的列列，ORACLE将无无法使用用该索引引。对于于单列索索引

28、，如如果列包包含空值值，索引引中将不不存在此此记录。 对于于复合索索引，如如果每个个列都为为空，索索引中同同样不存存在此记记录。如如果至少少有一个个列不为为空，则则记录存存在于索索引中。说明：ORACLE在在其索引引结构中中不存空空值，因因此对ISNULL条件的的查询采采用的是是全表扫扫描的方方式，而而不可能能使用索索引，且且对这种种条件的的查询不不存在改改写形式式。但是是对IS NOTNULL的条件件则可用用其它方方法代替替的。对于IS NOTNULL的条件件可以如如下的等等价写法法：对字符型型列列名（空格串串）对数值型型列列名 0查询优化化实例及及分析操作符优优化IN操作作用IN写写出来的

29、的SQL的优点点是比较较容易写写及清晰晰易懂，但是用用IN的的SQL性能总总是比较较低的，从ORACLE执行行的步骤骤来分析析用IN的SQL与不不用IN的SQL是有有区别的的。ORACLE试试图将其其转换成成多个表表的连接接，如果果转换不不成功则则先执行行IN里里面的子子查询，再查询询外层的的表记录录，如果果转换成成功则直直接采用用多个表表的连接接方式查查询。一一般的SQL都都可以转转换成功功，但对对于含有有分组统统计等方方面的SQL就就不能转转换了。说明：在业务密密集的SQL当当中尽量量不采用用IN操操作符，可以用用OR或或 EXISTS代替替。NOTIN操操作此操作是是强列推推荐不使使用的

30、，因为它它不能应应用表的的索引。说明：可以用NOTEXISTS 或（外连接接+判断断为空）代替。LIKE操作符符LIKE操作符符可以应应用通配配符查询询，里面面的通配配符组合合可能达达到几乎乎是任意意的查询询，但通通配符（%）在在搜寻词词首出现现，是不不会使用用索引，会降低低查询速速度。当当通配符符出现在在字符串串其他位位置时，优化器器就能利利用索引引。select*from servwherenamelike PHS%;-使用用范围索索引select*from servwherenamelike %PHS%;-全全表扫描描查询优化化实例及及分析操作符优优化 及2;高效率：SELECT*FRO

31、MTABLE1WHEREA =3;执行A2与A=3的效果果就有很很大的区区别了，因为A2时时ORACLE会先找找出为2的记录录索引再再进行比比较，而而A=3时ORACLE则则直接找找到=3的记录录索引。说明：在使用（大于）操作时时，应尽尽量的考考虑用=（大大于等于于）来代代替查询优化化实例及及分析ORACLE为为什么不不使用索索引1、检查查被索引引的列或或组合索索引的首首列是否否出现在在WHERE条条件中，这是“执行计计划”能能用到相相关索引引的必要要条件。2、看采采用了哪哪种类型型的连接接方式。ORACLE的共有有SortMergeJoin（SMJ）、HashJoin（HJ）和NestedL

32、oopJoin（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_I

33、NFO做为外表表，先被被访问，由于连连接机制制原因，外表的的数据访访问方式式是全表表扫描，STAFF_INFO.DEPT_NODE上上的索引引显然是是用不上上，最多多在其上上做索引引全扫描描或索引引快速全全扫描。4、是否否用到系系统数据据字典表表或视图图。由于于系统数数据字典典表都未未被分析析过，可可能导致致极差的的“执行行计划”。但是是不要擅擅自对数数据字典典表做分分析，否否则可能能导致死死锁，或或系统性性能下降降。5、索引引列是否否函数的的参数。如是，索引在在查询时时用不上上。6、是否否存在潜潜在的数数据类型型转换。如将字字符型数数据与数数值型数数据比较较，ORACLE会自自动将字字符型用用to_number()函函数进行行转换，从而导导致上一一种现象象的发生生。7、是否否为表和和相关的的索引搜搜集足够够的统计计数据。对数据据经常有有增、删删、改的的表最好好定期对对表和索索引进行行分析，可用SQL语语句“analyze table xxxxcomputestatistics forallindexes;”。ORACLE掌掌握了充充分反映映实际的的统计数数据，才才有可能能做出正正确的选选择。8、