ORACLE执行计划和SQL调优

1、ORACLE 执行计划和SQL调优内容安排排第一部分分：背景景知识第二部分分：SQL调优优第三部分分：工具具介绍第一部分分 背景景知识执行计划划的相关关概念Rowid的概概念rowid是一一个伪列列，既然然是伪列列，那么么这个列列就不是是用户定定义，而而是系统统自己给给加上的的。对每每个表都都有一个个rowid的的伪列，但是表表中并不不物理存存储ROWID列的值值。不过过你可以以像使用用其它列列那样使使用它，但是不不能删除除改列，也不能能对该列列的值进进行修改改、插入入。一旦旦一行数数据插入入数据库库，则rowid在该该行的生生命周期期内是唯唯一的，即即使使该行产产生行迁迁移，行行的rowid

2、也不会会改变。RecursiveSQL概念有时为了了执行用用户发出出的一个个sql语句，Oracle必须执执行一些些额外的的语句，我们将将这些额额外的语语句称之之为recursivecalls或recursiveSQL statements。如当当一个DDL语语句发出出后，ORACLE总总是隐含含的发出出一些recursiveSQL语句，来修改改数据字字典信息息，以便便用户可可以成功功的执行行该DDL语句句。当需需要的数数据字典典信息没没有在共共享内存存中时，经常会会发生Recursivecalls，这些Recursivecalls会会将数据据字典信信息从硬硬盘读入入内存中中。用户户不比关关心

3、这些些recursive SQL语句句的执行行情况，在需要要的时候候，ORACLE会自自动的在在内部执执行这些些语句。当然DML语语句也都都可能引引起recursiveSQL。简单的的说，我我们可以以将触发发器视为为recursive SQL。RowSource andPredicateRowSource(行源源)：用用在查询询中，由由上一操操作返回回的符合合条件的的行的集集合，即即可以是是表的全全部行数数据的集集合；也也可以是是表的部部分行数数据的集集合；也也可以为为对上2个rowsource进进行连接接操作(如join连连接)后后得到的的行数据据集合。Predicate(谓词)：一个个查询

4、中中的WHERE限制条条件Driving TableDriving Table(驱动动表)：该表又又称为外外层表(OUTERTABLE)。这个个概念用用于嵌套套与HASH连连接中。如果该该row source返回回较多的的行数据据，则对对所有的的后续操操作有负负面影响响。注意意此处虽虽然翻译译为驱动动表，但但实际上上翻译为为驱动行行源(driving rowsource)更为确确切。一一般说来来，是应应用查询询的限制制条件后后，返回回较少行行源的表表作为驱驱动表，所以如如果一个个大表在在WHERE条条件有有有限制条条件(如如等值限限制)，则该大大表作为为驱动表表也是合合适的，所以并并不是只只有

5、较小小的表可可以作为为驱动表表，正确确说法应应该为应应用查询询的限制制条件后后，返回回较少行行源的表表作为驱驱动表。在执行行计划中中，应该该为靠上上的那个个row source，后后面会给给出具体体说明。ProbedTableProbedTable(被探查查表)：该表又又称为内内层表(INNERTABLE)。在我我们从驱驱动表中中得到具具体一行行的数据据后，在在该表中中寻找符符合连接接条件的的行。所所以该表表应当为为大表(实际上上应该为为返回较较大rowsource的的表)且且相应的的列上应应该有索索引。组合索引引(concatenatedindex)由多个列列构成的的索引，如createin

6、dexidx_emp on emp(col1,col2,col3,)，则我们们称idx_emp索索引为组组合索引引。在组组合索引引中有一一个重要要的概念念：引导导列(leading column)，在上面面的例子子中，col1列为引引导列。当我们们进行查查询时可可以使用用”where col1=? ”，也可以以使用”wherecol1= ?and col2=?”，这这样的限限制条件件都会使使用索引引，但是是”where col2=? ”查查询就不不会使用用该索引引。所以以限制条条件中包包含先导导列时，该限制制条件才才会使用用该组合合索引。可选择性性(selectivity)比较一下下列中唯唯一

7、键的的数量和和表中的的行数，就可以以判断该该列的可可选择性性。如果果该列的的”唯一一键的数数量/表表中的行行数”的的比值越越接近1，则该该列的可可选择性性越高，该列就就越适合合创建索索引，同同样索引引的可选选择性也也越高。在可选选择性高高的列上上进行查查询时，返回的的数据就就较少，比较适适合使用用索引查查询。oracle访访问数据据的存取取方法全表扫描描（FullTableScans, FTS）通过ROWID的表存存取(TableAccess by ROWID )索引扫描描（Index Scan）索引扫描描（Index Scan）索引唯一一扫描(indexunique scan)索引范围围扫描

