Oracle数据库优化培训

www.doone.com.cn◎CopyrightnewdooneAllrightreservedORACLE数据库SQL优化培训软件一部2008年9月www.doone.com.cn◎CopyrightnewdooneAllrightreserved培训内容主要介绍与SQL调整有关的内容，内容涉及多个方面：1.SQL语句执行的过程2.ORACLE优化器3.表之间的关联4.如何得到SQL执行计划5.如何分析执行计划等内容www.doone.com.cn◎CopyrightnewdooneAllrightreserved背景知识在调整之前我们需要了解一些背景知识，只有知道这些背景知识，我们才能更好的调整sql语句SQL语句处理的基本过程，主要包括：◆查询语句处理◆DML语句处理(insert,update,delete)◆DDL语句处理(create..,drop..,alter..,)◆事务控制(commit,rollback)www.doone.com.cn◎CopyrightnewdooneAllrightreserved优化原理作为关系数据库管理系统，要解决的一个重要问题就是如何提高查询的效率，即所谓的查询优化。为什么会出现这个问题呢？我们知道SQL语言是一种非过程化的语言，即它只要用户指出“做什么”，而不需指出“如何做”，这样对用户来说确实方便了，但系统的负担就重了。系统要完成“如何做”，就必然有个选择、比较、权衡的过程，即如何选择最佳的存取途径和实现算法。www.doone.com.cn◎CopyrightnewdooneAllrightreserved什么是优化器查询优化对提高查询效率是至关重要的，在任何一个商品化的RDBMS中，都必须有一个专门负责查询语句优化的程序，称为优化器；是SQL之前分析语句的工具。优化器的优化方式：基于规则（RBO）：优化器遵循Oracle内部预定的规则基于代价（CBO）：依据语句执行的代价，主要指对CPU和内存的占用。优化器在判断是否使用CBO时，要参照表和索引的统计信息。统计信息要在对表做analyze后才会有。www.doone.com.cn◎CopyrightnewdooneAllrightreserved优化器的优化模式Oracle优化器的优化模式主要有四种：Rule：基于规则；Choose：默认模式。根据表或索引的统计信息，如果有统计信息，则使用CBO方式；如果没有统计信息，相应列有索引，则使用RBO方式。Firstrows：与Choose类似。不同的是如果表有统计信息，它将以最快的方式返回查询的前几行，以获得最佳响应时间。Allrows：即完全基于Cost的模式。当一个表有统计信息时，以最快方式返回表所有行，以获得最大吞吐量。没有统计信息则使用RBO方式。www.doone.com.cn◎CopyrightnewdooneAllrightreserved优化模式的选择指定优化模式（CBO/RBO）优化模式为默认选择模式（Choose）情况下，怎么样才能知道SQL是基于代价（CBO）查询，还是基于规则（RBO）查询。COST无值说明使用基于规则（RBO）模式COST有值说明使用基于代价（CBO）模式www.doone.com.cn◎CopyrightnewdooneAllrightreservedCOST说明什么COST是什么？cost属性的值是一个在oracle内部用来比较各个执行计划所耗费(IO\CPU)的代价的值，从而使优化器可以选择最好的执行计划。不同语句的cost值不具有可比性，只能对同一个语句的不同执行计划的cost值进行比较，越小越好。www.doone.com.cn◎CopyrightnewdooneAllrightreserved程序员的要求ORACLE系统中的优化器做的比较简单，因此这就要求用户要有较强的优化意识。程序员要想获得较优的查询性能，就必须对表的大小、索引的选择率以及更新和存取操作的频度等统计信息了如指掌。ORACLE优化对程序员的要求：www.doone.com.cn◎CopyrightnewdooneAllrightreserved优化原理优化通常有两方面的内容，即逻辑优化和物理优化。逻辑优化包括：选择运算尽早执行投影与选择运算同时进行，以避免重复扫描文件公共子表达式预处理谓词的简化处理表达式的恒等变换等逻辑优化往往是一种等价变换，它的优化会对查询带来绝对好处，这部分优化与用户无关，完全由优化器负责，故我们不关心这部分优化。www.doone.com.cn◎CopyrightnewdooneAllrightreserved优化原理物理优化包括：选择有效的存取路径选择合适的操作序列提供较优的操作实现方法物理优化则往往是对动态情况的一种权衡。物理优化目前采用的方法有：启发式的、基于统计信息的。智能式的ORACLE的优化器是启发式的，它的核心是一些从经验中得到的准则，它的好处是系统代价小，但不利的方面是用户的负担太重。www.doone.com.cn◎CopyrightnewdooneAllrightreserved什么是索引索引使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针排序。索引列可以基于数据库表中的单列或多列创建索引。多列索引可以区分其中一列可能有相同值的行。索引类型A）唯一索引唯一索引是不允许其中任何两行具有相同索引值的索引。B）主键索引在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。C）聚散索引在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。www.doone.com.cn◎CopyrightnewdooneAllrightreserved查询优化实例及分析优化准则使用ROWID的查询效率最高；使用唯一索引的查询要快于使用非唯一索引的查询；使用完整说明索引的查询快于使用部分说明索引的查询；等条件的查询快于范围条件或不等条件的查询；ISNULL条件的查询不能使用索引；尽量避免查询中出现全表扫描。www.doone.com.cn◎CopyrightnewdooneAllrightreserved单表查询的优化ORACLE中实现单表查询的途径有三种：使用ROWID（ACCESSBYROWID）索引扫描（INDEXSCAN）全表扫描（ACCESSFULLTABLE）单表查询优化的目的：如何选择一个最佳存取路径，显然使用ROWID的查询效果最高，索引扫描次之，全表扫描效率最低。在一个单表查询中，若上述三种方法都可用，显然要选择使用ROWID的方法，下面要介绍的是如何在多个索引中选择一个最佳的。在一个查询中，若有多个索引可用，ORACLE优化器做如下工作：选择可利用的索引，即判别索引与谓词的相容性；选择查询的驱动索引：若有唯一索引出现，则以此索引为入口，其他索引不用。若有多个非唯一索引，且查询谓词为相等谓词，则采用索引合并的算法。www.doone.com.cn◎CopyrightnewdooneAllrightreserved单表查询的优化索引与谓词有如下的优化关系：（1）唯一索引列＝常数（2）非唯一索引列＝常数（3）索引列常数说明：出现(1)时，(3)中的索引不用；出现(2)时，(3)的索引不用。索引合并的处理方法只用在表中有多个索引上，最多合并索引数目不超过五个。当且仅当只有(3)情况下的索引时，优化器任选其一使用。备注：比索引合并更有效的方法是建立组合索引组合索引有两种使用方式：全部说明方式和部分说明方式。全部说明方式：组合索引列全部出现在查询中。部分说明方式：组合索引列前面一部分出现在查询中。www.doone.com.cn◎CopyrightnewdooneAllrightreserved单表查询的优化例子：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的前部www.doone.com.cn◎CopyrightnewdooneAllrightreserved多表连接的优化处理——连接方式多表连接的基础是两表连接，连接优化的主要工作有：1、有关连接方式的选择排序合并连接(SortMergeJoin(SMJ))连接属性上都建有索引，则可利用索引已有的排序作合并连接。但在连接属性上没有索引时，则要首先对两表在连接属性上排序，对排序结果再作连接。SELECTA.MDSE_ID,B.NAMEFROMMDSEA,DIM_MDSE_SPECBWHEREA.MDSE_SPEC_ID=B.MDSE_SPEC_IDSELECTMDSE_ID,MDSE_SPEC_IDFROMMDSEORDERBYMDSE_ID,MDSE_SPEC_IDSELECTNAME,MDSE_SPEC_IDFROMDIM_MDSE_SPECORDERBYNAME,MDSE_SPEC_IDSELECTA.MDSE_ID,B.NAMEFROMA,BWHEREA.MDSE_SPEC_ID=B.MDSE_SPEC_IDwww.doone.com.cn◎CopyrightnewdooneAllrightreserved多表连接的优化处理——连接方式嵌套循环(NestedLoops(NL))这个连接方法有驱动表(外部表)的概念。该连接过程是一个2层嵌套循环。连接过程：驱动表的Row1----------------Probe-被驱动表的Row驱动表的Row2----------------Probe-被驱动表的Row驱动表的Row3----------------Probe-被驱动表的Row…….驱动表的Rown----------------Probe-被驱动表的Row在嵌套循环连接中，Oracle读取驱动表中的每一行，然后在被驱动表中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理驱动表中的下一行。这个过程一直继续，直到驱动表中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一，这种类型的连接可以用在需要快速响应的语句中，以响应速度为主要目标。说明：嵌套循环连接可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。www.doone.com.cn◎CopyrightnewdooneAllrightreserved多表连接的优化处理——连接方式哈希连接(HashJoin)这种连接是在Oracle7.3以后引入的，从