ORACLE执行计划和SQL调优课件.ppt

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

2、行一些额外的语句，我们将这些额外的语句称之为recursive calls或recursive SQL statements。如当一个DDL语句发出后，ORACLE总是隐含的发出一些recursive SQL语句，来修改数据字典信息，以便用户可以成功的执行该DDL语句。当需要的数据字典信息没有在共享内存中时，经常会发生Recursive calls，这些Recursive calls会将数据字典信息从硬盘读入内存中。用户不比关心这些recursive SQL语句的执行情况，在需要的时候，ORACLE会自动的在内部执行这些语句。当然DML语句也都可能引起recursive SQL。简单的说，我们

3、可以将触发器视为recursive SQL。Row Source and PredicatenRow Source(行源)：用在查询中，由上一操作返回的符合条件的行的集合，即可以是表的全部行数据的集合；也可以是表的部分行数据的集合；也可以为对上2个row source进行连接操作(如join连接)后得到的行数据集合。nPredicate(谓词)：一个查询中的WHERE限制条件Driving TablenDriving Table(驱动表)：该表又称为外层表(OUTER TABLE)。这个概念用于嵌套与HASH连接中。如果该row source返回较多的行数据，则对所有的后续操作有负面影响。注意

4、此处虽然翻译为驱动表，但实际上翻译为驱动行源(driving row source)更为确切。一般说来，是应用查询的限制条件后，返回较少行源的表作为驱动表，所以如果一个大表在WHERE条件有有限制条件(如等值限制)，则该大表作为驱动表也是合适的，所以并不是只有较小的表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。在执行计划中，应该为靠上的那个row source，后面会给出具体说明。Probed TablenProbed Table(被探查表)：该表又称为内层表(INNER TABLE)。在我们从驱动表中得到具体一行的数据后，在该表中寻找符合连接条件的行。所以

5、该表应当为大表(实际上应该为返回较大row source的表)且相应的列上应该有索引。组合索引(concatenated index)n由多个列构成的索引，如create index idx_emp on emp(col1,col2,col3,)，则我们称idx_emp索引为组合索引。在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1列为引导列。当我们进行查询时可以使用”where col1=?”，也可以使用”where col1=?and col2=?”，这样的限制条件都会使用索引，但是”where col2=?”查询就不会使用该索引。所以限制条件

6、中包含先导列时，该限制条件才会使用该组合索引。可选择性(selectivity)n比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的”唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。oracle访问数据的存取方法访问数据的存取方法 n全表扫描（Full Table Scans,FTS）n通过ROWID的表存取(Table Access by ROWID)n索引扫描（Index Scan）索引扫描（Index Scan）n索引唯一扫描(in

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

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

9、句nUnion 或 minus 操作n优化器调用sort merge join 操作n创建索引SQL优化器n概念：是一个为所有的sql语句创建执行计划的工具。n目的：生成最快的，消耗资源最少的执行计划。n两种优化器：基于规则的优化器（RBO)，基于成本的优化器（CBO）。优化器模式nRule 模式：完全基于数据字典生成执行计划；最古老、比较稳定；nChoose 模式：默认的优化器模式。根据统计数据的存在与否确定调用哪一个优化器；nFirst_rows 模式：基于成本的优化器模式，以最快的速度返回记录；nAll_rows 模式：基于成本的优化器模式，确保总体时间最短，使用的资源最小；设置优化器模

10、式的方法nInit.ora参数 optimizer_mode=rule/choose/all_rows/first_rows;n在会话层使用alter session set optimizer_goal=rule/choose/all_rows/first_rows;n在SQL中添加提示/*+rule/all_rows/first_rows*/n设置choose模式时候，将根据是否存在表或索引的统计资料来决定选择RBO或CBO；第二部分 SQL 调整SQL 调整的目标n去掉不必要的大表全表扫描；n缓存小表全表扫描；n尽量使用主机变量代替直接量，减少SQL语句的解析时间；n优化索引的使用；n优

