Oracle数据库优化培训.ppt

《Oracle数据库优化培训.ppt》由会员分享，可在线阅读，更多相关《Oracle数据库优化培训.ppt（36页珍藏版）》请在金锄头文库上搜索。

1、Copyright new doone All right reservedORACLE数据库SQL优化培训软件一部2008年9月Copyright new doone All right reserved 培训内容 主要介绍与SQL调整有关的内容，内容涉及多个方面： 1.SQL语句执行的过程 2.ORACLE优化器 3.表之间的关联 4.如何得到SQL执行计划 5.如何分析执行计划等内容Copyright new doone All right reserved 背景知识 在调整之前我们需要了解一些背景知识，只有知道这些背景知识，我们才能更好的调整sql语句 SQL语句处理的基本过程，主要包

2、括： 查询语句处理 DML语句处理(insert, update, delete) DDL 语句处理(create . , drop . , alter . , ) 事务控制(commit, rollback) Copyright new doone All right reserved 优化原理 作为关系数据库管理系统，要解决的一个重要问题就是如何提高查询的效率，即所谓的查询优化。为什么会出现这个问题呢？我们知道SQL语言是一种非过程化的语言，即它只要用户指出“做什么”，而不需指出“如何做”，这样对用户来说确实方便了，但系统的负担就重了。系统要完成“如何做”，就必然有个选择、比较、权衡的过程

3、，即如何选择最佳的存取途径和实现算法。Copyright new doone All right reserved 什么是优化器 查询优化对提高查询效率是至关重要的，在任何一个商品化的RDBMS中，都必须有一个专门负责查询语句优化的程序，称为优化器；是SQL之前分析语句的工具 。 优化器的优化方式：优化器的优化方式： 基于规则（RBO）： 优化器遵循Oracle内部预定的规则 基于代价（CBO）： 依据语句执行的代价，主要指对CPU和内存的占用。优化器在判断是否使用CBO时，要参照表和索引的统计信息。统计信息要在对表做analyze后才会有。 Copyright new doone All r

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

5、served 优化模式的选择指定优化模式（CBO/RBO）优化模式为默认选择模式（ Choose ）情况下，怎么样才能知道SQL是基于代价（CBO）查询，还是基于规则（RBO）查询。COST无值说明使用基于规则（RBO）模式COST有值说明使用基于代价（CBO）模式Copyright new doone All right reserved COST 说明什么COST是什么？ cost属性的值是一个在oracle内部用来比较各个执行计划所耗费(IOCPU)的代价的值，从而使优化器可以选择最好的执行计划。不同语句的cost值不具有可比性，只能对同一个语句的不同执行计划的cost值进行比较，越小越

6、好。Copyright new doone All right reserved 程序员的要求 ORACLE系统中的优化器做的比较简单，因此这就要求用户要有较强的优化意识。程序员要想获得较优的查询性能，就必须对表的大小表的大小、索引的选择率索引的选择率以及更新更新和存取操作的频度存取操作的频度等统计信息了如指掌。ORACLE优化对程序员的要求：Copyright new doone All right reserved 优化原理优化通常有两方面的内容，即逻辑优化和物理优化。逻辑优化包括：逻辑优化包括： 选择运算尽早执行 投影与选择运算同时进行，以避免重复扫描文件 公共子表达式预处理 谓词的简化

7、处理 表达式的恒等变换等 逻辑优化逻辑优化往往是一种等价变换，它的优化会对查询带来绝对好处，这部分优化与用户无关，完全由优化器负责，故我们不关心这部分优化。Copyright new doone All right reserved 优化原理物理优化包括：物理优化包括： 选择有效的存取路径 选择合适的操作序列 提供较优的操作实现方法 物理优化物理优化则往往是对动态情况的一种权衡。物理优化目前采用的方法有：启发式的、基于统计信息的。智能式的ORACLE的优化器是启发式的，它的核心是一些从经验中得到的准则，它的好处是系统代价小，但不利的方面是用户的负担太重。Copyright new doone

8、All right reserved 什么是索引索引索引 使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针排序。索引列索引列 可以基于数据库表中的单列或多列创建索引。多列索引可以区分其中一列可能有相同值的行。索引类型索引类型 A）唯一索引）唯一索引 唯一索引是不允许其中任何两行具有相同索引值的索引。 B）主键索引）主键索引 在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。 C）聚散索引）聚散索引 在聚集索引中，表中行的物理