8、(indexrangescan)索引全扫扫描(indexfull scan)索引快速速扫描(indexfastfull scan)表访问方方式全表扫描描：读取取表中每每一条记记录，顺顺序读取取；散列获取取：使用用符号散散列主键键来为带带有匹配配散列值值表中的的记录创创建ROWID ；ROWID访问问：通过过指定的的ROWID的的方式在在表中选选定一个个单独的的记录；是访问问记录的的最快方方式；表之间的的连接rowsource(表)之间的的连接顺顺序对于于查询的的效率有有非常大大的影响响。通过过首先存存取特定定的表，即将该该表作为为驱动表表，这样样可以先先应用某某些限制制条件，从而得得到一个个较

9、小的的row source，使使连接的的效率较较高，这这也就是是我们常常说的要要先执行行限制条条件的原原因。一一般是在在将表读读入内存存时，应应用where子句中中对该表表的限制制条件。SQL执执行的的步骤解析：安安全性检检查，语语法检查查；创建：评评估多个个执行计计划，并并选择一一个最优优的执行行计划；执行：捆捆绑变量量，执行行已经创创建的执执行计划划；获取：获获取结果果集，进进行转换换，排序序等；索引访问问方式索引范围围扫描：指从索索引中读读取多个个rowid，是最常常见到的的方式；单个索引引扫描：指从索索引中读读取一个个单独的的rowid；降序索引引范围扫扫描：指指按降序序的方式式从索引

10、引中读取取多个rowid；导致排序序的操作作Orderby子子句Groupby子子句Selectdistinct子子句Union或或 minus 操作作优化器调调用sortmergejoin操操作创建索引引SQL优优化器概念：是是一个为为所有的的sql语句创创建执行行计划的的工具。目的：生生成最快快的，消消耗资源源最少的的执行计计划。两种优化化器：基基于规则则的优化化器（RBO)，基于于成本的的优化器器（CBO）。优化器模模式Rule 模式式：完全全基于数数据字典典生成执执行计划划；最古古老、比比较稳定定；Choose模模式：默认的的优化器器模式。根据统统计数据据的存在在与否确确定调用用哪一个

11、个优化器器；First_rows 模式式：基于于成本的的优化器器模式，以最快快的速度度返回记记录；All_rows模模式：基基于成本本的优化化器模式式，确保保总体时时间最短短，使用用的资源源最小；设置优化化器模式式的方法法Init.ora参数数 optimizer_mode =rule/choose/all_rows/first_rows;在会话层层使用altersession setoptimizer_goal=rule/choose/all_rows/first_rows;在SQL中添加加提示/*+rule/all_rows/first_rows */设置choose模式式时候，将根据据是

12、否存存在表或或索引的的统计资资料来决决定选择择RBO或CBO；第二部分分 SQL调调整SQL调调整的的目标去掉不必必要的大大表全表表扫描；缓存小表表全表扫扫描；尽量使用用主机变变量代替替直接量量，减少少SQL语句的的解析时时间；优化索引引的使用用；优化表连连接方法法；优化子查查询；常见可能能导致全全表扫描描的操作作使用null条条件的查查询：wherexxxisnull;对没有索索引的字字段查询询；带有like条条件的查查询：wherexxxlike%x；带有notequals条条件的查查询：, !=,notin等等（除非非字段分分布不平平衡，而而且存在在字段矩矩形图）内置函数数使索引引无效：

13、substr()，to_char()等；使用all_rows提示；使用parallel 提示示；基于RBO调调整设置RBO模式式的方法法Init.ora参数数 optimizer_mode =rule/choose;在会话层层使用altersession setoptimizer_goal=rule/choose;在SQL中添加加提示/*+ rule*/设置choose模式式时候，将根据据是否存存在表或或索引的的统计资资料来决决定选择择RBO或CBO；RBO特特性总是使用用索引（不识别别位图索索引或基基于函数数的索引引）总是从驱驱动表开开始只有在不不可避免免的情况况下，才才使用全全表扫描描索引

14、选择择的随机机特性基于RBO的调调整原则则驱动表的的设置：在RBO中，驱动表表是from子子句的最最后一个个表；驱驱动表应应该是返返回记录录最少的的那个表表；Where子句句设置：限制性性最强的的布尔表表达式放放在最底底层；添加基于于成本的的提示，来获得得更快的的执行计计划；基于CBO调调整设置CBO模式式的方法法Init.ora参数数 optimizer_mode =all_rows/first_rows/choose;在会话层层使用altersession setoptimizer_goal=all_rows/first_rows/choose;在SQL中添加加提示/*+ hint*/设置