11、化表连接方法；n优化子查询；常见可能导致全表扫描的操作n使用null条件的查询：where xxx is null;n对没有索引的字段查询；n带有like条件的查询：where xxx like%x；n带有not equals条件的查询：,!=,not in等（除非字段分布不平衡，而且存在字段矩形图）n内置函数使索引无效：substr()，to_char()等；n使用all_rows提示；n使用parallel 提示；基于 RBO 调整设置RBO模式的方法nInit.ora参数 optimizer_mode=rule/choose;n在会话层使用alter session set optimi

12、zer_goal=rule/choose;n在SQL中添加提示/*+rule*/n设置choose模式时候，将根据是否存在表或索引的统计资料来决定选择RBO或CBO；RBO特性n总是使用索引（不识别位图索引或基于函数的索引）n总是从驱动表开始n只有在不可避免的情况下，才使用全表扫描n索引选择的随机特性基于RBO的调整原则n驱动表的设置：在RBO中，驱动表是from子句的最后一个表；驱动表应该是返回记录最少的那个表；nWhere子句设置：限制性最强的布尔表达式放在最底层；n添加基于成本的提示，来获得更快的执行计划；基于 CBO 调整设置CBO模式的方法nInit.ora参数 optimizer_

13、mode=all_rows/first_rows/choose;n在会话层使用alter session set optimizer_goal=all_rows/first_rows/choose;n在SQL中添加提示/*+hint*/n设置choose模式时候，将根据是否存在表或索引的统计资料来决定选择RBO或CBO；CBO 特性n前提条件：存在表和索引的统计资料；使用analyze table 和 analyze index 命令从表或索引中收集统计资料（表的记录平均长度，记录数等）；如果没有现存的统计资料，将在sql运行时收集资料，会大大降低性能；n影响CBO执行计划成本评估的初始化参数

14、较多（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等）调整表连接表连接方法n嵌套循环连接（nested loop join）：一个小的内部表和一个外部表。比较内部表的每一条记录和外部表的每一条记录，返回满足条件的记录；n散列连接（hash join）：为较小的表在RAM创建散列表

15、（可以用来从较大的那个表读取记录）；n排序合并连接（sort merge join）：使用连接字段将两个记录集排序然后合并；n星型连接（star join）：几个小型表（将组成虚拟表）和一个大型事实表，然后进行嵌套循环连接；表连接类型n等连接：标准连接；from a,b where a.f1=b.f1；oracle提供nested loop、hash join、sort merge三种连接方式；n外部连接:通过在where子句的等式谓词展览馆放置一个（）来实现；from a,b where a.f1=b.f1(+)，将包括b表中不匹配的字段；n自连接：一个表与自己连接的情况；from emp

16、a,emp b where a.f1-b.f1=10；经常调用nested loop连接；表连接类型（续）n反连接：指使用包含not in 或not exists子句的子查询进行的连接；默认使用嵌套循环算法；n半连接：指在子查询中使用in或exists子句时进行的操作；表连接调整原则nRBO只能调用nested loop和merge sort连接；hash join和star 连接只能在CBO中得到；n尽量不要使用not in反连接子查询，把它替换成标准等连接，用外连接和where column is null子句删除多余的记录；或者尽量替换成not exists子查询，因为它将调用相关联的子

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

18、接排序合并连接连接速度连接表中记录的数目少多慢快调整SQL子查询子查询类型n标准子查询：in 和 exists；n反连接子查询：not in 和 not exists；n关联子查询：指在子查询内部引用外部数据表；from table1 a where (selectfrom table2 b Where a.f1=b.f1)；对于外部数据集的每一条记录，都将重新执行一次内部子查询；n非关联子查询：指在子查询内部不会引用外部的数据表；from table1 a where (select from table2 b)；内部子查询只执行一次；子查询调整原则n只要可能的话，尽可能的避免使用子查询，而

19、用标准的连接操作来代替，这样可以使用提示来更改执行计划；n先考虑子查询的合法性，再考虑进行改写；n使用一个关联子查询时，in与exists子句的子查询的执行计划基本相同；n在外部查询返回相对较少的记录时，关联子查询比非关联子查询执行得更快；子查询调整原则（续）n在内部子查询只有少量的记录时，非关联子查询比关联子查询执行得更快；n关联子查询使用in子句是多余的；而非关联子查询使用exists子句是不恰当的。n使用in子句的非关联子查询可以转换为标准连接操作以及使用select distinct来删除重复的记录；n使用exists子句的关联子查询可以转换为标准连接，但子查询最好只能返回一个记录；子

