Oracle数据库优化培训ppt课件

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

2、系数据库管理系统，要处理的一个重要问题就是如何提高查询的效率，即所谓的查询优化。为什么会出现这个问题呢？我们知道SQL言语是一种非过程化的言语，即它只需用户指出“做什么，而不需指出“如何做，这样对用户来说确实方便了，但系统的负担就重了。系统要完成“如何做，就必然有个选择、比较、权衡的过程，即如何选择最正确的存取途径和实现算法。.什么是优化器 查询优化对提高查询效率是至关重要的，在任何一个商品化的RDBMS中，都必需有一个专门担任查询语句优化的程序，称为优化器；是SQL之前分析语句的工具 。 优化器的优化方式： 基于规那么RBO： 优化器遵照Oracle内部预定的规那么 基于代价CBO： 根据语

3、句执行的代价，主要指对CPU和内存的占用。优化器在判别能否运用CBO时，要参照表和索引的统计信息。统计信息要在对表做analyze后才会有。 .优化器的优化方式 Oracle优化器的优化方式主要有四种： Rule：基于规那么； Choose：默许方式。根据表或索引的统计信息，假设有统计信息，那么运用CBO方式；假设没有统计信息，相应列有索引，那么运用RBO方式。 First rows：与Choose类似。不同的是假设表有统计信息，它将以最快的方式前往查询的前几行，以获得最正确呼应时间。 All rows：即完全基于Cost的方式。当一个表有统计信息时，以最快方式前往表一切行，以获得最大吞吐量。

4、没有统计信息那么运用RBO方式。 .优化方式的选择指定优化方式CBO/RBO优化方式为默许选择方式 Choose 情况下，怎样样才干知道SQL是基于代价CBO查询，还是基于规那么RBO查询。COST无值阐明运用基于规那么RBO方式COST有值阐明运用基于代价CBO方式.COST 阐明什么COST是什么？ cost属性的值是一个在oracle内部用来比较各个执行方案所耗费(IOCPU)的代价的值，从而使优化器可以选择最好的执行方案。不同语句的cost值不具有可比性，只能对同一个语句的不同执行方案的cost值进展比较，越小越好。.程序员的要求 ORACLE系统中的优化器做的比较简单，因此这就要求用

5、户要有较强的优化认识。程序员要想获得较优的查询性能，就必需对表的大小、索引的选择率以及更新和存取操作的频度等统计信息了如指掌。ORACLE优化对程序员的要求：.优化原理优化通常有两方面的内容，即逻辑优化和物理优化。逻辑优化包括： 选择运算尽早执行 投影与选择运算同时进展，以防止反复扫描文件 公共子表达式预处置 谓词的简化处置 表达式的恒等变换等 逻辑优化往往是一种等价变换，它的优化会对查询带来绝对益处，这部分优化与用户无关，完全由优化器担任，故我们不关怀这部分优化。.优化原理物理优化包括： 选择有效的存取途径 选择适宜的操作序列 提供较优的操作实现方法 物理优化那么往往是对动态情况的一种权衡。

6、物理优化目前采用的方法有：启发式的、基于统计信息的。智能式的ORACLE的优化器是启发式的，它的中心是一些从阅历中得到的准那么，它的益处是系统代价小，但不利的方面是用户的负担太重。.什么是索引索引 运用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进展排序的一种构造，提供指向存储在表的指定列中的数据值的指针，然后根据指定的排序顺序对这些指针排序。索引列 可以基于数据库表中的单列或多列创建索引。多列索引可以区分其中一列能够有一样值的行。索引类型 A独一索引 独一索引是不允许其中任何两行具有一样索引值的索引。 B主键索引 在数据库中为表定义主键将自动创建主键索引，主键索引是独

7、一索引的特定类型。该索引要求主键中的每个值都独一。 C聚散索引 在聚集索引中，表中行的物理顺序与键值的逻辑索引顺序一样。一个表只能包含一个聚集索引。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。.查询优化实例及分析优 化 准 那么运用ROWID的查询效率最高；运用独一索引的查询要快于运用非独一索引的查询；运用完好阐明索引的查询快于运用部分阐明索引的查询；等条件的查询快于范围条件或不等条件的查询；IS NULL条件的查询不能运用索引；尽量防止查询中出现全表扫描。.单表查询的优化ORACLE中实现单表查询的途径有三种：运用ROWIDACCESS BY ROWID索引扫描INDEX SCAN

