ORACLE优化器介绍

1 Oracle9i优化器介绍 By Davis E-Mail:todavis@gmail.com Blog:davis.blogchina.com 选择合适的优化器目标 默认情况下，CBO以最佳吞吐量为目标，这意味着 Oracle使用尽可能少的资源去处理 被语句访问到的所有行；当然 CBO也可以用最快的响应速度来优化 SQL，这意味着 Oracle 用尽可能少的资源去处理被语句访问到的第一行或前面少数行，当然这种情况对于整个语句 来说可能消耗更多的资源。 优化器产生的执行 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 会因“优化器目标”的不同而不同。如果以最佳吞吐量为目标， 结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接； 如果以最快的响应速度为目标，其结果则通常倾向于使用索引扫描和嵌套循环连接。 例如，假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接，排序合并 连接能够较快的返回全部查询结果，而嵌套循环能快速的返回第一行或前面少数行结果。如 果你是以提高吞吐量为优化器目标，优化器就会倾向于选择排序合并连接；如果你的优化器 目标是提高响应速度，则优化器倾向于选择嵌套循环连接。 选择优化器目标要以你的应用为基础，一般规则是： 1、 对于批处理应用，以最佳吞吐量为优化目标为好。例如 Oracle报表应用程序。 2、 对于交互式应用，以最快响应速度为优化目标为好。例如 SQLPLUS的查询。 影响优化器优化目标的因素主要有： 1、 OPTIMIZER_MODE初始化参数。 2、 数据字典中的 CBO统计数据。 3、 用来改变 CBO优化目标的 Hints。 OPTIMIZER_MODE初始化参数 这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下： Value Description CHOOSE 此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也可以使用 基于规则的优化方法(RBO)，其决定于是否有可用的统计信息。 1、 如果在被访问的表中，至少有一个表在数据字典中有可用的统计 信息存在，则优化器使用基于成本的方法。 2、 如果在被访问的表中，只有部分表在数据字典中有可用的统计信 息，优化器仍然会使用基于成本的方法，但是优化器必须为无统 计信息的表利用一些内部信息去尝试其他的统计，比如分配给这 些表的数据块的数量等，这可能会导致产生不理想的执行计划。 3、 如果在被访问的表中，没有一个表在数据字典中有统计信息，则 优化器使用基于规则的方法。 ALL_ROWS 不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞 2 吐量为优化目标。 FIRST_ROWS_n 不论是否有统计信息存在，优化器都使用基于成本的方法，并以最快的 速度返回前 n行数据集，n可以是 1,10,100,1000。 FIRST_ROWS 优化器使用成本与试探法混合的方式，去寻找一个可以最快返回前面少 数行的执行计划。 注：CBO使用试探法产生的执行计划，其成本可能会比不使用试探法要 大。FIRST_ROWS可用于向后兼容和计划稳定性。 RULE 不论是否有统计信息存在，优化器都会使用基于规则的方法。 你可以在 SESSION中改变 CBO优化目标：ALTER SESSION SET OPTIMIZER_MODE。 例如： 1、在初始化参数文件中加入如下语句，可以在实例级改变 CBO优化目标： OPTIMIZER_MODE=FIRST_ROWS_1 2、下面的语句可以改变当前 SESSION的 CBO优化目标： ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1 可以改变 CBO优化目标的 Hints 使用如下 Hints可以单独为具体的 SQL指定 CBO优化目标，SQL语句中 Hints能够覆 盖 OPTIMIZER_MODE初始化参数。 l FIRST_ROWS(n)，n为任意正整数。 l FIRST_ROWS l ALL_ROWS l CHOOSE l RULE 数据字典中的 CBO统计信息 CBO 使用的统计信息存放于数据字典中，你可以使用 DBMS_STATS 包或 ANALYZE 语句以精确的方式或估算的方式来统计对象的物理存储特征和数据分布情况。 注意： Oracle 公司建议使用 DBMS_STATS 包来代替 ANALYZE 语句收集统计信息。 DBMS_STATS 包可以并行的收集统计信息，可以为分区对象收集全局统计信息，以及使用 其他方式优化收集操作。 但是，收集和基于成本优化器无关的信息必须用 ANALYZE而不是 DBMS_STATS，比 如： l 使用 VALIDATE或 LIST CHAINED ROWS 子句。 l 收集 freelist块的信息。 CBO如何对 SQL做最快响应的优化 3 OPTIMIZER_MODE被设置成 FIRST_ROWS_n、FIRST_ROWSS，或者 SQL语句中使 用了 FIRST_ROWS(n)、FIRST_ROWS提示，CBO都会对 SQL做最快响应的优化。 这非常适用于联机用户，像通过 Oracle Forms或Web访问的用户。联机用户的特点是 只对前面少数行感兴趣，很少看整个查询的结果，特别是在查询结果巨大的情况下。对于这 样的用户，优化 SQL 使前面少数行尽可能快速的返回是有意义的，即使产生整个查询结果 的时间并不理想。 CBO在做这种优化时，会产生一个处理第一行或前面少数行消耗成本最低的执行计划。 CBO 有两种用来产生最快响应速度的方法，一个是旧方法一个是新方法。旧的方法就是用 FIRST_ROWS提示或初始化参数，这种方法 CBO会使用成本和规则混合的方式来产生一个 计划。Oracle保留这种方法是为了向后兼容。 新方法 FIRST_ROWS_n或 FIRST_ROWS(n)提示，是完全基于成本的。如果 n值较小， CBO倾向于产生一个包含嵌套循环连接和索引查询的执行计划；如果 n值较大，则 CBO倾 向于产生一个包含散列连接和全表扫描的执行计划。 理解基于成本的优化器 CBO根据可用的访问路径和表、索引等对象的统计信息来确定当前 SQL的哪个执行计 划是最高效的或成本最低的；同时 CBO也会考虑 Hints的建议。 CBO执行下列步骤： 1、 优化器根据可用的访问路径和 Hints为 SQL语句产生一组潜在的执行计划。 2、 优化器根据数据字典的统计信息评估每个计划的成本。 成本就是一个评估值，它与 SQL 语句按照某个计划执行所消耗的计算机资源是成 正比的。优化器基于对计算机资源(I/O、CPU、内存)的评估，计算访问路径和连接顺序 的成本。 3、 优化器对比执行计划的成本，从而选择一个成本最低的执行计划。 CBO包含下列组件： l 查询变换器（Query Transformer） l 评估器（Estimator） l 计划生成器（Plan Generator） 如下图所示： 4 （基于成本的优化器组件） 查询变换器 被解析器解析过的查询语句进入查询变换器，表现出来的是一组查询块(query block)， 这些查询块之间是相互关联的或者是嵌套的，查询的形式决定这些查询块相互之间如何被关 联。查询变换器的主要目的就是决定改变查询的形式是否有利于产生一个好的执行计划。查 询变换器使用四种不同的查询变换技术： l 视图合并（View Merging） l 谓词推进（Predicate Pushing） l 非嵌套子查询（Subquery Unnesting） l 物化视图的查询重写（Query Rewrite with Materialized Views） 最终应用于查询的也可以是以上四种变换技术的任意组合。 视图合并 查询中的每个视图都会被解析器扩展到一个独立的查询块中，这个查询块本质上是用来 描述视图定义的，是视图的结果。优化器的一个任务就是去 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 这个独立视图查询块(view query block)并产生一个视图子计划(subplan)，然后优化器在产生整个查询执行计划的同时使 用视图子计划来处理剩余的查询部分。由于视图是被独立在整个查询之外被优化的，因此这 种技术常常会导致一个不良执行计划的产生。 查询变换器通过将视图查询块合并到查询块中从而消除这种不良执行计划。绝大多数类 型的视图是可以被合并的。在一个视图被合并后，它原有的视图查询块被包含到查询块中， 也就是说视图查询块不存在了，因此也不再需要产生一个子计划。 谓词推进 5 对于那些不能合并的视图，查询变换器能够将相关的谓词从查询块中推进到视图查询块 中。由于被推进的谓词能够用来访问索引或者用于过滤，这个技术通常可以改进那些不能被 合并的视图子计划。 非嵌套的子查询 和视图一样，子查询也是用一个独立的查询块来代表的。子查询是被嵌套在主查询或其 他子查询之中的，计划产生器在找到一个成本最低的执行计划之前被迫要试验所有可能的计 划。由嵌套子查询产生的限制可以在转换为非嵌套的子查询和连接之后消除，经由查询转换 器过滤之后绝大多数的子查询都会被转换为非嵌套的，然后这些非嵌套的子查询产生独立的 子计划，这些子计划按照一种高效的方式进行排列，从而提高了整个查询计划的执行速度。 物化视图的查询重写 物化视图就是把一个查询的结果事先固化存储在一个表里，当发现和物化视图一致的查 询语句就将相应的项用物化视图来重写。由于绝大多数的查询结果都事先计算好了，因此这 种技术可以极大的提高查询速度。查询转换器负责查找和用户查询相关的所有物化视图，用 其中的一个或多个来重写查询。利用物化视图来重写查询也是基于成本的，如果不使用物化 视图的成本更低一些，则不会去使用物化视图。 评估器 评估器会产生下列三个度量值： l 选择性（Selectivity） l 基数（Cardinality） l 成本（Cost） 这些值是相互关联的，一个值由其他值导出，评估器的最终目标是评估计划的总体成本。 如果有统计信息可用，评估器使用统计信息来计算这些值，统计信息可以提高其精确度。 选择性 这里的第一个度量值——选择性，表示所选择的行与行集的比值。所谓行集可以是表、 视图，或者是一个连接或 GROUP BY操作的中间结果。选择性与查询中的谓词有关，比如 last_name=’Smith’，或者一个联合谓词 last_name=’Smith’ and job_type=’Clerk’。一个谓词充 当着一个过滤器的角色，在行集中过滤了一定量的行，谓词的选择性是一个比值，它表示一 个行集经过谓词的过滤后剩下的行占原有行集的比例。其值在 0.0和 1.0之间，0.0表示在行 集中没有行被选择；1.0表示行集中的所有行都被选择了。 如果没有可用的统计信息，评估器为选择性赋予一个内部的缺省值，这个内部缺省值随 着谓词的不同而不同。例如：等式谓词（last_name=’Smith’）的内部缺省值低于范围谓词 （last_name>’Smith’），评估器会假定等式谓词返回的行数小于范围谓词。 当存在可用的统计信息，评估器将使用统计信息来估算选择性。例如：对于一个等式谓 词（ last_name=’Smith’），选择性的值是 distinct last_name 的倒数即：（1/count(distinct last_name)）。但是如果在 last_name 字段上存在直方图（histogram），则选择性值为：count 6 （last_name）where last_name=’Smith’ / count（last_name）where last_name is not null。可见 在数据倾斜的字段上应用直方图能够帮助 CBO进行准确的选择性评估。 基数 基数就是行集中行的数量。基数分为： l 基础基数（Base cardinality）：就是基表中的行数。基础基数在表分析期间获得。如 果表没有可用的统计信息，则评估器利用表中区（extents）的数量来估算基础基数。 l 有效基数（Effective cardinality）：就是从基表中选择的行数。有效基数与具体的谓 词和字段有关。有效基数是根据基础基数和作用于该表的所有谓词的选择性得出 的，如果没有谓词作用于该表，则有效基数就等于基础基数。 l 连接基数（Join cardinality）：就是两个行集在连接之后产生的行数。连接就是由两 个行集产生的笛卡尔积，再由连接谓词过滤结果。因此，连接基数是两个行集基数 与连接谓词选择性的乘积。 l Distinct基数（Distinct cardinality）：就是一个行集的字段 distinct之后的行数。一个 行集的 distinct基数是基于字段中的数据的。例如：一个拥有 100行的行集，如果 一个字段 distinct之后还剩下 20行，则 distinct基数就为 20。 l Group基数（Group cardinality）：就是一个行集在应用 GROUP BY之后产生行的数 量。Group基数依赖于每个组中字段的 distinct基数和行集的行数。 GROUP基数例子： 假如对一个有 100行的行集 group by colx，colx字段的 distinct基数是 30，则 Group基 数为 30。但是如果 group by colx,coly呢？coly字段的 distinct基数是 60，这种情况下 Group 基数大于max(colx distinct基数，coly distinct基数)，而小于min(colx distinct基数*coly distinct 基数，行集的行数)，用公式表示出来如下： group cardinality lies between max ( dist. card. colx , dist. card. coly ) and min ( (dist. card. colx * dist. card. coly) , num rows in row set ) 对于上面的例子 Group基数大于 max（30，60）而小于 min（30*60，100），也就是 Group 基数位于 60和 100之间。 成本 成本是用来描述工作单元或资源使用的。CBO 是用磁盘 I/O、CPU 和内存的使用情况 来作为工作单元的，因此 CBO使用的成本可以描述为，在一次操作的执行过程中所用的磁 盘 I/O数量以及 CPU和内存的总使用量。这里的操作可以是扫描一张表、通过索引访问表、 连接两个表、或者一个行集的排序。一个查询计划的成本就是运行这个查询并产生结果的同 时需要的工作单元的数量。 访问路径（access path）决定着在基表中获得数据所需要的工作单元数量。访问路径可 以是表扫描（table scan）、快速全索引扫描（fast full index scan）、索引扫描（index scan）等。 在表扫描或快速全索引扫描期间，多个块可以在一次 I/O中获得，因此表扫描或快速全索引 扫描的成本依赖于被扫描的块数和多块读取的数量。索引扫描的成本依赖于 B 树的深度、 被扫描的索引页块数量、和用 ROWID获取的行数，使用 ROWID获取行的成本倚赖于索引 7 聚集因子（clustering factor）。 尽管聚集因子是索引的一个属性，它实际也关系到表数据块中被索引的字段值。一较低 的聚集因子表明行被集中在表的少数块里，相反一个较高的聚集因子表明行被随机分散到表 的数据块中。因此，聚集因子过高意味着通过范围扫描用 ROWID获取行成本会较高，因为 需要访问表中过多的块才能返回数据。 聚集因子对成本的影响 假设环境如下： l 一个表有 9行数据。 l 在 col1上有一个非唯一索引 l distinct col1值是 A、B、C l 这个表占据三个 Oracle块 第一种情况：索引聚集因子低，如下图： Block 1 Block 2 Block 3 ------- ------- -------- A A A B B B C C C 索引字段相同的值都在同一个物理块中，这种情况下做范围扫描返回 col1=A的所有的 行成本就很低，因为只需要在表中读取一个块就可以返回数据。 第二种情况：索引聚集因子高，如下图： Block 1 Block 2 Block 3 ------- ------- -------- A B C A B C A B C 索引字段相同的值被分散存储到表中的块，这时要得到 col1=A的行则要读取三个块。 联合单独访问两个表的成本就是连接的成本，在一个连接中分为内行集和外行集。 l 嵌套循环连接（nested loop join）：对于外行集中的每一行都要在内行集寻找全部与 它匹配的行，然后连接。因此，在嵌套循环连接中外行集有多少行，内行集就被访 问多少次。成本计算公式如下： cost = outer access cost + (inner access cost * outer cardinality) l 排序合并连接（sort merge join）：如果两个行集的连接键是无序的，则进行排序。 成本计算公式如下： cost = outer access cost + inner access cost + sort costs (if sort is used) l 散列连接（hash join）：内部行集被散列到内存中，并用连接键建立一个散列表， 然后探测外部行集并连接与之匹配的行。如果内部行集非常大，则只会把一部分散 列到内存中，这叫做一个散列分区。此时，内存中的散列分区探测外部行集并连接 所有匹配的行，重复这个过程直到用完内部行集的所有分区。成本计算公式如下： cost = (outer access cost * num of hash partitions) + inner access cost 计划生成器 8 由于不同的访问路径、连接方式和连接顺序可以任意组合，以不同的方式访问和处理数 据，但可以产生同样的结果，因此一个 SQL 可能存在大量不同的计划。计划生成器的主要 作用正是为查询试验出所有这些可能存在的计划，并选择一个其中成本最低的。 连接顺序就是不同的连接项（如，表）以一定的顺序被访问和连接在一起。例如：有一 个连接按照 t1、t2、t3的顺序，则 t1是第一个被访问的，然后是 t2，访问 t2的同时与 t1做 连接并产生连接后的结果，最后 t3被访问，t3的数据与 t1和 t2产生的中间结果做连接。 在建立一个查询的计划之前要先为每个被嵌套的子查询和未合并的视图建立子计划，每 个嵌套的子查询和未合并的视图都是独立的查询块，这些查询块以自底向上的顺序进行优 化，也就是最里层的查询块最先优化并产生子计划，最外层的查询块最后优化。 计划生成器通过试验不同的访问路径、连接方式和连接顺序去探测各种计划，对于一个 查询来说可能存在的计划与 FROM 字句后面的连接项是成比例的，并以指数增长。然而实 际上计划生成器很少会试验所有的可能存在的计划，如果它发现当前计划的成本已经很低 了，它将停止试验，相反当前计划的成本如果很高它将继续试验其他计划，因此如果计划生 成器一开始就能够找到一个成本较低的计划则会大量减少时间，计划生成器通常按照连接项 有效基数由小到大的顺序排列初使连接,。 理解执行计划 Oracle用来运行一个语句的步骤就叫做执行计划（execution plan），执行计划包含了语 句所涉及的每个表的访问路径和连接顺序。 执行计划概述 使用 EXPLAIN PLAN语句可以查看优化器所选择的执行计划，下面看一个例子： 1、创建 PLAN_TABLE，用来存放执行计划的描述信息： CONNECT HR/your_password @$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL Table created. 当然你也可以改变 PLAN_TABLE的名字。 注意： Oracle公司建议你在做完数据库版本升级之后删除 PLAN_TABLE然后再重建，因为字 段可能会有所变化，这可能会导致脚本失效或 TKPROF失效。 当然你也可以改变 PLAN_TABLE的名字。 2、运行执行计划： 9 EXPLAIN PLAN FOR SELECT e.employee_id, j.job_title, e.salary, d.department_name FROM employees e, jobs j, departments d WHERE e.employee_id < 103 AND e.job_id = j.job_id AND e.department_id = d.department_id; 用下面的语句可以指定 PLAN_TABLE的名字： EXPLAIN PLAN INTO my_plan_table FOR YOUR_SQL; 3、显示执行计划信息： 这里可以用以下两个脚本 UTLXPLS.SQL – 显示计划表信息，以串行的方式处理。 UTLXPLP.SQL – 显示计划表信息，以并行的方式处理。 @$ORACLE_HOME/rdbms/utlxplp.sql; ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 189 | 10 (10)| | 1 | NESTED LOOPS | | 3 | 189 | 10 (10)| | 2 | NESTED LOOPS | | 3 | 141 | 7 (15)| |* 3 | TABLE ACCESS FULL | EMPLOYEES | 3 | 60 | 4 (25)| | 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 513 | 2 (50)| |* 5 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | | | 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 (50)| |* 7 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("E"."EMPLOYEE_ID"<103) 5 - access("E"."JOB_ID"="J"."JOB_ID") 7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") SQL Scratchpad的图形界面显示： 10 该图中每个图标左上角的数字就是执行的顺序号，利用图形工具很容易看出执行计划的 执行顺序。 但是很多情况下我们没用配置图形工具的环境，而且图形工具消耗资源，也不 太稳定，很多人也不太喜欢用图形工具。如果利用上面运行脚本的方法在 sqlplus 中获得执 行计划，对于新手来说不容易看出执行的顺序，通常我们用下面的方法来获得执行计划： 1、conn /as sysdba; 2、@$ORACLE_HOME/sqlplus/admin/plustrce.sql 3、grant plustrace to public 4、@$ORACLE_HOME/rdbms/admin/utlxplan.sql 5、create public synonym plan_table for plan_table; 6、grant all on plan_table to public ; ------------------------------------------------------------------- 以上步骤只需配置一次即可。 7、conn username/password; 8、set autotrace traceonly（如果想看到结果集：set autotrace on，关闭：set autotrace off） 9、set timing on（如果想同时看到语句执行的时间） 10、运行你的 SQL 关于如何看执行计划的顺序，请看下面的例子： set autotrace traceonly select ename,dname from emp, dept where emp.deptno=dept.deptno and dept.dname in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS'); 11 15 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes=248) 1 0 HASH JOIN (Cost=3 Card=8 Bytes=248) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36) 3 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304) 用这种方法产生的执行计划会有两列数字，第一列是 statement_id，第二列是 child_id。 我们可以从 statement_id=0处开始读，当该句有子句则先执行子句，该句就为其子句的父句； 如果该句有多个子句，则子句的执行顺序是从上到下；子句执行完执行父句。 在这个例子中，statement_id=0 有一个子句 statement_id=1，因此在执行 statement_id=0 之前要执行 statement_id=1，但 statement_id=1有两个子句分别为 statement_id=2和 3，因此 这两句要最先执行。此执行计划的执行顺序为：2——>3——>1——>0 后面的 cost说明该 SQL是基于成本优化的，如果没有 cost则是基于规则的。 通过这个例子可以知道学会看执行计划也不是什么难事，关键是要理解执行计划，学会 如何优化执行计划，下面将继续讨论。 理解 CBO访问路径 访问路径就是从数据库中检索数据的方式。通常来说，检索一个表中少量的数据行应该 使用索引访问，但是检索大量数据时全表扫描可能优于索引。 全表扫描（Full Table Scans） 全表扫描将读取 HWM之下的所有数据块，访问表中的所有行，每一行都要经WHERE 子句判断是否满足检索条件。当 Oracle 执行全表扫描时会按顺序读取每个块且只读一次， 因 此 如 果 能 够 一 次 读 取 多 个 数 据 块 ， 可 以 提 高 扫 描 效 率 ， 初 始 化 参 数 DB_FILE_MULTIBLOCK_READ_COUNT 用来设置在一次 I/O 中可以读取数据块的最大数 量。 优化器何时会使用全表扫描 在以下情况中优化器会使用全表扫描： 1、无可用索引 如下面例子： SELECT last_name, first_name FROM employees WHERE UPPER(last_name)=’TOM’ 12 last_name 字段有索引，但在查询中使用了函数，因此该查询不会使用索引。如果想让 这个查询走索引，则需要建立函数索引 create index ind_upper_lastname on last_name (upper(last_name))。特别要注意的是隐式转换，比如 colx 字段是 varchar2 型但存放数字： where colx=123456，这时会发生隐式转换 TO_NUMBER(colx)，此时 colx上的索引也会失效。 2、大量数据 如果优化器认为查询将会访问表中绝大多数的数据块，此时就算索引是可用的也会使用 全表扫描。 3、小表 如果一个表 HWM之下的数据块比 DB_FILE_MULTIBLOCK_READ_COUNT要少，只 需要一次 I/O就能扫完，则使用全表扫描要比使用索引的成本低，此时会使用全表扫描。 如果有这样小表访问频率又高，通常把它固定在内存中为好 alter table table_name storage(buffer_pool keep)。 4、并行 如果在表一级设置了较高的并行度，如 alter table table_name parallel(degree 10)，通常会 使 CBO错误的选择全表扫描。通常不建议在表级的设置并行。 并行查询通常可以提高全表扫描的性能，建议在语句级用 HINTS 来实现并行，如 /*+full(table_name) parallel(table_name degree)*/。 5、全表扫描 hints 如果想强制优化器使用全表扫描可以用提示 FULL。 I/O是针对数据块的而不是行 Oracle的 I/O 是针对数据块的，因此被访问的数据块所占的百分比将影响 CBO 是否选 择全表扫描。通常一个数据块中存储着多条记录，被请求的记录要么聚集在少数几个块中， 要么分散在大量的数据块中。 HWM(High Water Mark) HWM是全表扫描范围的标记，每个全表扫描都要读到 HWM位置。当表 analyze之后 可以在 DBA_TABLES.BLOCKS查到 HWM，当表被 drop、truncate或者 move之后，HWM 将会被重置。需要注意的是，当一个表被大量删除记录之后，HWM下面的大量数据块是空 的，此时若对此表进行全表扫描，Oracle仍然会读到 HWM位置，会对全表扫描的性能产生 极坏的影响。 Rowid扫描 Rowid就是一个记录在数据块中的位置，由于指定了记录在数据库中的精确位置，因此 rowid是检索单条记录的最快方式。 如果通过 rowid 来访问表，Oracle 首先需要获得被检索记录的 rowid，Oracle 可以在 WHERE子句中得到 rowid，但更多的是通过索引扫描来获得，然后 Oracle基于 rowid来定 13 位被检索的每条记录。 优化器何时使用 Rowid 并不是每个索引扫描都伴随着 rowid的访问，如果索引中包含了被访问的所有字段，则 不再需要通过 rowid来访问表。 注意： Rowid 是 Oracle 表示数据存储的内部方法，它可能会由于版本的改变而改变。不推荐通过 在 WHERE中指定 rowid来访问数据，因为行迁移和行链接会导致 rowid变化，exp/imp也 会使 rowid变化。 索引扫描 索引不仅包含被索引字段的值，还包含表中行的位置标识 rowid，如果语句只检索索引 字段，Oracle直接从索引中读取该值而不去访问表，如果语句通过索引检索其他字段值，则 Oracle通过 rowid访问表中记录。 索引扫描类型： l 索引唯一扫描（Index Unique Scans） l 索引范围扫描（Index Range Scans） l 索引降序范围扫描（Index Range Scans Descending） l 索引跳跃扫描（Index Skip Scans） l 全索引扫描（Full Scans） l 快速全索引扫描（Fast Full Index Scans） l 索引连接（Index Joins） l 位图连接（Bitmap Joins） 1、索引唯一扫描 这种扫描通常发生在对一个主键字段或含有唯一约束的字段指定相等条件时，只有单行 记录被访问。 2、索引范围扫描 索引范围扫描是检索数据的常用方式，返回的数据返照索引字段升序排列，字段值相同 的则按照 rowid升序排列。如果在语句中指定了 order by字句，而且排序字段是索引字段时 Oracle将忽略 order by子句。 例如： SQL> select * from t; COLX COLY --------------- --------------- 1 3 1 2 1 1 14 1 0 SQL> create index ind_t on t(coly); SQL> set autotrace on SQL> select * from t where coly>0; COLX COLY --------------- --------------- 1 1 1 2 1 3 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' 2 1 INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE) 没有使用 order by结果集已经是按 coly升序排列的。 SQL> set autotrace traceonly SQL> select * from t where coly>0 order by coly; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' 2 1 INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE) 可以看到执行计划中无 SORT 步骤，说明 Oracle忽略了 order by子句。 3、索引降序范围扫描 如果在 order by中指定了索引是降序排列的，或者使用了 index_desc提示，Oracle可能 会使用索引降序范围扫描。 例如： SQL> select /*+index_desc(t ind_t)*/colx,coly from t where coly<3; COLX COLY --------------- --------------- 1 2 1 1 1 0 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=104) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=4 Bytes=104) 2 1 INDEX (RANGE SCAN DESCENDING) OF 'IND_T' (NON-UNIQUE) (Cost=2 Card=1) 4、索引跳跃式扫描 跳跃式扫描发生在复合索引中，它在逻辑上将索引分离为较小的子索引，当复合索引的 15 某一个字段不在查询中指定时，它将被跳过，从而提高索引扫描的效率。可以使用 index_ss 提示强制使用跳跃扫描。 举个例子： SQL> select* from employees; SEX EMPLOYEE_ID ADDRESS ---- --------------- -------------------- F 98 ABC F 100 ABC F 102 ABC F 104 ABC M 101 ABC M 103 ABC M 105 ABC SQL> create index ind_sex_empid on employees(sex,employee_id); 索引结构如下图所示： SQL>set autotrace traceonly SQL>select/*+index_ss(employees ind_sex_empid)*/* from employees where employee_id=101; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=3 Card=1 Bytes=11) 2 1 INDEX (SKIP SCAN) OF 'IND_SEX_EMPID' (NON-UNIQUE) (Cost=2 Card=1) 5、全索引扫描 如果要使用全索引扫描必须满足两个条件，一是查询涉及的字段都包含在索引中，二是 至少一个索引字段具有非空属性。由于索引键的数据是有序的，因此全索引扫描可以用消除 排序操作。全索引扫描只需要一次 I/O。 select empno,ename from big_emp order by empno,ename; Execution Plan -------------------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11) 1 0 INDEX (FULL SCAN) OF ‘BE_IX’ (Cost=2 Card=1) 16 6、快速全索引扫描 快速全索引扫描只访问索引本身，而不去访问表，因此只有查询涉及的字段都包含在索 引中时才会使用快速全索引扫描。如果想使用快速全索引扫描查询所涉及的字段必须全部包 含在索引中，而且索引中至少有一个字段具有非空属性。满足条件后可以使用 index_ffs 提 示来强制使用快速全索引扫描，快速全索引扫描只适用于 CBO。 快速全索引扫描并不能消除排序操作，因为索引键中的数据没有被排序。不同于全索引 扫描，快速全索引扫描是通过多块读取的方式来读取整个索引的，并可以设置并行方式。 7、索引连接 只有查询涉及的所有字段都包含在索引中，才会使用索引连接，此时只通过访问索引就 能获得所有需要的数据，而不用访问表。索引连接只适用于 CBO，且不能消除排序操作。 可以通过 index_join提示来强制使用索引连接。 8、位图连接 位图连接使用一个位图作为键，然后通过映射函数将比特位转换为 rowid。只有 Oracle9i 企业版才支持位图索引和位图索引连接。 Sample Table Scans Sample table scan 是随机检索表中的数据，当 FROM 后面有 SAMPLE 或 SAMPLE BLOCK子句时，会执行 Sample table scan。 如：SELECT * FROM employees SAMPLE BLOCK (1); CBO如何选择访问路径 CBO首先检查WHERE子句中的条件以及 FROM子句，确定有哪些访问路径是可用的。 然后 CBO使用这个访问路径产生一组可能的执行计划，再通过索引、表的统计信息评估每 个计划的成本，最后优化器选择成本最低的一个。 例 1： SELECT * FROM employees WHERE last_name = 'JACKSON'; 如果 last_name具有唯一约束或者主键约束，优化器了解到只有一行数据被返回，这种 情况下查询具有很强的选择性，优化很可能走唯一索引扫描。 例 2： 还是上面的语句，如果 last_name 不具有唯一约束或主键约束，优化器使用 USER_TAB_COLUMNS.NUM_DISTINCT和 USER_TABLES.NUM_ROWS的统计信息来评 估查询的选择性，估算 last_name为 jackson的记录占了 employees表的比例。 例 3： SELECT * FROM employees 17 WHERE employee_id < 7500; 评估这个查询的选择性时优化器使用 WHERE子句中的边界值 7500和 employee_id字 段的 USER_TAB_COLUMNS.HIGH_VALUE、USER_TAB_COLUMNS.LOW_VALUE，优化 器假定在最小值和最大值之间 employee_id 是平均分布的，优化器确定值小于 7500 的百分 比，然后把这个值作为这个查询的选择性。 例 4： SELECT * FROM employees WHERE employee_id < :e1; 优化器并不知道 e1的值，绑定变量的值每次运行都可能不同，因此优化器不能使用前 面的方法来评估含有绑定变量的查询的选择性，在这种情况下优化器会使用内部缺省值试探 着估算一个选择性。 例 5： SELECT * FROM employees WHERE employee_id BETWEEN :low_e AND :high_e; 优化器会将这句改写为： employee_id >= :low_e employee_id <= :high_e 然后优化器仍然是用内部缺省值来试探着为其评估一个选择性。 例 6： SELECT * FROM employees WHERE employee_id BETWEEN 7500 AND 7800; 优化器会改写为： employee_id >= 7500 employee_id <= 7800 优化器为每个条件独立的评估选择性（S1和 S2），然后用下列公式计算 BETWEEN 的选择 性：S=ABS（S1+S2-1） 理解连接 CBO如何运行连接语句 为一个连接语句选择一个执行计划，优化器必须做出下列相关决策： 1、 访问路径 优化器必