20、查询调整原则（续）n非关联子查询使用not exists子句是没有意义的；n使用not in子句的非关联子查询可以转sql minus子句，性能相对会高一些；n使用not in子句的关联子查询，可以使用带有select distinct子句的外部连接操作改写；各种子查询技术总结标准子查询反连接子查询inexistsNot inNot exists关联子查询多余的自动转换为嵌套的循环连接可以重写为select distinct 外部连接可以重写为select distinct 外部连接非关联子查询自动转换为嵌套的循环连接不适合可以重写为minus操作符的嵌套循环连接不适合例子：使用in子句的非关

21、联子查询可以使用标准连接操作以及使用select distinct来删除重复的记录；原sql语句：Select ename From empWhere empno in(select empno from bad_credit where bad_credit_datesysdate-365);改写后：Select distinct/*+rule*/enameFrom emp,bad_creditWhere a.empno=b.empno and bad_credit_datesysdate-365;使用提示进行调整提示简介n历史：第一次引入是在oracle7，用来弥补CBO的缺陷，oracl

22、e8i中工作得较好；n目的：用来更改SQL语句 的执行计划；n格式：select(update,delete)/*+hints*/或 select(update,delete)-+hint使用提示遵循的原则n注意检查语法：select/*+hint*/，/*和+之间不能有空格，必须紧跟在select之后，否则无效n使用表别名：如果指定了表别名，就不能使用表名称；n不能使用模式名称：如果指定了模式所有者，那么提示将被忽略；n检验提示：如果指定不可用的访问路径，如：first_rows优化器模式与order by子句不兼容，那么提示将被忽略；使提示无效的条件nCluster，hash：与非簇表一起

23、使用；nMerge_aj,push_subq,Hash_aj：不存在子查询；nIndex：指定的索引不存在；nIndex_combine：不存在位图索引；nParellel：调用的不是全表扫描计划；nStar：事实表中存在不恰当索引；nUse_concat：在where子句中不存在多个索引；nUse_nl：表中不存在索引；提示之一：优化器提示nRule：使oracle为查询应用基于规则的优化模式。在怀疑CBO使用了非优化的执行计划时，使用rule提示；它将忽略表和索引的统计资料；/*+rule*/nAll_rows：基于成本的优化方法。目的是提供最佳的吞吐量和最小的资源消耗。倾向于全表扫描，不

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

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

26、ot exist 子句时执行的操作；如果子查询返回的任何一条记录包含空值，那么该查询将不会返回记录；应尽量避免使用。nHash_aj，Merge_aj：在not in子查询的字段中不存在空值的时候，根据连接的类型，考虑使用这两个提示之一，可以在很大程度上提高not in子查询的性能；/*+hash_aj*/提示之四：索引提示nIndex：优化器将使用指定的索引；如果没有指定索引，优化器将使用表中最佳的索引；/*+index(table,index)*/nIndex_join：要求优化器使用索引连接作为访问路径；nAnd_equal：如果表拥有非唯一的单独字段索引，而且期望使用多个索引服务于该查

27、询，那么使用该提示将合并这些索引；至少两个索引名，但不能超过五个；/*+and_equal(table,index1,index2,)*/索引提示（续）nIndex_asc：要求在范围扫描中使用升序索引；优化器的默认行为，不常用；nNo_index：强制优化器忽略索引得存在。一般用在并行全表扫描性能高于索引范围扫描性能的情况下；等同于full提示；nIndex_desc：要求在范围扫描中使用降序索引；如在max（）计算字段的最大值的时候使用；/*+index_desc(table,index)*/索引提示（续）nIndex_combine：强制使用位图索引作为表的访问路径，对两个位图索引执行R