8、全表扫描ACCESS FULL TABLE单表查询优化的目的： 如何选择一个最正确存取途径，显然运用ROWID的查询效果最高，索引扫描次之，全表扫描效率最低。在一个单表查询中，假设上述三种方法都可用，显然要选择运用ROWID的方法，下面要引见的是如何在多个索引中选择一个最正确的。在一个查询中，假设有多个索引可用，ORACLE优化器做如下任务：选择可利用的索引，即判别索引与谓词的相容性；选择查询的驱动索引： 假设有独一索引出现，那么以此索引为入口，其他索引不用。 假设有多个非独一索引，且查询谓词为相等谓词，那么采用索引合并的算法。.单表查询的优化索引与谓词有如下的优化关系：1独一索引列常数2非独

9、一索引列常数3索引列常数阐明： 出现(1)时，(3)中的索引不用；出现(2)时，(3)的索引不用。 索引合并的处置方法只用在表中有多个索引上，最多合并索引数目不超越五个。 当且仅当只需(3)情况下的索引时，优化器任选其一运用。 备注：比索引合并更有效的方法是建立组合索引 组合索引有两种运用方式：全部阐明方式和部分阐明方式。 全部阐明方式：组合索引列全部出如今查询中。 部分阐明方式：组合索引列前面一部分出如今查询中。.单表查询的优化例子：SERV表建有组合索引： SERV_PLACE_X_IDX(PLACE_S_NODE, INTEGRAL, STATE)SERV表建有单索引： SERV_PLA

10、CE_S_NODE_IDX(PLACE_S_NODE) SERV_INTEGRAL_IDX(INTEGRAL) SERV_STATE_IDX(STATE)SQL语句： A)SELECT * FROM SERV T WHERE T.PLACE_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_

11、S_NODE = 600488 AND T.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、有关衔接方式的选择排序合并衔接(Sort Merg

12、e Join (SMJ) ) 衔接属性上都建有索引，那么可利用索引已有的排序作合并衔接。但在衔接属性上没有索引时，那么要首先对两表在衔接属性上排序，对排序结果再作衔接。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 NAM

13、E, MDSE_SPEC_IDSELECT A.MDSE_ID, B.NAME FROM A, B WHERE A.MDSE_SPEC_ID = B.MDSE_SPEC_ID.多表衔接的优化处置衔接方式嵌套循环(Nested Loops (NL) ) 这个衔接方法有驱动表(外部表)的概念。该衔接过程是一个2层嵌套循环。 衔接过程： 驱动表的Row 1 - Probe -被驱动表的Row 驱动表的Row 2 - Probe -被驱动表的Row 驱动表的Row 3 - Probe -被驱动表的Row . 驱动表的Row n - Probe -被驱动表的Row 在嵌套循环衔接中，Oracle读取驱动

14、表中的每一行，然后在被驱动表中检查能否有匹配的行，一切被匹配的行都被放到结果集中，然后处置驱动表中的下一行。这个过程不断继续，直到驱动表中的一切行都被处置。这是从衔接操作中可以得到第一个匹配行的最快的方法之一，这种类型的衔接可以用在需求快速呼应的语句中，以呼应速度为主要目的。 阐明：嵌套循环衔接可以先前往曾经衔接的行，而不用等待一切的衔接操作处置完才前往数据，这可以实现快速的呼应时间。.多表衔接的优化处置衔接方式哈希衔接(Hash Join) 这种衔接是在Oracle 7.3以后引入的，从实际上来说比NL与SMJ更高效，而且只用在CBO优化器中。 衔接过程： 1、构建阶段：优化器首先选择一张小

