Oracle数据库优化培训.ppt
《Oracle数据库优化培训.ppt》由会员分享,可在线阅读,更多相关《Oracle数据库优化培训.ppt(36页珍藏版)》请在三一办公上搜索。
1、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),优化原理,作为关系数据库管理系统,要解决的一个
2、重要问题就是如何提高查询的效率,即所谓的查询优化。为什么会出现这个问题呢?我们知道SQL语言是一种非过程化的语言,即它只要用户指出“做什么”,而不需指出“如何做”,这样对用户来说确实方便了,但系统的负担就重了。系统要完成“如何做”,就必然有个选择、比较、权衡的过程,即如何选择最佳的存取途径和实现算法。,什么是优化器,查询优化对提高查询效率是至关重要的,在任何一个商品化的RDBMS中,都必须有一个专门负责查询语句优化的程序,称为优化器;是SQL之前分析语句的工具。优化器的优化方式:基于规则(RBO):优化器遵循Oracle内部预定的规则 基于代价(CBO):依据语句执行的代价,主要指对CPU和内
3、存的占用。优化器在判断是否使用CBO时,要参照表和索引的统计信息。统计信息要在对表做analyze后才会有。,优化器的优化模式,Oracle优化器的优化模式主要有四种:Rule:基于规则;Choose:默认模式。根据表或索引的统计信息,如果有统计信息,则使用CBO方式;如果没有统计信息,相应列有索引,则使用RBO方式。First rows:与Choose类似。不同的是如果表有统计信息,它将以最快的方式返回查询的前几行,以获得最佳响应时间。All rows:即完全基于Cost的模式。当一个表有统计信息时,以最快方式返回表所有行,以获得最大吞吐量。没有统计信息则使用RBO方式。,优化模式的选择,指
4、定优化模式(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中实现单表查询的途径有三种:使用ROWID(ACCESS BY ROWID)索引扫描(INDEX SCAN)全表扫描(ACCE
8、SS FULL TABLE)单表查询优化的目的:如何选择一个最佳存取路径,显然使用ROWID的查询效果最高,索引扫描次之,全表扫描效率最低。在一个单表查询中,若上述三种方法都可用,显然要选择使用ROWID的方法,下面要介绍的是如何在多个索引中选择一个最佳的。在一个查询中,若有多个索引可用,ORACLE优化器做如下工作:选择可利用的索引,即判别索引与谓词的相容性;选择查询的驱动索引:若有唯一索引出现,则以此索引为入口,其他索引不用。若有多个非唯一索引,且查询谓词为相等谓词,则采用索引合并的算法。,单表查询的优化,索引与谓词有如下的优化关系:(1)唯一索引列常数(2)非唯一索引列常数(3)索引列常
9、数说明:出现(1)时,(3)中的索引不用;出现(2)时,(3)的索引不用。索引合并的处理方法只用在表中有多个索引上,最多合并索引数目不超过五个。当且仅当只有(3)情况下的索引时,优化器任选其一使用。备注:比索引合并更有效的方法是建立组合索引 组合索引有两种使用方式:全部说明方式和部分说明方式。全部说明方式:组合索引列全部出现在查询中。部分说明方式:组合索引列前面一部分出现在查询中。,单表查询的优化,例子:SERV表建有组合索引:SERV_PLACE_X_IDX(PLACE_S_NODE,INTEGRAL,STATE)SERV表建有单索引:SERV_PLACE_S_NODE_IDX(PLACE_
10、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_S_NODE=600488 AND T.STATE=A;说明:例子A、例子B优化器选
11、用的组合索引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 Merge Join(SMJ)连接属性上都建有索引,则可利用索引已有的排序作合并连接。但在连接属性上没有
12、索引时,则要首先对两表在连接属性上排序,对排序结果再作连接。,多表连接的优化处理连接方式,嵌套循环(Nested Loops(NL)这个连接方法有驱动表(外部表)的概念。该连接过程是一个2层嵌套循环。连接过程:驱动表的Row 1-Probe-被驱动表的Row 驱动表的Row 2-Probe-被驱动表的Row 驱动表的Row 3-Probe-被驱动表的Row.驱动表的Row n-Probe-被驱动表的Row 在嵌套循环连接中,Oracle读取驱动表中的每一行,然后在被驱动表中检查是否有匹配的行,所有被匹配的行都被放到结果集中,然后处理驱动表中的下一行。这个过程一直继续,直到驱动表中的所有行都被处
13、理。这是从连接操作中可以得到第一个匹配行的最快的方法之一,这种类型的连接可以用在需要快速响应的语句中,以响应速度为主要目标。说明:嵌套循环连接可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。,多表连接的优化处理连接方式,哈希连接(Hash Join)这种连接是在Oracle 7.3以后引入的,从理论上来说比NL与SMJ更高效,而且只用在CBO优化器中。连接过程:1、构建阶段:优化器首先选择一张小表做为驱动表,运用哈希函数对连接列进行计算产生一张哈希表。通常这个步骤是在内存(hash_area_size)里面进行的,因此运算很快。2、探测阶段:优化器对
14、被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录。这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话,那么这些记录将会被丢弃而不进行探测。说明:哈希连接比较适用于返回大数据量结果集的连接,且只适用于等值连接。使用哈希连接必须是在CBO模式下,参数hash_join_enabled设置为true(缺省值),还要设置 hash_area_size参数,以使哈希连接高效运行,因为哈希连接会在该参数指定大小的内存中运行,过小的参数会使哈希连接的性能比其他连接方式还要低。,多表连接的优化处理连接方式,总结一下,在哪种情况下用哪种连接方法比较好:A
15、)排序合并连接(Sort Merge Join,SMJ):a)对于非等值连接,这种连接方式的效率是比较高的。b)如果在关联的列上都有索引,效果更好。c)对于将2个较大的表源做连接,该连接方法比NL连接要好一些。B)嵌套循环(Nested Loops,NL):a)如果驱动表(外部表)比较小,并且在被驱动表(内部表)上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。b)嵌套循环连接有其它连接方法没有的的一个优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。C)哈希连接(Hash Join,HJ):a)这种方法是在oracle7
![Oracle数据库优化培训.ppt_第1页](https://www.31ppt.com/fileroot1/2023-7/2/5fdf5b78-3869-4809-b0d3-b95a609df5ff/5fdf5b78-3869-4809-b0d3-b95a609df5ff1.gif)
![Oracle数据库优化培训.ppt_第2页](https://www.31ppt.com/fileroot1/2023-7/2/5fdf5b78-3869-4809-b0d3-b95a609df5ff/5fdf5b78-3869-4809-b0d3-b95a609df5ff2.gif)
![Oracle数据库优化培训.ppt_第3页](https://www.31ppt.com/fileroot1/2023-7/2/5fdf5b78-3869-4809-b0d3-b95a609df5ff/5fdf5b78-3869-4809-b0d3-b95a609df5ff3.gif)
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 优化 培训
![提示](https://www.31ppt.com/images/bang_tan.gif)
链接地址:https://www.31ppt.com/p-6513514.html