ORACLE执行计划和SQL调优PPT课件

.1，ORACLE执行计划和SQL调整，2，内容调度，第1部分：背景知识第2部分： SQL调整第3部分：工具介绍，3，第1部分背景知识，执行计划的概念，4，Rowid的概念，Rowid为伪列，因此该列为伪列每个表都有一个rowid伪列，但rowid列中的值不会物理存储在表中。 但是，您可以像使用其他列一样使用它们，但不能删除更改的列，也不能更改或插入该列的值。 将行数据插入数据库时，rowid在该行的生命周期中是唯一的。 换句话说，行迁移不会改变行的rowid。 对于，5，Recursivesql概念，Oracle可能需要执行称为“recursivecalls”或“recursiveSQLstatements”的附加语句来执行用户发出的SQL语句。 发出DDL语句后，ORACLE隐式发出recursiveSQL语句来修改数据字典信息，以便用户能够成功执行DDL语句。 当共享存储器中没有必要的数据辞典信息时，频繁发生从硬盘将数据辞典信息读入存储器的Recursivecalls。 用户不要介意执行这些recursiveSQL语句。 ORACLE将根据需要自动在内部运行。 当然，DML语句也可能导致recursiveSQL。 简而言之，触发器可以视为recursiveSQL。6，RowSourceandPredicate，RowSource (行源):在查询中使用的两个RowSource，这两个RowSource可以是满足上一操作返回的条件的行集合，也就是表中所有行数据的集合表中的行数据的一部分Predicate (谓词):查询的WHERE约束条件、7、驱动表和驱动表(驱动表):此表也称为外表。 此概念用于嵌套和HASH连接。 rowsource返回更多的原始数据会对所有后续操作产生负面影响。 请注意，虽然在这里翻译成驱动表，但实际上翻译成驱动行源是正确的。 一般来说，在应用查询的约束条件之后，将较少源的表作为驱动表返回，因此如果大的表对WHERE条件具有约束条件(等值约束等)，则该表也适合作为驱动表，因此不仅小的表作为驱动表发挥作用在实行计划中，应该是上位的rowsource，具体的说明在后面叙述。 8，ProbedTable，ProbedTable (被探测表):这个表也称为内层表(INNERTABLE )。 从驱动表中检索特定行的数据，然后在该表中查找满足连接条件的行。 因此，该表是一个大表(实际上是一个返回大rowsource的表)，对应的列必须具有索引。9、由多列构成的索引，例如concatenatedindex、createindexidx_emponemp(col1、col2、col3、等，将idx_emp索引称为结合索引。 组合索引有一个重要的概念：“读取列”(leadingcolumn )。 在上面的示例中，col1列是读取器列。 执行查询时wherecol1=？ 和col2=？ 的约束条件使用索引，但是wherecol2=？ 查询不使用索引。 因此，仅当约束包含第一列时，约束才使用绑定索引。10、可选择性(selectivity )通过比较以下中的唯一键的数量和表中的行数，能够判断该列的可选择性。 列的唯一键数/表中的行数的比率越接近1，列的选择性就越高，列适合于索引，同一索引的选择性也就越高。 在选择性列中执行查询可减少返回的数据量，并且适合使用索引查询。、11、oracle访问数据的访问方法、基于全表扫描(FullTableScans，fts)rowid的表访问(TableAccessbyROWID )索引扫描(IndexScan )、12、索引扫描(IndexScan ) 索引唯一扫描(indexuniquescan )索引范围扫描(indexrangescan )索引全扫描(indexfullscan )索引高速扫描(indexfastfullscan )、13、表访问方式全表扫描：读取表中的每条记录并按顺序读取哈希检索：使用代码哈希主键为哈希值匹配的表创建记录ROWID rowid访问权限：指定rowid以选择表中的单个记录访问记录的最快方式通过首先访问特定表，将此表作为驱动表，您可以首先应用特定约束条件以获得更小的rowsource，从而提高连接效率。 这是人们常说要先执行约束的原因。 通常，将表读入内存时，会对where子句中的表应用约束条件。15、SQL执行步骤、分析：安全检查、语法检查创建：评估多个执行计划，选择最佳执行计划执行：绑定变量，执行创建的执行计划获取：获取结果集，进行转换、排序等， 16、索引访问方式、 索引范围扫描：从索引读取多个rowid是最普遍的方法单一索引扫描：从索引读取个别的rowid降序索引范围扫描：从索引中按降序读取多个rowid，17，作为排序原因的操作， Orderby子句Groupby子句Selectdistinct子句Union，或者运算优化程序调用sortmergejoin运算制作索引，18，SQL优化程序，概念：制作所有SQL语句的执行计划的工具目的：生成最快、最不消耗资源的执行计划。 基于规则的优化器(RBO )、基于成本的优化器(CBO )这两种优化器。 19、优化器模式、Rule模式：完全基于数据字典生成执行计划的最老、相对稳定的选择模式：默认优化模式。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。21、第二部分SQL调整、22和SQL调整的目标是： 删除不必要的整个表扫描高速缓存表的所有表扫描尽可能使用主机变量而不是直接量，优化表连接方法优化索引使用以缩短SQL语句的分析时间，23，通常可能导致所有表扫描查询没有索引的字段的like条件查询： whyerexxxlike% x； 具有notequals条件的查询： 内置函数，如notin (除非字段分布不均衡，并且具有字段矩形图)，否则将禁用索引： substr ()、to_char ()等； 使用并行提示使用all_rows提示、 24、基于RBO调节、25、如何设置RBO模式、Init.ora参数optimizer_mode=rule/choose； 会话层中的altersessionsetoptimizer _ goal=rule/choose； 将提示/*rule*/choose模式添加到SQL时，RBO或CBO的选择取决于表或索引中是否存在统计信息.26、RBO特性始终使用索引(无法识别位图索引或基于函数的索引) 27、根据RBO调整原则设置驱动器表：在RBO中，驱动器表是from子句中的最后一个表的驱动器表是、 设置Where子句，其返回的记录必须是最少的表：添加最受限的布尔表达式位于底部的基于成本的提示以获得更快的执行计划.28、如何基于CBO调整设置、29、CBO模式，以及Init.ora参数在会话级别altersessionsetoptimizer _ goal=all _ rows/first _ rows/choose； 将提示/*hint*/choose模式添加到SQL时，RBO或CBO的选择取决于表或索引统计信息。30、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 optimizer_index_cost_adj等)、31、表连接协调、32、表连接方法、嵌套循环连接(nestedloopinpoint ) :小的内部表和外部表。将内部表中的每条记录与外部表中的每条记录进行比较，并返回满足条件的记录的散列连接：在RAM中创建一个小表的散列列表(可用于从大表读取记录)“排序合并连接”(sortmergeejoin ) :使用连接字段对两个记录集进行排序和合并：“星形联接”:一些小表(构成虚拟表)和大数值表，以及嵌套环联接、33和表连接类型等连接：标准连接oracle提供了三种连接方法： nestedloop、hashjoin和sortmerge。外部连接：通过在where子句的等式谓语展览馆中放置()来实现froma， bwherea.f1=b.f1()包含b表的不匹配字段自身连接：如果表连接到自己fromempa，empbwherea.f1-b.f1=10； 频繁调用nestedloop连接，34，表连接类型(续)， 反向连接：使用包含notin子句或notexists子句的子查询的连接默认情况下使用嵌套循环算法。半连接：在子查询中使用in子句或exists子句的操作、35、表连接调整原则、 RBO只能调用nestedloop和mergesort连接hashjoin和star连接不能使用只能通过CBO获得的notin反连接子查询。 将其替换为连接(如标准)，删除外连接和wherecolumnisnull子句中的多馀记录，或调用相关子查询，因此请尽可能用notexists子查询替换半连接子查询，如标准可以使用selectdistinct子句删除重复记录.36、表连接协调原则(续)、驱动器表较小时可以完全加载到hash_area_size存储器中，散列连接(hashjoin )的使用速度为嵌套循环(nestedloop ) 如果两个较快的表非常大，则通常使用嵌套循环连接。对于生成较大结果集的查询、不使用where子句的较大表连接或表中没有索引的查询，请使用排序合并连接(srotmerge ) 经常使用星形连接(star )在有小维表和大事实表的情况下(数据仓库)，37，不同的表连接方法的相对速度，星形连接，嵌套环连接，哈希连接，排序统一连接，连接速度调整连接表中记录的记录数，少，多，慢，快，38，SQL子查询，39，子查询类型，标准子查询： in和exists； 反向连接子查询： notin和notexists； 相关子查询：在子查询内部引用外部数据表from table1a where(selectfrom table2bwherea.f1=b.f 1)对于外部数据集中的每条记录， 重新执行内部子查询非相关子查询：不要在子查询内部引用外部数据表from table1a where(selectfrom table2b)仅执行一次内部子查询，40，子查询调整原则是： 您可以使用标准连接操作替换子查询，而不是尽可能使用子查询，从而使用更改解释计划的提示。如果在使用相关子查询之前考虑子查询的有效性，然后考虑重写，则可以使用、 如果in和exists子查询的执行计划基本相同的外部查询返回相对较少的记录，则相关子查询的执行速度比非相关子查询快.41，子查询调整原则(续)，如果内部子查询只有很少的记录， 不相关子查询比相关子查询执行得快的相关子查询使用in子句太多了。不相关子查询不适合使用exists子句。使用in子句的非相关子查询可以转换为标准连接操作，也可以使用selectdistinct删除重复记录。使用exists子句的关系子查询可以转换为标准连接，但子查询