15、表做为驱动表，运用哈希函数对衔接列进展计算产生一张哈希表。通常这个步骤是在内存hash_area_size里面进展的，因此运算很快。 2、探测阶段：优化器对被驱动表的衔接列运用同样的哈希函数计算得到的结果与前面构成的哈希表进展探测前往符合条件的记录。这个阶段中假设被驱动表的衔接列的值没有与驱动表衔接列的值相等的话，那么这些记录将会被丢弃而不进展探测。 阐明：哈希衔接比较适用于前往大数据量结果集的衔接，且只适用于等值衔接。运用哈希衔接必需是在CBO方式下，参数hash_join_enabled设置为true缺省值，还要设置 hash_area_size参数，以使哈希衔接高效运转，由于哈希衔接会在

16、该参数指定大小的内存中运转，过小的参数会使哈希衔接的性能比其他衔接方式还要低。 .多表衔接的优化处置衔接方式总结一下，在哪种情况下用哪种衔接方法比较好： A排序合并衔接(Sort Merge Join, SMJ)： a) 对于非等值衔接，这种衔接方式的效率是比较高的。 b) 假设在关联的列上都有索引，效果更好。 c) 对于将2个较大的表源做衔接，该衔接方法比NL衔接要好一些。 B嵌套循环(Nested Loops, NL)： a) 假设驱动表(外部表)比较小，并且在被驱动表(内部表)上有独一索引，或有高选择性非独一索引时，运用这种方法可以得到较好的效率。 b)嵌套循环衔接有其它衔接方法没有的的

17、一个优点是：可以先前往曾经衔接的行，而不用等待一切的衔接操作处置完才前往数据，这可以实现快速的呼应时间。 C哈希衔接(Hash Join, HJ)： a) 这种方法是在oracle7后来引入的，运用了比较先进的衔接实际，普通来说，其效率应该好于其它2种衔接，但是这种衔接只能用在CBO优化器中，而且需求设置适宜的hash_area_size参数，才干获得较好的性能。 b) 在2个较大的表源之间衔接时会获得相对较好的效率，在一个表源较小时那么能获得更好的效率。 c) 只能用于等值衔接中.多表衔接的优化处置驱动表的选择多表衔接的根底是两表衔接，衔接优化的主要任务有：2、有关衔接次序的优化驱动表的选择

18、基于规那么的优化器 ORACLE的解析器按照从右到左的顺序处置FROM子句中的表名，因此FROM子句中写在最后的表根底表 driving table将被最先处置。 在FROM子句中包含多个表的情况下，他必需选择记录条数最少的表作为根底表。当ORACLE处置多个表时， 会运用排序及合并的方式衔接它们。 首先，扫描第一个表FROM子句中最后的那个表并对记录进展派序。 然后扫描第二个表FROM子句中最后第二个表。 最后将一切从第二个表中检索出的记录与第一个表中适宜记录进展合并 。.多表衔接的优化处置驱动表的选择例子A例子B例子A： 以SERV表为驱动表，与VIP_INFO表做循环嵌套，产生的结果集与

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

20、(sharedbufferpool)中的内存可以被一切的数据库用户共享。因此，当执行一个SQL语句(有时被称为一个游标)时，假设该语句和之前的执行过的某一语句完全一样，并且之前执行的该语句与其执行方案依然在内存中存在，那么ORACLE就不需求再进展分析，直接得到该语句的执行途径。ORACLE的这个功能大大地提高了SQL的执行性能并大大节省了内存的运用。 运用这个功能的关键是将执行过的语句尽能够放到内存中，所以这要求有大的共享池(经过设置sharedbufferpool参数值)和尽能够的运用绑定变量的方法执行SQL语句。.查询优化实例及分析SQL共享共享SQL语句条件 当向Oracle提交一个S

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

22、br varchar2(240) ，建有serv_acc_nbr_idx(acc_nbr)索引 .查询优化实例及分析类型不一致查询类型一致与不一致的查询总结 当索引项为数值型的时候，在谓词条件在类型不一致的情况下查询， ORACLE的优化器也可以援用该索引，是由于含有隐式转换。 当索引项为字符型的时候，谓词条件就必需为字符，否那么ORACLE的优化器不会运用到该索引，而采用全表扫描的方式来执行。 阐明：为了防止ORACLE对他的SQL进展隐式的类型转换， 最好把类型转换用显式表现出来。 留意：当字符和数值比较时， ORACLE会优先转换数值类型到字符类型。 例如： SELECT * FROM

