Oracle数据库优化培训.ppt

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 优化原理 作为关系数据库管理系统 要解决的一个重要问题就是如何提高查询的效率 即所谓的查询优化 为什么会出现这个问题呢 我们知道SQL语言是一种非过程化的语言 即它只要用户指出 做什么 而不需指出 如何做 这样对用户来说确实方便了 但系统的负担就重了 系统要完成 如何做 就必然有个选择 比较 权衡的过程 即如何选择最佳的存取途径和实现算法 什么是优化器 查询优化对提高查询效率是至关重要的 在任何一个商品化的RDBMS中 都必须有一个专门负责查询语句优化的程序 称为优化器 是SQL之前分析语句的工具 优化器的优化方式 基于规则 RBO 优化器遵循Oracle内部预定的规则基于代价 CBO 依据语句执行的代价 主要指对CPU和内存的占用 优化器在判断是否使用CBO时 要参照表和索引的统计信息 统计信息要在对表做analyze后才会有 优化器的优化模式 Oracle优化器的优化模式主要有四种 Rule 基于规则 Choose 默认模式 根据表或索引的统计信息 如果有统计信息 则使用CBO方式 如果没有统计信息 相应列有索引 则使用RBO方式 Firstrows 与Choose类似 不同的是如果表有统计信息 它将以最快的方式返回查询的前几行 以获得最佳响应时间 Allrows 即完全基于Cost的模式 当一个表有统计信息时 以最快方式返回表所有行 以获得最大吞吐量 没有统计信息则使用RBO方式 优化模式的选择 指定优化模式 CBO RBO 优化模式为默认选择模式 Choose 情况下 怎么样才能知道SQL是基于代价 CBO 查询 还是基于规则 RBO 查询 COST无值说明使用基于规则 RBO 模式COST有值说明使用基于代价 CBO 模式 COST说明什么 COST是什么 cost属性的值是一个在oracle内部用来比较各个执行计划所耗费 IO CPU 的代价的值 从而使优化器可以选择最好的执行计划 不同语句的cost值不具有可比性 只能对同一个语句的不同执行计划的cost值进行比较 越小越好 程序员的要求 ORACLE系统中的优化器做的比较简单 因此这就要求用户要有较强的优化意识 程序员要想获得较优的查询性能 就必须对表的大小 索引的选择率以及更新和存取操作的频度等统计信息了如指掌 ORACLE优化对程序员的要求 优化原理 优化通常有两方面的内容 即逻辑优化和物理优化 逻辑优化包括 选择运算尽早执行投影与选择运算同时进行 以避免重复扫描文件公共子表达式预处理谓词的简化处理表达式的恒等变换等逻辑优化往往是一种等价变换 它的优化会对查询带来绝对好处 这部分优化与用户无关 完全由优化器负责 故我们不关心这部分优化 优化原理 物理优化包括 选择有效的存取路径选择合适的操作序列提供较优的操作实现方法物理优化则往往是对动态情况的一种权衡 物理优化目前采用的方法有 启发式的 基于统计信息的 智能式的ORACLE的优化器是启发式的 它的核心是一些从经验中得到的准则 它的好处是系统代价小 但不利的方面是用户的负担太重 什么是索引 索引使用索引可快速访问数据库表中的特定信息 索引是对数据库表中一列或多列的值进行排序的一种结构 提供指向存储在表的指定列中的数据值的指针 然后根据指定的排序顺序对这些指针排序 索引列可以基于数据库表中的单列或多列创建索引 多列索引可以区分其中一列可能有相同值的行 索引类型A 唯一索引唯一索引是不允许其中任何两行具有相同索引值的索引 B 主键索引在数据库中为表定义主键将自动创建主键索引 主键索引是唯一索引的特定类型 该索引要求主键中的每个值都唯一 C 聚散索引在聚集索引中 表中行的物理顺序与键值的逻辑 索引 顺序相同 一个表只能包含一个聚集索引 与非聚集索引相比 聚集索引通常提供更快的数据访问速度 查询优化实例及分析 优化准则使用ROWID的查询效率最高 使用唯一索引的查询要快于使用非唯一索引的查询 使用完整说明索引的查询快于使用部分说明索引的查询 等条件的查询快于范围条件或不等条件的查询 ISNULL条件的查询不能使用索引 尽量避免查询中出现全表扫描 单表查询的优化 ORACLE中实现单表查询的途径有三种 使用ROWID ACCESSBYROWID 索引扫描 INDEXSCAN 全表扫描 ACCESSFULLTABLE 单表查询优化的目的 如何选择一个最佳存取路径 显然使用ROWID的查询效果最高 索引扫描次之 全表扫描效率最低 在一个单表查询中 若上述三种方法都可用 显然要选择使用ROWID的方法 下面要介绍的是如何在多个索引中选择一个最佳的 在一个查询中 若有多个索引可用 ORACLE优化器做如下工作 选择可利用的索引 即判别索引与谓词的相容性 选择查询的驱动索引 若有唯一索引出现 则以此索引为入口 其他索引不用 若有多个非唯一索引 且查询谓词为相等谓词 则采用索引合并的算法 单表查询的优化 索引与谓词有如下的优化关系 1 唯一索引列 常数 2 非唯一索引列 常数 3 索引列 常数说明 出现 1 时 3 中的索引不用 出现 2 时 3 的索引不用 索引合并的处理方法只用在表中有多个索引上 最多合并索引数目不超过五个 当且仅当只有 3 情况下的索引时 优化器任选其一使用 备注 比索引合并更有效的方法是建立组合索引组合索引有两种使用方式 全部说明方式和部分说明方式 全部说明方式 组合索引列全部出现在查询中 部分说明方式 组合索引列前面一部分出现在查询中 单表查询的优化 例子 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的前部 多表连接的优化处理 连接方式 多表连接的基础是两表连接 连接优化的主要工作有 1 有关连接方式的选择排序合并连接 SortMergeJoin SMJ 连接属性上都建有索引 则可利用索引已有的排序作合并连接 但在连接属性上没有索引时 则要首先对两表在连接属性上排序 对排序结果再作连接 多表连接的优化处理 连接方式 嵌套循环 NestedLoops NL 这个连接方法有驱动表 外部表 的概念 该连接过程是一个2层嵌套循环 连接过程 驱动表的Row1 Probe 被驱动表的Row驱动表的Row2 Probe 被驱动表的Row驱动表的Row3 Probe 被驱动表的Row 驱动表的Rown Probe 被驱动表的Row在嵌套循环连接中 Oracle读取驱动表中的每一行 然后在被驱动表中检查是否有匹配的行 所有被匹配的行都被放到结果集中 然后处理驱动表中的下一行 这个过程一直继续 直到驱动表中的所有行都被处理 这是从连接操作中可以得到第一个匹配行的最快的方法之一 这种类型的连接可以用在需要快速响应的语句中 以响应速度为主要目标 说明 嵌套循环连接可以先返回已经连接的行 而不必等待所有的连接操作处理完才返回数据 这可以实现快速的响应时间 多表连接的优化处理 连接方式 哈希连接 HashJoin 这种连接是在Oracle7 3以后引入的 从理论上来说比NL与SMJ更高效 而且只用在CBO优化器中 连接过程 1 构建阶段 优化器首先选择一张小表做为驱动表 运用哈希函数对连接列进行计算产生一张哈希表 通常这个步骤是在内存 hash area size 里面进行的 因此运算很快 2 探测阶段 优化器对被驱动表的连接列运用同样的哈希函数计算得到的结果与前面形成的哈希表进行探测返回符合条件的记录 这个阶段中如果被驱动表的连接列的值没有与驱动表连接列的值相等的话 那么这些记录将会被丢弃而不进行探测 说明 哈希连接比较适用于返回大数据量结果集的连接 且只适用于等值连接 使用哈希连接必须是在CBO模式下 参数hash join enabled设置为true 缺省值 还要设置hash area size参数 以使哈希连接高效运行 因为哈希连接会在该参数指定大小的内存中运行 过小的参数会使哈希连接的性能比其他连接方式还要低 多表连接的优化处理 连接方式 总结一下 在哪种情况下用哪种连接方法比较好 A 排序合并连接 SortMergeJoin SMJ a 对于非等值连接 这种连接方式的效率是比较高的 b 如果在关联的列上都有索引 效果更好 c 对于将2个较大的表源做连接 该连接方法比NL连接要好一些 B 嵌套循环 NestedLoops NL a 如果驱动表 外部表 比较小 并且在被驱动表 内部表 上有唯一索引 或有高选择性非唯一索引时 使用这种方法可以得到较好的效率 b 嵌套循环连接有其它连接方法没有的的一个优点是 可以先返回已经连接的行 而不必等待所有的连接操作处理完才返回数据 这可以实现快速的响应时间 C 哈希连接 HashJoin HJ a 这种方法是在oracle7后来引入的 使用了比较先进的连接理论 一般来说 其效率应该好于其它2种连接 但是这种连接只能用在CBO优化器中 而且需要设置合适的hash area size参数 才能取得较好的性能 b 在2个较大的表源之间连接时会取得相对较好的效率 在一个表源较小时则能取得更好的效率 c 只能用于等值连接中 多表连接的优化处理 驱动表的选择 多表连接的基础是两表连接 连接优化的主要工作有 2 有关连接次序的优化 驱动表的选择 基于规则的优化器 ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名 因此FROM子句中写在最后的表 基础表drivingtable 将被最先处理 在FROM子句中包含多个表的情况下 你必须选择记录条数最少的表作为基础表 当ORACLE处理多个表时 会运用排序及合并的方式连接它们 首先 扫描第一个表 FROM子句中最后的那个表 并对记录进行派序 然后扫描第二个表 FROM子句中最后第二个表 最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并 多表连接的优化处理 驱动表的选择 例子A 例子B 例子A 以SERV表为驱动表 与VIP INFO表做循环嵌套 产生的结果集与MDSE表再做循环嵌套 例子B 以MDSE表为驱动表 与SERV表做循环嵌套 产生的结果集与VIP INFO表再做循环嵌套 where语句的连接顺序 ORACLE采用自下而上的顺序解析WHERE子句 根据这个原理 表之间的连接必须写在其他WHERE条件之前 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾 例子 查询优化实例及分析 SQL共享 共享SQL语句为了不重复解析相同的SQL语句 因为解析操作比较费资源 会导致性能下降 在第一次解析之后 ORACLE将SQL语句及解析后得到的执行计划存放在内存中 这块位于系统全局区域SGA systemglobalarea 的共享池 sharedbufferpool 中的内存可以被所有的数据库用户共享 因此 当执行一个SQL语句 有时被称为一个游标 时 如果该语句和之前的执行过的某一语句完全相同 并且之前执行的该语句与其执行计划仍然在内存中存在 则ORACLE就不需要再进行分析 直接得到该语句的执行路径 ORACLE的这个功能大大地提高了SQL的执行性能并大大节省了内存的使用 使用这个功能的关键是将执行过的语句尽可能放到内存中 所以这要求有大的共享池 通过设置sharedbufferpool参数值 和尽可能的使用绑定变量的方法执行SQL语句 查询优化实例及分析 SQL共享 共享SQL语句条件当向Oracle提交一个SQL语句 首先在共享内存中查找是否有相同的语句 注意 Oracle对两者采取的是一种严格匹配 要达成共享 SQL语句必须完全相同 包括空格 换行等 共享的三个条件 A 字符级的比较当前被执行的语句和共享池中的语句必须完全相同 包括大小写 空白和注释 B 两条语句所指的对象必须完全相同C 两个SQL语句中绑定变量的名字必须相同 查询优化实例及分析 类型不一致查询 类型一致与不一致的查询 索引项为数值型 serv表serv idnumber 10 建有serv serv id idx serv id 索引 查询优化实例及分析 类型不一致查询 类型一致与不一致的查询 索引项为字符型 serv表acc nbrvarchar2 240 建有serv acc nbr idx acc nbr 索引 查询优化实例及分析 类型不一致查询 类型一致与不一致的查询 总结 当索引项为数值型的时候 在谓词条件在类型不一致的情况下查询 ORACLE的优化器也可以引用该索引 是因为含有隐式转换 当索引项为字符型的时候 谓词条件就必须为字符 否则ORACLE的优化器不会使用到该索引 而采用全表扫描的方式来执行 说明 为了避免ORACLE对你的SQL进行隐式的类型转换 最好把类型转换用显式表现出来 注意 当字符和数值比较时 ORACLE会优先转换数值类型到字符类型 例如 SELECT FROMSERVWHEREACC NUBER 2394701 转换为下面语句 SELECT FROMSERVWHERETO NUMBER ACC NUBER 2394701 SELECT FROMSERVWHERESERV ID 1800093946 转换为下面语句 SELECT FROMSERVWHERESERV ID TO NUMBER 1800093946 查询优化实例及分析 操作符优化 不等条件的查询 说明 对于不等查询的两种等价的不同形式 执行计划是不一样的 如果serv表中acc nbr 2394701 的记录占总记录的比例小 使用索引效率较快 若比例较大使用索引也是没有什么效果 查询优化实例及分析 操作符优化 避免在索引列上使用ISNULL和ISNOTNULL避免在索引中使用任何可以为空的列 ORACLE将无法使用该索引 对于单列索引 如果列包含空值 索引中将不存在此记录 对于复合索引 如果每个列都为空 索引中同样不存在此记录 如果至少有一个列不为空 则记录存在于索引中 说明 ORACLE在其索引结构中不存空值 因此对ISNULL条件的查询采用的是全表扫描的方式 而不可能使用索引 且对这种条件的查询不存在改写形式 但是对ISNOTNULL的条件则可用其它方法代替的 对于ISNOTNULL的条件可以如下的等价写法 对字符型列名 空格串 对数值型列名 0 查询优化实例及分析 操作符优化 IN操作用IN写出来的SQL的优点是比较容易写及清晰易懂 但是用IN的SQL性能总是比较低的 从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL是有区别的 ORACLE试图将其转换成多个表的连接 如果转换不成功则先执行IN里面的子查询 再查询外层的表记录 如果转换成功则直接采用多个表的连接方式查询 一般的SQL都可以转换成功 但对于含有分组统计等方面的SQL就不能转换了 说明 在业务密集的SQL当中尽量不采用IN操作符 可以用OR或EXISTS代替 NOTIN操作此操作是强列推荐不使用的 因为它不能应用表的索引 说明 可以用NOTEXISTS或 外连接 判断为空 代替 LIKE操作符LIKE操作符可以应用通配符查询 里面的通配符组合可能达到几乎是任意的查询 但通配符 在搜寻词首出现 是不会使用索引 会降低查询速度 当通配符出现在字符串其他位置时 优化器就能利用索引 select fromservwherenamelike PHS 使用范围索引select fromservwherenamelike PHS 全表扫描 查询优化实例及分析 操作符优化 及2 高效率 SELECT FROMTABLE1WHEREA 3 执行A 2与A 3的效果就有很大的区别了 因为A 2时ORACLE会先找出为2的记录索引再进行比较 而A 3时ORACLE则直接找到 3的记录索引 说明 在使用 大于 操作时 应尽量的考虑用 大于等于 来代替 查询优化实例及分析 ORACLE为什么不使用索引 1 检查被索引的列或组合索引的首列是否出现在WHERE条件中 这是 执行计划 能用到相关索引的必要条件 2 看采用了哪种类型的连接方式 ORACLE的共有SortMergeJoin SMJ HashJoin HJ 和NestedLoopJoin 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 INFO做为外表 先被访问 由于连接机制原因 外表的数据访问方式是全表扫描 STAFF INFO DEPT NODE上的索引显然是用不上 最多在其上做索引全扫描或索引快速全扫描 4 是否用到系统数据字典表或视图 由于系统数据字典表都未被分析过 可能导致极差的 执行计划 但是不要擅自对数据字典表做分析 否则可能导致死锁 或系统性能下降 5 索引列是否函数的参数 如是 索引在查询时用不上 6 是否存在潜在的数据类型转换 如将字符型数据与数值型数据比较 ORACLE会自动将字符型用to number 函数进行转换 从而导致上一种现象的发生 7 是否为表和相关的索引搜集足够的统计数据 对数据经常有增 删 改的表最好定期对表和索引进行分析 可用SQL语句 analyzetablexxxxcomputestatisticsforallindexes ORACLE掌握了充分反映实际的统计数据 才有可能做出正确的选择 8 索引列的选择性不高 9 索引列值是否可为空 NULL 如果索引列值可以是空值 在SQL语句