ORACLE执行计划和SQL调优

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和e