23、SERV WHERE ACC_NUBER = 2394701; 转换为下面语句： SELECT * FROM SERV WHERE TO_NUMBER(ACC_NUBER) = 2394701; SELECT * FROM SERV WHERE SERV_ID = 1800093946; 转换为下面语句： SELECT * FROM SERV WHERE SERV_ID = TO_NUMBER(1800093946 ); .查询优化实例及分析操作符优化不等条件的查询阐明：对于不等查询的两种等价的不同方式，执行方案是不一样的假设serv表中acc_nbr=2394701的记录占总记录的比例小，运

24、用索引效率较快；假设比例较大运用索引也是没有什么效果.查询优化实例及分析操作符优化防止在索引列上运用IS NULL和IS NOT NULL 防止在索引中运用任何可以为空的列，ORACLE将无法运用该索引。对于单列索引，假设列包含空值，索引中将不存在此记录。 对于复合索引，假设每个列都为空，索引中同样不存在此记录。假设至少有一个列不为空，那么记录存在于索引中。 阐明：ORACLE在其索引构造中不存空值，因此对IS NULL条件的查询采用的是全表扫描的方式，而不能够运用索引，且对这种条件的查询不存在改写方式。但是对IS NOT NULL的条件那么可用其它方法替代的。 对于IS NOT NULL的条

25、件可以如下的等价写法： 对字符型 列名 空格串 对数值型 列名 0.查询优化实例及分析操作符优化IN操作 用IN写出来的SQL的优点是比较容易写及明晰易懂，但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL是有区别的。 ORACLE试图将其转换成多个表的衔接，假设转换不胜利那么先执行IN里面的子查询，再查询外层的表记录，假设转换胜利那么直接采用多个表的衔接方式查询。普通的SQL都可以转换胜利，但对于含有分组统计等方面的SQL就不能转换了。 阐明：在业务密集的SQL当中尽量不采用IN操作符，可以用OR或 EXISTS替代。NOT IN操作 此操作是

26、强列引荐不运用的，由于它不能运用表的索引 。 阐明：可以用NOT EXISTS 或外衔接+判别为空替代。 LIKE操作符 LIKE操作符可以运用通配符查询，里面的通配符组合能够到达几乎是恣意的查询，但通配符%在搜索词首出现，是不会运用索引，会降低查询速度。当通配符出如今字符串其他位置时，优化器就能利用索引。 select * from serv where name like PHS%; -运用范围索引 select * from serv where name like %PHS%; -全表扫描.查询优化实例及分析操作符优化 及 2; 高效率： SELECT * FROM TABLE1 WH

27、ERE A = 3; 执行A2与A=3的效果就有很大的区别了，由于A2时ORACLE会先找出为2的记录索引再进展比较，而A=3时ORACLE那么直接找到=3的记录索引。 阐明：在运用 大于操作时，应尽量的思索用=大于等于来替代.查询优化实例及分析ORACLE为什么不运用索引1、检查被索引的列或组合索引的首列能否出如今WHERE条件中，这是“执行方案能用到相关索引的必要条件。2、看采用了哪种类型的衔接方式。ORACLE的共有Sort Merge JoinSMJ、Hash JoinHJ和Nested Loop JoinNL。在两张表衔接，且内表的目的列上建有索引时，只需Nested Loop才干有

28、效地利用到该索引。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上的索引显然是用不上，最多在其上做索引全扫描或索引

29、快速全扫描。4、能否用到系统数据字典表或视图。由于系统数据字典表都未被分析过，能够导致极差的“执行方案。但是不要擅自对数据字典表做分析，否那么能够导致死锁，或系统性能下降。5、索引列能否函数的参数。如是，索引在查询时用不上。6、能否存在潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进展转换，从而导致上一种景象的发生。7、能否为表和相关的索引搜集足够的统计数据。对数据经常有增、删、改的表最好定期对表和索引进展分析，可用SQL语句“analyze table xxxx compute statistics for all indexes;。ORACLE掌握了充分反映实践的统计数据，才有能够做出正确的选择。8、索引列的选择性不高。9、索引列