9、顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。Copyright new doone All right reserved 查询优化实例及分析优 化 准 则使用ROWID的查询效率最高；使用唯一索引的查询要快于使用非唯一索引的查询；使用完整说明索引的查询快于使用部分说明索引的查询；等条件的查询快于范围条件或不等条件的查询；IS NULL条件的查询不能使用索引；尽量避免查询中出现全表扫描。Copyright new doone All right reserved 单表查询的优化ORACLE中实现单表查询的途径有三种：中实现单

10、表查询的途径有三种：使用ROWID（ACCESS BY ROWID）索引扫描（INDEX SCAN）全表扫描（ACCESS FULL TABLE）单表查询优化的目的：单表查询优化的目的： 如何选择一个最佳存取路径，显然使用ROWID的查询效果最高，索引扫描次之，全表扫描效率最低。在一个单表查询中，若上述三种方法都可用，显然要选择使用ROWID的方法，下面要介绍的是如何在多个索引中选择一个最佳的。在一个查询中，若有多个索引可用，在一个查询中，若有多个索引可用，ORACLE优化器做如下工作：优化器做如下工作：选择可利用的索引，即判别索引与谓词的相容性；选择查询的驱动索引： 若有唯一索引出现，则以此

11、索引为入口，其他索引不用。 若有多个非唯一索引，且查询谓词为相等谓词，则采用索引合并的算法。Copyright new doone All right reserved 单表查询的优化索引与谓词有如下的优化关系：索引与谓词有如下的优化关系：（1）唯一索引列常数（2）非唯一索引列常数（3）索引列常数说明： 出现(1)时，(3)中的索引不用；出现(2)时，(3)的索引不用。 索引合并的处理方法只用在表中有多个索引上，最多合并索引数目不超过五个。 当且仅当只有(3)情况下的索引时，优化器任选其一使用。 备注：比索引合并更有效的方法是建立组合索引 组合索引有两种使用方式：全部说明方式和部分说明方式。

12、全部说明方式：组合索引列全部出现在查询中。 部分说明方式：组合索引列前面一部分出现在查询中。Copyright new doone All right reserved 单表查询的优化例子：例子：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 * FROM SERV T WHERE T.PLAC

13、E_S_NODE = 600488 AND T.INTEGRAL = 594000248 AND T.STATE = A; B) SELECT * FROM SERV T WHERE T.PLACE_S_NODE = 600488 AND T.INTEGRAL = 594000248; C)SELECT * FROM SERV T WHERE T.PLACE_S_NODE = 600488 AND T.STATE = A;说明：说明： 例子A、 例子B优化器选用的组合索引SERV_PLACE_X_IDX进行索引扫描；例子A采用全部说明方式，例子B采用部分说明方式。例子C优化器则选用SERV_P

14、LACE_S_NODE_IDX、SERV_STATE_IDX进行索引合并，而不能使用组合SERV_PLACE_X_IDX，因为PLACE_S_NODE、STATE不是组合索引列PLACE_S_NODE、INTEGRAL、STATE的前部Copyright new doone All right reserved 多表连接的优化处理连接方式多表连接的基础是两表连接，连接优化的主要工作有：1、有关连接方式的选择、有关连接方式的选择排序合并连接(Sort Merge Join (SMJ) ) 连接属性上都建有索引，则可利用索引已有的排序作合并连接。但在连接属性上没有索引时，则要首先对两表在连接属性上

15、排序，对排序结果再作连接。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_IDSELECT A.MDSE_ID, B.NAME FROM A, B WHERE A.MDSE_SPEC_ID

16、= B.MDSE_SPEC_IDCopyright new doone All right reserved 多表连接的优化处理连接方式嵌套循环(Nested Loops (NL) ) 这个连接方法有驱动表(外部表)的概念。该连接过程是一个2层嵌套循环。 连接过程： 驱动表的Row 1 - Probe -被驱动表的Row 驱动表的Row 2 - Probe -被驱动表的Row 驱动表的Row 3 - Probe -被驱动表的Row . 驱动表的Row n - Probe -被驱动表的Row 在嵌套循环连接中，Oracle读取驱动表中的每一行，然后在被驱动表中检查是否有匹配的行，所有被匹配的行都

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

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

19、连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还要低。 Copyright new doone All right reserved 多表连接的优化处理连接方式总结一下，在哪种情况下用哪种连接方法比较好： A）排序合并连接(Sort Merge Join, SMJ)： a) 对于非等值连接，这种连接方式的效率是比较高的。 b) 如果在关联的列上都有索引，效果更好。 c) 对于将2个较大的表源做连接，该连接方法比NL连接要好一些。 B）嵌套循环(Nested Loops, NL)： a) 如果驱动表(外部表)比较小，并且在被驱动表(内部表)上

