ORACLE执行计划和SQL调优.ppt

ORACLE执行计划和SQL调优 内容安排 第一部分 背景知识第二部分 SQL调优第三部分 工具介绍 第一部分背景知识 执行计划的相关概念 Rowid的概念 rowid是一个伪列 既然是伪列 那么这个列就不是用户定义 而是系统自己给加上的 对每个表都有一个rowid的伪列 但是表中并不物理存储ROWID列的值 不过你可以像使用其它列那样使用它 但是不能删除改列 也不能对该列的值进行修改 插入 一旦一行数据插入数据库 则rowid在该行的生命周期内是唯一的 即即使该行产生行迁移 行的rowid也不会改变 RecursiveSQL概念 有时为了执行用户发出的一个sql语句 Oracle必须执行一些额外的语句 我们将这些额外的语句称之为 recursivecalls 或 recursiveSQLstatements 如当一个DDL语句发出后 ORACLE总是隐含的发出一些recursiveSQL语句 来修改数据字典信息 以便用户可以成功的执行该DDL语句 当需要的数据字典信息没有在共享内存中时 经常会发生Recursivecalls 这些Recursivecalls会将数据字典信息从硬盘读入内存中 用户不比关心这些recursiveSQL语句的执行情况 在需要的时候 ORACLE会自动的在内部执行这些语句 当然DML语句也都可能引起recursiveSQL 简单的说 我们可以将触发器视为recursiveSQL RowSourceandPredicate RowSource 行源 用在查询中 由上一操作返回的符合条件的行的集合 即可以是表的全部行数据的集合 也可以是表的部分行数据的集合 也可以为对上2个rowsource进行连接操作 如join连接 后得到的行数据集合 Predicate 谓词 一个查询中的WHERE限制条件 DrivingTable DrivingTable 驱动表 该表又称为外层表 OUTERTABLE 这个概念用于嵌套与HASH连接中 如果该rowsource返回较多的行数据 则对所有的后续操作有负面影响 注意此处虽然翻译为驱动表 但实际上翻译为驱动行源 drivingrowsource 更为确切 一般说来 是应用查询的限制条件后 返回较少行源的表作为驱动表 所以如果一个大表在WHERE条件有有限制条件 如等值限制 则该大表作为驱动表也是合适的 所以并不是只有较小的表可以作为驱动表 正确说法应该为应用查询的限制条件后 返回较少行源的表作为驱动表 在执行计划中 应该为靠上的那个rowsource 后面会给出具体说明 ProbedTable ProbedTable 被探查表 该表又称为内层表 INNERTABLE 在我们从驱动表中得到具体一行的数据后 在该表中寻找符合连接条件的行 所以该表应当为大表 实际上应该为返回较大rowsource的表 且相应的列上应该有索引 组合索引 concatenatedindex 由多个列构成的索引 如createindexidx emponemp col1 col2 col3 则我们称idx emp索引为组合索引 在组合索引中有一个重要的概念 引导列 leadingcolumn 在上面的例子中 col1列为引导列 当我们进行查询时可以使用 wherecol1 也可以使用 wherecol1 andcol2 这样的限制条件都会使用索引 但是 wherecol2 查询就不会使用该索引 所以限制条件中包含先导列时 该限制条件才会使用该组合索引 可选择性 selectivity 比较一下列中唯一键的数量和表中的行数 就可以判断该列的可选择性 如果该列的 唯一键的数量 表中的行数 的比值越接近1 则该列的可选择性越高 该列就越适合创建索引 同样索引的可选择性也越高 在可选择性高的列上进行查询时 返回的数据就较少 比较适合使用索引查询 oracle访问数据的存取方法 全表扫描 FullTableScans FTS 通过ROWID的表存取 TableAccessbyROWID 索引扫描 IndexScan 索引扫描 IndexScan 索引唯一扫描 indexuniquescan 索引范围扫描 indexrangescan 索引全扫描 indexfullscan 索引快速扫描 indexfastfullscan 表访问方式 全表扫描 读取表中每一条记录 顺序读取 散列获取 使用符号散列主键来为带有匹配散列值表中的记录创建ROWID ROWID访问 通过指定的ROWID的方式在表中选定一个单独的记录 是访问记录的最快方式 表之间的连接 rowsource 表 之间的连接顺序对于查询的效率有非常大的影响 通过首先存取特定的表 即将该表作为驱动表 这样可以先应用某些限制条件 从而得到一个较小的rowsource 使连接的效率较高 这也就是我们常说的要先执行限制条件的原因 一般是在将表读入内存时 应用where子句中对该表的限制条件 SQL执行的步骤 解析 安全性检查 语法检查 创建 评估多个执行计划 并选择一个最优的执行计划 执行 捆绑变量 执行已经创建的执行计划 获取 获取结果集 进行转换 排序等 索引访问方式 索引范围扫描 指从索引中读取多个rowid 是最常见到的方式 单个索引扫描 指从索引中读取一个单独的rowid 降序索引范围扫描 指按降序的方式从索引中读取多个rowid 导致排序的操作 Orderby子句Groupby子句Selectdistinct子句Union或minus操作优化器调用sortmergejoin操作创建索引 SQL优化器 概念 是一个为所有的sql语句创建执行计划的工具 目的 生成最快的 消耗资源最少的执行计划 两种优化器 基于规则的优化器 RBO 基于成本的优化器 CBO 优化器模式 Rule模式 完全基于数据字典生成执行计划 最古老 比较稳定 Choose模式 默认的优化器模式 根据统计数据的存在与否确定调用哪一个优化器 First rows模式 基于成本的优化器模式 以最快的速度返回记录 All rows模式 基于成本的优化器模式 确保总体时间最短 使用的资源最小 设置优化器模式的方法 Init ora参数optimizer mode rule choose all rows first rows 在会话层使用altersessionsetoptimizer goal rule choose all rows first rows 在SQL中添加提示 rule all rows first rows 设置choose模式时候 将根据是否存在表或索引的统计资料来决定选择RBO或CBO 第二部分SQL调整 SQL调整的目标 去掉不必要的大表全表扫描 缓存小表全表扫描 尽量使用主机变量代替直接量 减少SQL语句的解析时间 优化索引的使用 优化表连接方法 优化子查询 常见可能导致全表扫描的操作 使用null条件的查询 wherexxxisnull 对没有索引的字段查询 带有like条件的查询 wherexxxlike x 带有notequals条件的查询 notin等 除非字段分布不平衡 而且存在字段矩形图 内置函数使索引无效 substr to char 等 使用all rows提示 使用parallel提示 基于RBO调整 设置RBO模式的方法 Init ora参数optimizer mode rule choose 在会话层使用altersessionsetoptimizer goal rule choose 在SQL中添加提示 rule 设置choose模式时候 将根据是否存在表或索引的统计资料来决定选择RBO或CBO RBO特性 总是使用索引 不识别位图索引或基于函数的索引 总是从驱动表开始只有在不可避免的情况下 才使用全表扫描索引选择的随机特性 基于RBO的调整原则 驱动表的设置 在RBO中 驱动表是from子句的最后一个表 驱动表应该是返回记录最少的那个表 Where子句设置 限制性最强的布尔表达式放在最底层 添加基于成本的提示 来获得更快的执行计划 基于CBO调整 设置CBO模式的方法 Init ora参数optimizer mode all rows first rows choose 在会话层使用altersessionsetoptimizer goal all rows first rows choose 在SQL中添加提示 hint 设置choose模式时候 将根据是否存在表或索引的统计资料来决定选择RBO或CBO CBO特性 前提条件 存在表和索引的统计资料 使用analyzetable和analyzeindex命令从表或索引中收集统计资料 表的记录平均长度 记录数等 如果没有现存的统计资料 将在sql运行时收集资料 会大大降低性能 影响CBO执行计划成本评估的初始化参数较多 optimizer search limit optimizer max permutations optimizer index caching hash area size hash join enable hash multiblock io count star transformation enable optimizer index cost adj等 调整表连接 表连接方法 嵌套循环连接 nestedloopjoin 一个小的内部表和一个外部表 比较内部表的每一条记录和外部表的每一条记录 返回满足条件的记录 散列连接 hashjoin 为较小的表在RAM创建散列表 可以用来从较大的那个表读取记录 排序合并连接 sortmergejoin 使用连接字段将两个记录集排序然后合并 星型连接 starjoin 几个小型表 将组成虚拟表 和一个大型事实表 然后进行嵌套循环连接 表连接类型 等连接 标准连接 froma bwherea f1 b f1 oracle提供nestedloop hashjoin sortmerge三种连接方式 外部连接 通过在where子句的等式谓词展览馆放置一个 来实现 froma bwherea f1 b f1 将包括b表中不匹配的字段 自连接 一个表与自己连接的情况 fromempa empbwherea f1 b f1 10 经常调用nestedloop连接 表连接类型 续 反连接 指使用包含notin或notexists子句的子查询进行的连接 默认使用嵌套循环算法 半连接 指在子查询中使用in或exists子句时进行的操作 表连接调整原则 RBO只能调用nestedloop和mergesort连接 hashjoin和star连接只能在CBO中得到 尽量不要使用notin反连接子查询 把它替换成标准等连接 用外连接和wherecolumnisnull子句删除多余的记录 或者尽量替换成notexists子查询 因为它将调用相关联的子查询 半连接子查询可以重新书写成标准等连接 用selectdistinct子句删除重复的记录 表连接调整原则 续 如果驱动表较小 可以完全装入hash area size内存中 使用散列连接 hashjoin 速度比嵌套循环连接 nestedloop 快 在两个表非常大的情况下 经常使用嵌套循环连接 nestedloop 生成大型结果集的查询 不使用where子句的大表连接或表中无可用索引的查询 经常使用排序合并连接 srotmerge 对于多个小的维表和一个大的事实表的情况下 数据仓库 经常使用星型连接 star 不同表连接方法的相对速度 星型连接 嵌套循环连接 散列连接 排序合并连接 连接速度 连接表中记录的数目 少 多 慢 快 调整SQL子查询 子查询类型 标准子查询 in和exists 反连接子查询 notin和notexists 关联子查询 指在子查询内部引用外部数据表 fromtable1awhere select fromtable2bWherea f1 b f1 对于外部数据集的每一条记录 都将重新执行一次内部子查询 非关联子查询 指在子查询内部不会引用外部的数据表 fromtable1awhere select fromtable2b 内部子查询只执行一次 子查询调整原则 只要可能的话 尽可能的避免使用子查询 而用标准的连接操作来代替 这样可以使用提示来更改执行计划 先考虑子查询的合法性 再考虑进行改写 使用一个关联子查询时 in与exists子句的子查询的执行计划基本相同 在外部查询返回相对较少的记录时 关联子查询比非关联子查询执行得更快 子查询调整原则 续 在内部子查询只有少量的记录时 非关联子查询比关联子查询执行得更快 关联子查询使用in子句是多余的 而非关联子查询使用exists子句是不恰当的 使用in子句的非关联子查询可以转换为标准连接操作以及使用selectdistinct来删除重复的记录 使用exists子句的关联子查询可以转换为标准连接 但子查询最好只能返回一个记录 子查询调整原则 续 非关联子查询使用notexists子句是没有意义的 使用notin子句的非关联子查询可以转sqlminus子句 性能相对会高一些 使用notin子句的关联子查询 可以使用带有selectdistinct子句的外部连接操作改写 各种子查询技术总结 例子 使用in子句的非关联子查询可以使用标准连接操作以及使用selectdistinct来删除重复的记录 原sql语句 SelectenameFromempWhereempnoin selectempnofrombad creditwherebad credit date sysdate 365 改写后 Selectdistinct rule enameFromemp bad creditWherea empno b empnoandbad credit date sysdate 365 使用提示进行调整 提示简介 历史 第一次引入是在oracle7 用来弥补CBO的缺陷 oracle8i中工作得较好 目的 用来更改SQL语句的执行计划 格式 select update delete hints 或select update delete hint 使用提示遵循的原则 注意检查语法 select hint 和 之间不能有空格 必须紧跟在select之后 否则无效使用表别名 如果指定了表别名 就不能使用表名称 不能使用模式名称 如果指定了模式所有者 那么提示将被忽略 检验提示 如果指定不可用的访问路径 如 first rows优化器模式与orderby子句不兼容 那么提示将被忽略 使提示无效的条件 Cluster hash 与非簇表一起使用 Merge aj push subq Hash aj 不存在子查询 Index 指定的索引不存在 Index combine 不存在位图索引 Parellel 调用的不是全表扫描计划 Star 事实表中存在不恰当索引 Use concat 在where子句中不存在多个索引 Use nl 表中不存在索引 提示之一 优化器提示 Rule 使oracle为查询应用基于规则的优化模式 在怀疑CBO使用了非优化的执行计划时 使用rule提示 它将忽略表和索引的统计资料 rule All rows 基于成本的优化方法 目的是提供最佳的吞吐量和最小的资源消耗 倾向于全表扫描 不适用于OLTP系统 依赖于表和索引的统计资料 all rows First rows 基于成本的优化方法 目的是提供最快的反映时间 依赖于表和索引的统计资料 first rows 提示之二 表连接提示 Use hash 对指定的表执行一个散列连接 如果有一个表较小 通常快于嵌套循环连接 在两个表非常大的情况下 散列连接经常与并行查询连接结合使用 select use hash a b parallel a 4 parallel b 4 Use merge 强制执行一个排序合并操作 对表执行全表扫描 通常与并行查询结合使用 最适用于生成大型结果集的查询 不使用where子句的大表连接或表中无可用索引的查询 select use merge a b parallel a 4 parallel b 4 表连接提示 续 Use nl 强制对目标表执行嵌套循环连接 对包含两个大表的连接通常最快 可以不用更改from子句表的顺序来更改驱动表 使用CBO时 from子句的第一个表 是CBO的默认行为 比较少用 use nl a Star 强制使用星型查询计划 查询中至少存在三个表 一个事实表和几个维表 而且事实表 大表 存在恰当的索引 8i可以使用位图索引 star 提示之三 反连接提示 反连接是在SQL语句中包含notin或notexist子句时执行的操作 如果子查询返回的任何一条记录包含空值 那么该查询将不会返回记录 应尽量避免使用 Hash aj Merge aj 在notin子查询的字段中不存在空值的时候 根据连接的类型 考虑使用这两个提示之一 可以在很大程度上提高notin子查询的性能 hash aj 提示之四 索引提示 Index 优化器将使用指定的索引 如果没有指定索引 优化器将使用表中最佳的索引 index table index Index join 要求优化器使用索引连接作为访问路径 And equal 如果表拥有非唯一的单独字段索引 而且期望使用多个索引服务于该查询 那么使用该提示将合并这些索引 至少两个索引名 但不能超过五个 and equal table index1 index2 索引提示 续 Index asc 要求在范围扫描中使用升序索引 优化器的默认行为 不常用 No index 强制优化器忽略索引得存在 一般用在并行全表扫描性能高于索引范围扫描性能的情况下 等同于full提示 Index desc 要求在范围扫描中使用降序索引 如在max 计算字段的最大值的时候使用 index desc table index 索引提示 续 Index combine 强制使用位图索引作为表的访问路径 对两个位图索引执行ROWID交集操作 如果没有指定索引作参数 优化器将自动选择最佳的位图索引 table bitmap1 bitmap2 Index ffs 强制使用快速完全索引扫描 如果大表中不存在被查询字段的高层索引主键 比如需要选择复合索引中第二个字段的值时 那么快速完全索引扫描总是比全表扫描速度更快 index ffs table comindex 索引提示 续 Use concat 要求为查询中所有or条件使用unionall执行计划 一般用在where子句中存在大量的or条件 提示之五 并行提示 Parallel 要求表查询以并行模式执行 一般用在多个cpu的服务器上 与full提示一起使用 full table parallel table 8 Noparallel 不希望对全表扫描使用并行机制 如对小表执行的全表扫描时 使用该提示 提示之六 表访问提示 Full 要求避开索引 调用全表扫描 读取表中大量的数据块时 与parallel一起使用 Hash 选择散列扫描来访问指定的簇表 hash Cluster 选择簇扫描来访问指定的簇表 cluster Nocache 指定为keep池分配的表数据块放置在default池的中点 很少使用 表访问提示 续 Ordered 要求表按照from子句指定的顺序进行连接 对连接多于4个表的查询非常有用 可以节省sql解析的时间 ordered Ordered predicates 用来指定where子句中布尔条件评估的顺序 Push subq 要求查询数据块中的所有子查询在执行计划中尽可能早的被执行 用在子查询相对来说不很昂贵 并且在很大程度上降低返回到记录数 如果子查询使用的是排序合并连接或调用远程表 提示将不起作用 调整索引 常见的索引类型 普通索引 最常见到的索引 createindexidx1ontable1 f1 复合索引 索引建立在多个字段上 createidx2ontable2 f1 f3 位图索引 使用位图的方式存储索引 createbitmapindexidx3ontable3 f1 基于函数的索引 createidx4ontable4 upper f2 索引无效 内置函数使索引无效 fromtable1awheresubstr a f1 2 4 f1上的索引无效 执行一个数学函数也会使索引无效 fromtable1awherea f1 3 1000 f1上的索引无效 不相等运算符有时也会使索引无效 fromtable1awherea f11000 导致全表扫描 f1上的索引无效 Where子句中使用复合函数的非第一字段将无法使用索引 索引无效 续 带有like条件的查询 wherexxxlike x Null字符串与字段相连接使索引无效 wheref1 索引调整 使用索引来消除一些不必要 如表中没有索引 排序合并连接 的排序操作 通过增加