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。RowSourceandPredicateRowSource(行源)：用在查询中，由上一操作返回的符合条件的行的集合，即可以是表的全部行数据的集合；也可以是表的部分行数据的集合；也可以为对上2个rowsource进行连接操作(如join连接)后得到的行数据集合。Predicate(谓词)：一个查询中的WHERE限制条件DrivingTableDrivingTable(驱动表)：该表又称为外层表(OUTERTABLE)。这个概念用于嵌套与HASH连接中。如果该rowsource返回较多的行数据，则对所有的后续操作有负面影响。注意此处虽然翻译为驱动表，但实际上翻译为驱动行源(drivingrowsource)更为确切。一般说来，是应用查询的限制条件后，返回较少行源的表作为驱动表，所以如果一个大表在WHERE条件有有限制条件(如等值限制)，则该大表作为驱动表也是合适的，所以并不是只有较小的表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。在执行计划中，应该为靠上的那个rowsource，后面会给出具体说明。ProbedTableProbedTable(被探查表)：该表又称为内层表(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子子句的的外部部连接接操作作改写写；各种子子查询询技术术总结结标准子查询反连接子查询inexistsNotinNotexists关联子查询多余的自动转换为嵌套的循环连接可以重写为selectdistinct外部连接可以重写为selectdistinct外部连接非关联子查询自动转换为嵌套的循环连接不适合可以重写为minus操作符的嵌套循环连接不适合例子：：使用用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.f1<>1000,