20、有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。 b)嵌套循环连接有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。 C）哈希连接(Hash Join, HJ)： a) 这种方法是在oracle7后来引入的，使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。 b) 在2个较大的表源之间连接时会取得相对较好的效率，在一个表源较小时则能取得更好的效率。 c) 只能用于等值连接中Co

21、pyright new doone All right reserved 多表连接的优化处理驱动表的选择多表连接的基础是两表连接，连接优化的主要工作有：2、有关连接次序的优化、有关连接次序的优化驱动表的选择（基于规则的优化器）驱动表的选择（基于规则的优化器） ORACLE的解析器按照从右到左右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表 driving table）将被最先处理。 在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。 首先，扫描第一个表（FROM子句中最后的那个表）

22、并对记录进行派序。 然后扫描第二个表（FROM子句中最后第二个表）。 最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并 。Copyright new doone All right reserved 多表连接的优化处理驱动表的选择例子A例子B例子A： 以SERV表为驱动表，与VIP_INFO表做循环嵌套，产生的结果集与MDSE表再做循环嵌套。例子B： 以MDSE表为驱动表，与SERV表做循环嵌套，产生的结果集与VIP_INFO表再做循环嵌套。Copyright new doone All right reserved where语句的连接顺序ORACLE采用自下而上自下而上的顺序

23、解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前， 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。 例子：例子：Copyright new doone All right reserved 查询优化实例及分析SQL共享共享SQL语句 为了不重复解析相同的SQL语句(因为解析操作比较费资源，会导致性能下降)，在第一次解析之后，ORACLE将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享。因此，当执行一个SQL语句(

24、有时被称为一个游标)时，如果该语句和之前的执行过的某一语句完全相同，并且之前执行的该语句与其执行计划仍然在内存中存在，则ORACLE就不需要再进行分析，直接得到该语句的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并大大节省了内存的使用。 使用这个功能的关键使用这个功能的关键是将执行过的语句尽可能放到内存中，所以这要求有大的共享池(通过设置sharedbufferpool参数值)和尽可能的使用绑定变量的方法执行SQL语句。Copyright new doone All right reserved 查询优化实例及分析SQL共享共享SQL语句条件 当向Oracle提交一个SQL语句

25、，首先在共享内存中查找是否有相同的语句。注意：注意：Oracle对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同(包括空格，换行等) 共享的三个条件： A）字符级的比较 当前被执行的语句和共享池中的语句必须完全相同 （包括大小写、空白和注释） B）两条语句所指的对象必须完全相同 C）两个SQL语句中绑定变量的名字必须相同Copyright new doone All right reserved 查询优化实例及分析类型不一致查询类型一致与不一致的查询（索引项为数值型） serv表serv_id number(10)，建有serv_serv_id_idx(serv_id)索引Cop

26、yright new doone All right reserved 查询优化实例及分析类型不一致查询类型一致与不一致的查询（索引项为字符型） serv表 acc_nbr varchar2(240) ，建有serv_acc_nbr_idx(acc_nbr)索引 Copyright new doone All right reserved 查询优化实例及分析类型不一致查询类型一致与不一致的查询（总结） 当索引项为数值型的时候，在谓词条件在类型不一致的情况下查询， ORACLE的优化器也可以引用该索引，是因为含有隐式转换。 当索引项为字符型的时候，谓词条件就必须为字符，否则ORACLE的优化器不

27、会使用到该索引，而采用全表扫描的方式来执行。 说明：说明：为了避免ORACLE对你的SQL进行隐式的类型转换， 最好把类型转换用显式表现出来。 注意：注意：当字符和数值比较时， ORACLE会优先转换数值类型到字符类型。 例如： SELECT * FROM SERV WHERE ACC_NUBER = 2394701; 转换为下面语句： SELECT * FROM SERV WHERE TO_NUMBER(ACC_NUBER) = 2394701; SELECT * FROM SERV WHERE SERV_ID = 1800093946; 转换为下面语句： SELECT * FROM SER

28、V WHERE SERV_ID = TO_NUMBER(1800093946 ); Copyright new doone All right reserved 查询优化实例及分析操作符优化不等条件的查询说明：说明：对于不等查询的两种等价的不同形式，执行计划是不一样的如果serv表中acc_nbr=2394701的记录占总记录的比例小，使用索引效率较快；若比例较大使用索引也是没有什么效果Copyright new doone All right reserved 查询优化实例及分析操作符优化避免在索引列上使用IS NULL和IS NOT NULL 避免在索引中使用任何可以为空的列，ORACLE