15、choose模式式时候，将根据据是否存存在表或或索引的的统计资资料来决决定选择择RBO或CBO；CBO特特性前提条件件：存在在表和索索引的统统计资料料；使用用analyzetable和和 analyzeindex命命令从从表或索索引中收收集统计计资料（表的记记录平均均长度，记录数数等）；如果没没有现存存的统计计资料，将在sql运运行时收收集资料料，会大大大降低低性能；影响CBO执行行计划成成本评估估的初始始化参数数较多（optimizer_search_limit,optimizer_max_permutations,optimizer_index_caching,hash_area_size

16、,hash_join_enable,hash_multiblock_io_count,star_transformation_enable,optimizer_index_cost_adj等等）调整表连连接表连接方方法嵌套循环环连接（nested loopjoin）：一个小小的内部部表和一一个外部部表。比比较内部部表的每每一条记记录和外外部表的的每一条条记录，返回满满足条件件的记录录；散列连接接（hashjoin）：为较小小的表在在RAM创建散散列表（可以用用来从较较大的那那个表读读取记录录）；排序合并并连接（sortmergejoin）：使使用连接接字段将将两个记记录集排排序然后后合并；星型

17、连接接（starjoin）：几个小小型表（将组成成虚拟表表）和一一个大型型事实表表，然后后进行嵌嵌套循环环连接；表连接类类型等连接：标准连连接；froma,bwherea.f1=b.f1；oracle提供供nested loop、hashjoin、sortmerge三三种连接接方式；外部连接接:通通过在where子句句的等式式谓词展展览馆放放置一个个（）来实现现；from a,bwherea.f1=b.f1(+)，将包括括b表中中不匹配配的字段段；自连接：一个表表与自己己连接的的情况；fromemp a,emp bwherea.f1-b.f1 =10；经常常调用nestedloop连接接；表连

18、接类类型（续续）反连接：指使用用包含notin或或notexists子子句的子子查询进进行的连连接；默默认使用用嵌套循循环算法法；半连接：指在子子查询中中使用in或exists子子句时进进行的操操作；表连接调调整原则则RBO只只能调用用nested loop和和mergesort连接接；hashjoin和star 连接接只能在在CBO中得到到；尽量不要要使用notin反反连接子子查询，把它替替换成标标准等连连接，用用外连接接和where columnisnull子句删删除多余余的记录录；或者者尽量替替换成notexists子查询询，因为为它将调调用相关关联的子子查询；半连接子子查询可可以重新新

19、书写成成标准等等连接，用selectdistinct子句句删除重重复的记记录；表连接调调整原则则（续）如果驱动动表较小小，可以以完全装装入hash_area_size内存中中，使用用散列连连接（hash join）速度比比嵌套循循环连接接（nestedloop）快；在两个表表非常大大的情况况下，经经常使用用嵌套循循环连接接（nestedloop）；生成大型型结果集集的查询询、不使使用where子句的的大表连连接或表表中无可可用索引引的查询询，经常常使用排排序合并并连接（srotmerge)；对于多个个小的维维表和一一个大的的事实表表的情况况下（数数据仓库库），经经常使用用星型连连接(star)

20、；不同表连连接方法法的相对对速度星型连接接嵌套循环环连接散列连接接排序合并并连接连接速度度连接表中中记录的的数目少多慢快调整SQL子查查询子查询类类型标准子查查询：in和和 exists；反连接子子查询：not in 和not exists；关联子查查询：指指在子查查询内部部引用外外部数据据表；fromtable1awhere (selectfromtable2bWherea.f1 =b.f1)；对于于外部数数据集的的每一条条记录，都将重重新执行行一次内内部子查查询；非关联子子查询：指在子子查询内内部不会会引用外外部的数数据表；fromtable1 awhere(select from tab

21、le2b )；内部部子查询询只执行行一次；子查询调调整原则则只要可能能的话，尽可能能的避免免使用子子查询，而用标标准的连连接操作作来代替替，这样样可以使使用提示示来更改改执行计计划；先考虑子子查询的的合法性性，再考考虑进行行改写；使用一个个关联子子查询时时，in与exists子句句的子查查询的执执行计划划基本相相同；在外部查查询返回回相对较较少的记记录时，关联子子查询比比非关联联子查询询执行得得更快；子查询调调整原则则（续）在内部子子查询只只有少量量的记录录时，非非关联子子查询比比关联子子查询执执行得更更快；关联子查查询使用用in子子句是多多余的；而非关关联子查查询使用用exists子句是是不

