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,导致致全表扫描描，f1上上的索引无无效；Where子句中中使用复合合函数的非非第一字