28、OWID交集操作。如果没有指定索引作参数，优化器将自动选择最佳的位图索引；/*+table(bitmap1,bitmap2)*/nIndex_ffs：强制使用快速完全索引扫描；如果大表中不存在被查询字段的高层索引主键，比如需要选择复合索引中第二个字段的值时，那么快速完全索引扫描总是比全表扫描速度更快；/*+index_ffs(table,comindex)*/索引提示（续）nUse_concat：要求为查询中所有or条件使用union all执行计划；一般用在where子句中存在大量的or条件；提示之五：并行提示nParallel：要求表查询以并行模式执行；一般用在多个cpu的服务器上，与fu

29、ll提示一起使用；/*+full(table)parallel(table,8)*/nNoparallel：不希望对全表扫描使用并行机制，如对小表执行的全表扫描时，使用该提示；提示之六：表访问提示nFull：要求避开索引，调用全表扫描；读取表中大量的数据块时；与parallel一起使用；nHash：选择散列扫描来访问指定的簇表；/*+hash*/nCluster：选择簇扫描来访问指定的簇表；/*+cluster*/nNocache：指定为keep池分配的表数据块放置在default池的中点；很少使用；表访问提示（续）nOrdered：要求表按照from子句指定的顺序进行连接；对连接多于4个表的

30、查询非常有用，可以节省sql解析的时间；/*+ordered*/nOrdered_predicates：用来指定where子句中布尔条件评估的顺序；nPush_subq：要求查询数据块中的所有子查询在执行计划中尽可能早的被执行；用在子查询相对来说不很昂贵，并且在很大程度上降低返回到记录数；如果子查询使用的是排序合并连接或调用远程表，提示将不起作用；调整索引常见的索引类型n普通索引：最常见到的索引，create index idx1 on table1(f1);n复合索引：索引建立在多个字段上；create idx2 on table2(f1,f3);n位图索引：使用位图的方式存储索引；crea

31、te bitmap index idx3 on table3(f1);n基于函数的索引：create idx4 on table4(upper(f2)；索引无效n内置函数使索引无效：from table1 a where substr(a.f1,2,4)，f1上的索引无效；n执行一个数学函数也会使索引无效：from table1 a where a.f1*3 1000,f1上的索引无效；n不相等运算符有时也会使索引无效：from table1 a where a.f1 1000,导致全表扫描，f1上的索引无效；nWhere 子句中使用复合函数的非第一字段将无法使用索引；索引无效（续）n带有li

32、ke条件的查询：where xxx like%x；nNull字符串与字段相连接使索引无效；where f1|=；索引调整n使用索引来消除一些不必要（如表中没有索引，排序合并连接）的排序操作；n通过增加索引来避免不合法的全表扫描（一般来说，在有序表中查询返回的记录数少于表记录数的40，或者在无序表中返回的记录数少于表中记录数的7）；n对于数据列的唯一值较少的字段，建立位图索引，以提高性能；n创建基于函数的索引来避免全表扫描；索引调整（续）n当数据列中数据值不均匀时，建立字段矩形图，以帮助系统选择恰当地访问方式来提高性能；n使用快速完全索引扫描来代替访问表的记录；/*+index_ffs*/第三部

33、分 工具介绍一、Explain plann说明：用以查看SQL语句的执行计划；n准备：运行$ORACLE_HOME/rdbms/admin目录下的utlxplan.sql脚本，建立plan_table表；n装入语句，产生执行方案：explain plan set statement_id=select1 for select a.f1,b.f2,a.f2 from table1 a,table2 b where a.f1=b.f1 and;n查看执行方案：select decode(id,0,lpad(,2*(level-1)|level|.|position)|operation|optio

34、ns|object_name|object_type|decode(id,0,cost=|postion)query_plan From plan_table connect by prior_id=parent_id and statement_id=upper(select1)start with id=0 and statement_id=upper(select1);n或使用第三方工具查看执行计划；n执行方案例子：Query_planSelect statement cost 5762.1 nested loops3.1 nested loops4.1Table access by index rowid 5.1Index range scan index14.2 table access full table13.2 table access full table2二、设置自动跟踪n以sys用户连接；n运行$ORACLE_HOME/sqlplus目录下的plustrace.sql脚本；ngrant plustrace to public，对所有用户有效；n在sql*plus 中运行set autot on命令，将自动跟踪sql的执行计划并提供sql统计资料；谢 谢！