22、恰当当的。使用in子句的的非关联联子查询询可以转转换为标标准连接接操作以以及使用用select distinct来删除除重复的的记录；使用exists子句句的关联联子查询询可以转转换为标标准连接接，但子子查询最最好只能能返回一一个记录录；子查询调调整原则则（续）非关联子子查询使使用notexists子子句是没没有意义义的；使用notin子句句的非关关联子查查询可以以转sqlminus子句句，性能能相对会会高一些些；使用notin子句句的关联联子查询询，可以以使用带带有selectdistinct子句句的外部部连接操操作改写写；各种子查查询技术术总结标准子查询反连接子查询inexistsNot

23、inNot exists关联子查询多余的自动转换为嵌套的循环连接可以重写为select distinct 外部连接可以重写为select distinct 外部连接非关联子查询自动转换为嵌套的循环连接不适合可以重写为minus操作符的嵌套循环连接不适合例子：使使用in子句的的非关联联子查询询可以使使用标准准连接操操作以及及使用selectdistinct来来删除重重复的记记录；原sql语句：SelectenameFrom empWhereempnoin(select empnofrom bad_creditwherebad_credit_datesysdate-365);改写后：Selectd

24、istinct/*+rule */ enameFrom emp,bad_creditWherea.empno=b.empnoand bad_credit_datesysdate-365;使用提示示进行调调整提示简介介历史：第第一次引引入是在在oracle7，用用来弥补补CBO的缺陷陷，oracle8i中工作作得较好好；目的：用用来更改改SQL语句的的执行行计划；格式：select(update,delete)/*+ hints */ 或或select(update,delete) - +hint使用提示示遵循的的原则注意检查查语法：select /*+hint */ ， /*和+之之间不能能有

25、空格格，必须须紧跟在在select之后，否则无无效使用表别别名：如如果指定定了表别别名，就就不能使使用表名名称；不能使用用模式名名称：如如果指定定了模式式所有者者，那么么提示将将被忽略略；检验提示示：如果果指定不不可用的的访问路路径，如如：first_rows优优化器模模式与orderby子句句不兼容容，那么么提示将将被忽略略；使提示无无效的条条件Cluster，hash：与非簇簇表一起起使用；Merge_aj,push_subq,Hash_aj：不不存在子子查询；Index：指指定的索索引不存存在；Index_combine：不存存在位图图索引；Parellel：调调用的不不是全表表扫描计计

26、划；Star：事实实表中存存在不恰恰当索引引；Use_concat：在where子句句中不存存在多个个索引；Use_nl：表中不不存在索索引；提示之一一：优化化器提示示Rule：使oracle为为查询应应用基于于规则的的优化模模式。在在怀疑CBO使使用了非非优化的的执行计计划时，使用rule提示；它将忽忽略表和和索引的的统计资资料；/*+rule*/All_rows：基基于成本本的优化化方法。目的是是提供最最佳的吞吞吐量和和最小的的资源消消耗。倾倾向于全全表扫描描，不适适用于OLTP系统；依赖于于表和索索引的统统计资料料；/*+all_rows*/First_rows:基于于成本的的优化方方法

27、。目目的是提提供最快快的反映映时间。依赖于于表和索索引的统统计资料料；/*+first_rows */提示之二二：表连连接提示示Use_hash：对对指定的的表执行行一个散散列连接接；如果果有一个个表较小小，通常常快于嵌嵌套循环环连接；在两个个表非常常大的情情况下，散列连连接经常常与并行行查询连连接结合合使用；select /*+use_hash(a,b) parallel(a,4)parallel(b,4)*/;Use_merge：强制执执行一个个排序合合并操作作；对表表执行全全表扫描描；通常常与并行行查询结结合使用用；最适适用于生生成大型型结果集集的查询询、不使使用where子句的的大表连