29、将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。 对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。 说明：说明：ORACLE在其索引结构中不存空值，因此对IS NULL条件的查询采用的是全表扫描的方式，而不可能使用索引，且对这种条件的查询不存在改写形式。但是对IS NOT NULL的条件则可用其它方法代替的。 对于对于IS NOT NULL的条件可以如下的等价写法：的条件可以如下的等价写法： 对字符型 列名 （空格串） 对数值型 列名 Copyright new doone All right reserved 查询优化

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

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

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

33、）和Nested Loop Join（NL）。在两张表连接，且内表的目标列上建有索引时，只有在两张表连接，且内表的目标列上建有索引时，只有Nested Loop才能有效地利用到该索引。才能有效地利用到该索引。SMJ即使相关列上建有索引，最多只能因索引的存在，避免数据排序过程。HJ由于须做由于须做HASH运算，索引的存在对数据查询速度几乎没有影响。运算，索引的存在对数据查询速度几乎没有影响。3、看连接顺序是否允许使用相关索引。假设表STAFF_INFO的DEPT_NODE列上有索引，表DEPT_TREE的列DEPT_NODE上无索引，WHERE语句有STAFF_INFO.DEPT_NODE =

34、DEPT_TREE.DEPT_NODE条件。在做在做NL连接时，连接时， STAFF_INFO做为外表，先被访问，由于做为外表，先被访问，由于连接机制原因，外表的数据访问方式是全表扫描，连接机制原因，外表的数据访问方式是全表扫描， STAFF_INFO.DEPT_NODE上的索引显然是用不上，最多在其上做索引全扫描或索引快速全扫描。4、是否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，可能导致极差的“执行计划”。但是不要擅自对数据字典表做分析，否则可能导致死锁，或系统性能下降。5、索引列是否函数的参数。如是，索引在查询时用不上。6、是否存在潜在的数据类型转换。如将字符型数据与数值型

35、数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致上一种现象的发生。7、是否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进行分析，可用SQL语句“analyze table xxxx compute statistics for all indexes;”。ORACLE掌握了充分反映实际的统计数据，才有可能做出正确的选择。掌握了充分反映实际的统计数据，才有可能做出正确的选择。8、索引列的选择性不高。9、索引列值是否可为空（NULL）。如果索引列值可以是空值，在SQL语句中那些要返回NULL值的操作，将不会用到索引，如COUNT

36、（*），而是用全表扫描。这是因为索引中存储值不能为全空。10、看是否有用到并行查询（PQO）。并行查询将不会用到索引。Copyright new doone All right reserved 查询优化实例及分析hint的使用使用注释(comment)来为一个语句添加hint，一个语句块只能有一个注释，而且注释只能放在SELECT, UPDATE, or DELETE关键字的后面。 使用hint的语法： DELETE|INSERT|SELECT|UPDATE /*+ hint text hinttext. */ or DELETE|INSERT|SELECT|UPDATE -+ hint t

37、ext hinttext. 注解： 1) DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。 2) “+”号表示该注释是一个hint，该加号必须立即跟在“/*”的后面，中间不能有空格。 3) hint是介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。 4) text 是其它说明hint的注释性文本 注意：注意：没有正确的指定hint，Oracle将忽略该hint，并且不会给出任何错误。在语句中表用别名的话,那么hint中必须使用表的别名,否则hint将不会生效。Copyrig

38、ht new doone All right reserved 查询优化实例及分析hint的关键词连接有关 （1）use_nl(t1,t2)：表示对表t1、t2关联时采用嵌套循环连接。 （2）use_merge(t1,t2)：表示对表t1、t2关联时采用排序合并连接。 （3）use_hash(t1,t2)：表示对表t1、t2关联时采用哈希连接。 （4）leading(t)：表示在进行表连接时，选择t为驱动表。 （5）ordred：要求优化器按from列出的表顺序进行连接。索引有关 （1）index(tab,idx)：表示对tab表强制指定引用idx索引。 （2）no_index(tab,idx)：表示对tab表强制指定不引用idx索引。优化模式有关 （1）rule：表示该语句进行按rule访问（RBO） （2）first rows： （3）all rows：（RBO）Copyright new doone All right reserved Oracle数据库优化培训 为了企业的明天为了企业的明天, ,我们一起努力我们一起努力! !