28、连接或表表中无可可用索引引的查询询；select/*+use_merge(a,b)parallel(a,4)parallel(b,4) */表连接提提示（续续）Use_nl：强制对对目标表表执行嵌嵌套循环环连接；对包含含两个大大表的连连接通常常最快；可以不不用更改改from子句句表的顺顺序来更更改驱动动表（使使用CBO时，from子句句的第一一个表）；是CBO的的默认行行为，比比较少用用；/*+use_nl(a)*/Star：强制制使用星星型查询询计划；查询中中至少存存在三个个表（一一个事实实表和几几个维表表），而而且事实实表（大大表）存存在恰当当的索引引（8i可以使使用位图图索引）；/*+s

29、tar */提示之三三：反连连接提示示反连接是是在SQL语句句中包含含not in或notexist子子句时执执行的操操作；如如果子查查询返回回的任何何一条记记录包含含空值，那么该该查询将将不会返返回记录录；应尽尽量避免免使用。Hash_aj，Merge_aj：在notin子子查询的的字段中中不存在在空值的的时候，根据连连接的类类型，考考虑使用用这两个个提示之之一，可可以在很很大程度度上提高高not in子查询询的性能能；/*+hash_aj */提示之四四：索引引提示Index：优优化器将将使用指指定的索索引；如如果没有有指定索索引，优优化器将将使用表表中最佳佳的索引引；/*+index(t

30、able,index)*/Index_join：要求求优化器器使用索索引连接接作为访访问路径径；And_equal：如果表表拥有非非唯一的的单独字字段索引引，而且且期望使使用多个个索引服服务于该该查询，那么使使用该提提示将合合并这些些索引；至少两两个索引引名，但但不能超超过五个个；/*+and_equal(table,index1,index2,)*/索引提示示（续）Index_asc：要求在在范围扫扫描中使使用升序序索引；优化器器的默认认行为，不常用用；No_index：强强制优化化器忽略略索引得得存在。一般用用在并行行全表扫扫描性能能高于索索引范围围扫描性性能的情情况下；等同于于full提

31、示示；Index_desc：要求求在范围围扫描中中使用降降序索引引；如在在max（）计计算字段段的最大大值的时时候使用用；/*+index_desc(table,index)*/索引提示示（续）Index_combine：强制制使用位位图索引引作为表表的访问问路径，对两个个位图索索引执行行ROWID交交集操作作。如果果没有指指定索引引作参数数，优化化器将自自动选择择最佳的的位图索索引；/*+table(bitmap1,bitmap2)*/Index_ffs：强制使使用快速速完全索索引扫描描；如果果大表中中不存在在被查询询字段的的高层索索引主键键，比如如需要选选择复合合索引中中第二个个字段的的值

32、时，那么快快速完全全索引扫扫描总是是比全表表扫描速速度更快快；/*+index_ffs(table,comindex) */索引提示示（续）Use_concat：要求求为查询询中所有有or条条件使用用unionall执行计计划；一一般用在在where子子句中存存在大量量的or条件；提示之五五：并行行提示Parallel：要要求表查查询以并并行模式式执行；一般用用在多个个cpu的服务务器上，与full提提示一起起使用；/*+ full(table) parallel(table,8) */Noparallel：不希希望对全全表扫描描使用并并行机制制，如对对小表执执行的全全表扫描描时，使使用该提提

33、示；提示之六六：表访访问提示示Full：要求求避开索索引，调调用全表表扫描；读取表表中大量量的数据据块时；与parallel一起使使用；Hash：选择择散列扫扫描来访访问指定定的簇表表；/*+hash */Cluster：选择择簇扫描描来访问问指定的的簇表；/*+ cluster*/Nocache：指定定为keep池池分配的的表数据据块放置置在default池池的中点点；很少少使用；表访问提提示（续续）Ordered：要求求表按照照from子句句指定的的顺序进进行连接接；对连连接多于于4个表表的查询询非常有有用，可可以节省省sql解析的的时间；/*+ ordered*/Ordered_pred

34、icates：用来指指定where子句中中布尔条条件评估估的顺序序；Push_subq：要求查查询数据据块中的的所有子子查询在在执行计计划中尽尽可能早早的被执执行；用用在子查查询相对对来说不不很昂贵贵，并且且在很大大程度上上降低返返回到记记录数；如果子子查询使使用的是是排序合合并连接接或调用用远程表表，提示示将不起起作用；调整索引引常见的索索引类型型普通索引引：最常常见到的的索引，create index idx1ontable1 (f1);复合索引引：索引引建立在在多个字字段上；create idx2ontable2 (f1,f3);位图索引引：使用用位图的的方式存存储索引引；createb

35、itmapindexidx3ontable3(f1);基于函数数的索引引：createidx4 on table4(upper(f2)；索引无效效内置函数数使索引引无效：from table1a where substr(a.f1,2,4)，f1上的的索引无无效；执行一个个数学函函数也会会使索引引无效：from table1a where a.f1*3 1000, f1上的索索引无效效；不相等运运算符有有时也会会使索引引无效：from table1a where a.f11000,导导致全表表扫描，f1上上的索引引无效；Where子子句中使使用复合合函数的的非第一一字段将将无法使使用索引引；索引无效效（续）带有like条条件的查查询：wherexxxlike%x；Nul