ORACLE性能优化之SQL优化-优化器

1、Oracle9i优化器介绍By DavisE-Mail: todavisBlog:davis.blogchi 选择合适的优化器目标默认情况下，CB0以最佳吞吐量为目标，这意味着Oracle使用尽可能少的资源去处理被语句访问到的所有行；当然CBO也可以用最快的响应速度来优化SQL，这意味着 Oracle用尽可能少的资源去处理被语句访问到的第一行或前面少数行，当然这种情况对于整个语句 来说可能消耗更多的资源。优化器产生的执行计划会因 优化器目标I的不同而不同。如果以最佳吞吐量为目标， 结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接； 如果以最快的响应速度为目标，其结

2、果则通常倾向于使用索引扫描和嵌套循环连接。例如，假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接，排序合并 连接能够较快的返回全部查询结果，而嵌套循环能快速的返回第一行或前面少数行结果。如 果你是以提高吞吐量为优化器目标，优化器就会倾向于选择排序合并连接；如果你的优化器 目标是提高响应速度，则优化器倾向于选择嵌套循环连接。选择优化器目标要以你的应用为基础，一般规则是：1、 对于批处理应用，以最佳吞吐量为优化目标为好。例如Oracle报表应用程序。2、 对于交互式应用，以最快响应速度为优化目标为好。例如SQLPLUS的查询。 影响优化器优化目标的因素主要有：1、OPTIMIZER_M

3、ODE 初始化参数。2、数据字典中的 CBO统计数据。3、用来改变 CBO优化目标的 Hints。OPTIMIZER_MODE 初始化参数这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下：ValueDescriptionCHOOSE此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也可以使用基于规则的优化方法(RBO)，其决定于是否有可用的统计信息。1、如果在被访问的表中，至少有一个表在数据字典中有可用的统计 信息存在，则优化器使用基于成本的方法。2、如果在被访问的表中，只有部分表在数据字典中有可用的统计信息，优化器仍然会使用基于成本的方法，但是优化器必须为无统计信息的表利

4、用一些内部信息去尝试其他的统计，比如分配给这些表的数据块的数量等，这可能会导致产生不理想的执行计划。3、如果在被访问的表中，没有一个表在数据字典中有统计信息，则 优化器使用基于规则的方法。ALL_ROWS不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞1吐量为优化日标。FIRST_ROWS_ n不论是否有统计信息存在，优化器都使用基于成本的方法，并以最快的 速度返回前n行数据集，n可以是1,10,100,1000。FIRST_ROWS优化器使用成本与试探法混合的方式，去寻找一个可以最快返回前面少 数行的执行计划。注：CBO使用试探法产生的执行计划，其成本可能会比不使用试探法要 大

5、。FIRST ROWS可用于向后兼容和计划稳定性。RULE不论是否有统计信息存在，优化器都会使用基于规则的方法。你可以在 SESSION 中改变 CBO 优化目标：ALTER SESSION SET OPTIMIZER_MODE 。 例如：1在初始化参数文件中加入如下语句，可以在实例级改变CBO优化目标：OPTIMIZER_MODE=FIRST_ROWS_12、下面的语句可以改变当前 SESSION的CBO优化目标：ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1可以改变CBO优化目标的Hints使用如下Hints可以单独为具体的 SQL指定CBO优化

6、目标，SQL语句中Hints能够覆 盖OPTIMIZER_MODE 初始化参数。FIRST_ROWS(n)，n为任意正整数。FIRST_ROWSALL_ROWSCHOOSERULE数据字典中的CBO统计信息CBO使用的统计信息存放于数据字典中，你可以使用DBMS_STATS包或ANALYZE语句以精确的方式或估算的方式来统计对象的物理存储特征和数据分布情况。Oracle公司建议使用DBMS_STATS包来代替 ANALYZE 语句收集统计信息。DBMS_STATS包可以并行的收集统计信息，可以为分区对象收集全局统计信息，以及使用 其他方式优化收集操作。但是，收集和基于成本优化器无关的信息必须用

7、ANALYZE 而不是DBMS_STATS，比如：使用 VALIDATE 或 LIST CHAINED ROWS 子句。收集freelist块的信息。CBO 如何对SQL做最快响应的优化OPTIMIZER_MODE 被设置成 FIRST_ROWS_n 、 FIRST_ROWSS ，或者 SQL 语句中使 用了 FIRST_ROWS(n) 、FIRST_ROWS 提示， CBO 都会对 SQL 做最快响应的优化。这非常适用于联机用户，像通过 Oracle Forms 或 Web 访问的用户。联机用户的特点是 只对前面少数行感兴趣，很少看整个查询的结果，特别是在查询结果巨大的情况下。对于这 样的用

8、户，优化 SQL 使前面少数行尽可能快速的返回是有意义的，即使产生整个查询结果 的时间并不理想。CBO 在做这种优化时，会产生一个处理第一行或前面少数行消耗成本最低的执行计划。 CBO 有两种用来产生最快响应速度的方法，一个是旧方法一个是新方法。旧的方法就是用 FIRST_ROWS 提示或初始化参数，这种方法 CBO 会使用成本和规则混合的方式来产生一个 计划。 Oracle 保留这种方法是为了向后兼容。新方法 FIRST_ROWS_n 或 FIRST_ROWS(n) 提示，是完全基于成本的。如果 n 值较小， CBO 倾向于产生一个包含嵌套循环连接和索引查询的执行计划；如果 n 值较大，则

9、CBO 倾 向于产生一个包含散列连接和全表扫描的执行计划。理解基于成本的优化器CBO 根据可用的访问路径和表、索引等对象的统计信息来确定当前 SQL 的哪个执行计 划是最高效的或成本最低的；同时 CBO 也会考虑 Hints 的建议。CBO 执行下列步骤：1、优化器根据可用的访问路径和Hints 为 SQL 语句产生一组潜在的执行计划。2、优化器根据数据字典的统计信息评估每个计划的成本。成本就是一个评估值，它与SQL语句按照某个计划执行所消耗的计算机资源是成正比的。优化器基于对计算机资源 (I/O、CPU、内存)的评估，计算访问路径和连接顺序 的成本。3、优化器对比执行计划的成本，从而选择一个

10、成本最低的执行计划。CBO 包含下列组件： 查询变换器( Query Transformer ) 评估器( Estimator) 计划生成器( Plan Generator)如下图所示：I Fa侶百计I (from Paiscr)J(基于成本的优化器组件)查询变换器被解析器解析过的查询语句进入查询变换器，表现出来的是一组查询块(query这些查询块之间是相互关联的或者是嵌套的，查询的形式决定这些查询块相互之间如何被关 联。查询变换器的主要目的就是决定改变查询的形式是否有利于产生一个好的执行计划。查 询变换器使用四种不同的查询变换技术：视图合并(View Merging)谓词推进(Predica

11、te Pushing)非嵌套子查询(Subquery Unnesting) 物化视图的查询重写(Query Rewrite with Materialized Views )最终应用于查询的也可以是以上四种变换技术的任意组合。视图合并查询中的每个视图都会被解析器扩展到一个独立的查询块中，这个查询块本质上是用来 描述视图定义的，是视图的结果。优化器的一个任务就是去分析这个独立视图查询块 query block)并产生一个视图子计划 (subplan)，然后优化器在产生整个查询执行计划的同时使 用视图子计划来处理剩余的查询部分。由于视图是被独立在整个查询之外被优化的，因此这 种技术常常会导致一个不

12、良执行计划的产生。查询变换器通过将视图查询块合并到查询块中从而消除这种不良执行计划。绝大多数类 型的视图是可以被合并的。在一个视图被合并后，它原有的视图查询块被包含到查询块中， 也就是说视图查询块不存在了，因此也不再需要产生一个子计划。block)，(view谓词推进4对于那些不能合并的视图，查询变换器能够将相关的谓词从查询块中推进到视图查询块 中。由于被推进的谓词能够用来访问索引或者用于过滤，这个技术通常可以改进那些不能被 合并的视图子计划。非嵌套的子查询和视图一样，子查询也是用一个独立的查询块来代表的。子查询是被嵌套在主查询或其 他子查询之中的，计划产生器在找到一个成本最低的执行计划之前被

13、迫要试验所有可能的计 划。由嵌套子查询产生的限制可以在转换为非嵌套的子查询和连接之后消除，经由查询转换 器过滤之后绝大多数的子查询都会被转换为非嵌套的，然后这些非嵌套的子查询产生独立的 子计划，这些子计划按照一种高效的方式进行排列，从而提高了整个查询计划的执行速度。物化视图的查询重写物化视图就是把一个查询的结果事先固化存储在一个表里，当发现和物化视图一致的查 询语句就将相应的项用物化视图来重写。由于绝大多数的查询结果都事先计算好了，因此这 种技术可以极大的提高查询速度。查询转换器负责查找和用户查询相关的所有物化视图，用 其中的一个或多个来重写查询。利用物化视图来重写查询也是基于成本的，如果不使

14、用物化 视图的成本更低一些，则不会去使用物化视图。评估器评估器会产生下列三个度量值： 选择性( Selectivity ) 基数( Cardinality ) 成本( Cost) 这些值是相互关联的，一个值由其他值导出，评估器的最终目标是评估计划的总体成本。 如果有统计信息可用，评估器使用统计信息来计算这些值，统计信息可以提高其精确度。选择性这里的第一个度量值 选择性，表示所选择的行与行集的比值。所谓行集可以是表、 视图，或者是一个连接或 GROUP BY 操作的中间结果。选择性与查询中的谓词有关，比如Iast_name= Smith或者一个联合谓词 last_name= Smith and

15、job_type=。一个谓词充当着一个过滤器的角色，在行集中过滤了一定量的行，谓词的选择性是一个比值，它表示一 个行集经过谓词的过滤后剩下的行占原有行集的比例。其值在 0.0 和 1.0 之间， 0.0 表示在行 集中没有行被选择； 1.0 表示行集中的所有行都被选择了。如果没有可用的统计信息，评估器为选择性赋予一个内部的缺省值，这个内部缺省值随着谓词的不同而不同。例如：等式谓词(last_name= Smith的内部缺省值低于范围谓词(last_name Smith ；评估器会假定等式谓词返回的行数小于范围谓词。当存在可用的统计信息，评估器将使用统计信息来估算选择性。例如：对于一个等式谓词

16、(last_name= Smith)', 选择性的值是distinet last_name 的倒数即：( 1/count(distinctlast_name)。但是如果在 last_name字段上存在直方图(histogram)，则选择性值为：count(Iast_name) where last_name= Smith / coUntst_name) where last_name is notnull。可见在数据倾斜的字段上应用直方图能够帮助 CBO 进行准确的选择性评估。基数基数就是行集中行的数量。基数分为：基础基数( Base cardinality )：就是基表中的行数。基础

17、基数在表分析期间获得。如 果表没有可用的统计信息，则评估器利用表中区(exte nts)的数量来估算基础基数。有效基数( Effective cardinality )：就是从基表中选择的行数。有效基数与具体的谓 词和字段有关。有效基数是根据基础基数和作用于该表的所有谓词的选择性得出 的，如果没有谓词作用于该表，则有效基数就等于基础基数。连接基数( Join cardinality )：就是两个行集在连接之后产生的行数。连接就是由两 个行集产生的笛卡尔积，再由连接谓词过滤结果。因此，连接基数是两个行集基数 与连接谓词选择性的乘积。Distinct 基数( Distinct cardinalit

18、y )：就是一个行集的字段 distinct 之后的行数。一个 行集的 distinct 基数是基于字段中的数据的。例如：一个拥有 100 行的行集，如果 一个字段 distinct 之后还剩下 20 行，则 distinct 基数就为 20。Group基数(Group cardinality ):就是一个行集在应用GROUP BY之后产生行的数量。 Group 基数依赖于每个组中字段的 distinct 基数和行集的行数。GROUP 基数例子:假如对一个有 100 行的行集 group by colx， colx 字段的 distinct 基数是 30，则 Group 基 数为 30。但是如

19、果 group by colx,coly 呢？ coly 字段的 distinct 基数是 60，这种情况下 Group 基数大于 max(colx distinet 基数，coly distinct 基数)，而小于 min(colx distinet 基数 *coly distinct 基数，行集的行数 )，用公式表示出来如下:group cardinality lies betweenmax ( dist. card. colx , dist. card. coly )andmin ( (dist. card. colx * dist. card. coly) , num rows in

20、row set )对于上面的例子 Group 基数大于 max(30， 60)而小于 min(30*60， 100)，也就是 Group 基数位于 60 和 100 之间。成本成本是用来描述工作单元或资源使用的。 CBO是用磁盘I/O、 CPU 和内存的使用情况来作为工作单元的，因此 CBO 使用的成本可以描述为，在一次操作的执行过程中所用的磁 盘 I/O 数量以及 CPU 和内存的总使用量。这里的操作可以是扫描一张表、通过索引访问表、 连接两个表、或者一个行集的排序。一个查询计划的成本就是运行这个查询并产生结果的同 时需要的工作单元的数量。访问路径(access path决定着在基表中获得数

21、据所需要的工作单元数量。访问路径可以是表扫描(table scan)、快速全索引扫描(fast full index scan )、索引扫描(index scan)等。在表扫描或快速全索引扫描期间，多个块可以在一次 I/O 中获得，因此表扫描或快速全索引 扫描的成本依赖于被扫描的块数和多块读取的数量。索引扫描的成本依赖于 B 树的深度、 被扫描的索引页块数量、和用 ROWID 获取的行数，使用 ROWID 获取行的成本倚赖于索引 聚集因子( clustering factor )。尽管聚集因子是索引的一个属性，它实际也关系到表数据块中被索引的字段值。一较低 的聚集因子表明行被集中在表的少数块里

22、，相反一个较高的聚集因子表明行被随机分散到表 的数据块中。因此，聚集因子过高意味着通过范围扫描用 ROWID 获取行成本会较高，因为 需要访问表中过多的块才能返回数据。聚集因子对成本的影响假设环境如下：一个表有 9 行数据。在 col1 上有一个非唯一索引distinct col1 值是 A 、B、C 这个表占据三个 Oracle 块第一种情况：索引聚集因子低，如下图：Block 1Block 2Block 3A A A B B B C C C索引字段相同的值都在同一个物理块中，这种情况下做范围扫描返回 col1=A 的所有的 行成本就很低，因为只需要在表中读取一个块就可以返回数据。第二种情况

23、：索引聚集因子高，如下图：Block 1Block 2Block 3A B C A B C A B C 索引字段相同的值被分散存储到表中的块，这时要得到 col1=A 的行则要读取三个块。联合单独访问两个表的成本就是连接的成本，在一个连接中分为内行集和外行集。 嵌套循环连接( nested loop join )：对于外行集中的每一行都要在内行集寻找全部与 它匹配的行，然后连接。因此，在嵌套循环连接中外行集有多少行，内行集就被访 问多少次。成本计算公式如下：cost = outer access cost + (inner access cost * outer cardinality) 排序

24、合并连接( sort merge join )：如果两个行集的连接键是无序的，则进行排序。 成本计算公式如下：cost = outer access cost + inner access cost + sort costs (if sort is used) 散列连接( hash join )：内部行集被散列到内存中，并用连接键建立一个散列表， 然后探测外部行集并连接与之匹配的行。如果内部行集非常大，则只会把一部分散 列到内存中，这叫做一个散列分区。此时，内存中的散列分区探测外部行集并连接 所有匹配的行，重复这个过程直到用完内部行集的所有分区。成本计算公式如下： cost = (outer

25、access cost * num of hash partitions) + inner access cost计划生成器由于不同的访问路径、连接方式和连接顺序可以任意组合，以不同的方式访问和处理数 据，但可以产生同样的结果，因此一个SQL可能存在大量不同的计划。计划生成器的主要作用正是为查询试验出所有这些可能存在的计划，并选择一个其中成本最低的。连接顺序就是不同的连接项(如，表)以一定的顺序被访问和连接在一起。例如：有一 个连接按照t1、t2、t3的顺序，贝U t1是第一个被访问的，然后是 t2，访问t2的同时与t1做 连接并产生连接后的结果，最后t3被访问，t3的数据与t1和t2产生的中

26、间结果做连接在建立一个查询的计划之前要先为每个被嵌套的子查询和未合并的视图建立子计划，每 个嵌套的子查询和未合并的视图都是独立的查询块，这些查询块以自底向上的顺序进行优 化，也就是最里层的查询块最先优化并产生子计划，最外层的查询块最后优化。计划生成器通过试验不同的访问路径、连接方式和连接顺序去探测各种计划，对于一个 查询来说可能存在的计划与FROM字句后面的连接项是成比例的，并以指数增长。然而实际上计划生成器很少会试验所有的可能存在的计划，如果它发现当前计划的成本已经很低 了，它将停止试验，相反当前计划的成本如果很高它将继续试验其他计划，因此如果计划生 成器一开始就能够找到一个成本较低的计划则

27、会大量减少时间，计划生成器通常按照连接项 有效基数由小到大的顺序排列初使连接，。理解执行计划Oracle用来运行一个语句的步骤就叫做执行计划(executio n plan)，执行计划包含了语句所涉及的每个表的访问路径和连接顺序。执行计划概述使用EXPLAIN PLAN语句可以查看优化器所选择的执行计划，下面看一个例子：1、创建PLAN_TABLE，用来存放执行计划的描述信息：connect hr/ your_password$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQLTable created.当然你也可以改变 PLAN_TABLE 的名字。Oracle公司建议

28、你在做完数据库版本升级之后删除PLAN_TABLE然后再重建，因为字段可能会有所变化，这可能会导致脚本失效或TKPROF失效。当然你也可以改变 PLAN_TABLE 的名字。2、运行执行计划:EXPLAIN PLAN FORSELECT e.employee_id, j.job_title, e.salary, d.department_name FROM employees e, jobs j, departments d WHERE e.employee_id < 103AND e.job_id = j.job_idAND e.department_id = d.department

29、_id;用下面的语句可以指定 PLAN_TABLE 的名字：EXPLAIN PLANINTO my_plan_tableFORYOUR_SQL;3、显示执行计划信息：这里可以用以下两个脚本UTLXPLS.SQL -显示计划表信息，以串行的方式处理。UTLXPLP.SQL -显示计划表信息，以并行的方式处理。$ORACLE_HOME/rdbms/utlxplp.sql;|0 | SELECT STATEMENT| |3 | 189 | 10(10)| 1 |NESTED LOOPS| |3 | 189 | 10(10)| 2 |NESTED LOOPS| |3 | 141 | 7(15)|* 3

30、 |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 | | Id | Operation| Name| Rows | Bytes | Cost (%CPU)|Pred

31、icate 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 的图形界面显示：12S)SE

32、LECT STATEMENTRowe:_ K&rtes' 0.13S 4) =- = cost?NESTED LOOPSRows19 -KBYlesO.QSS u H <HR.EMPLOYEES9Rows: <KBvte 0.1857)Cost: 10NESTED LOOPS0-RKBytes； 0 1:27KByte EiOdSj Dost: 2HRJOBSW Cost 2HR.DEPARTMENTSTABLE ACCESS (8Y INDEX ROWL.9Row制TABLE ACCESS (FULL) TABLE ACCESS (BY INDEX BUWI.0O

33、QSt： 1HR ,DEPTJD_PKINDEX (UNIQUE SCANOost: 1HR.JOB_ID_PKINDEX (UNIQUE SCAN该图中每个图标左上角的数字就是执行的顺序号，利用图形工具很容易看出执行计划的 执行顺序。但是很多情况下我们没用配置图形工具的环境，而且图形工具消耗资源，也不 太稳定，很多人也不太喜欢用图形工具。如果利用上面运行脚本的方法在sqlplus中获得执行计划，对于新手来说不容易看出执行的顺序，通常我们用下面的方法来获得执行计划：1、conn /as sysdba;2、$ORACLE_HOME/sqlplus/admin/plustrce.sql3、gran

34、t plustrace to public4、$ORACLE_HOME/rdbms/admin/utlxplan.sql5、create public synonym plan_table for plan_table;6、grant all on plan_table to public ;以上步骤只需配置一次即可。7、conn username/password;8、 set autotrace trace only （如果想看至 U结果集：set autotrace on, 关闭：set autotrace off）9、set timing on （如果想同时看到语句执行的时间）10、运

35、行你的SQL 关于如何看执行计划的顺序，请看下面的例子:set autotrace trace only select en ame,d namefrom emp, deptwhere emp.dept no=dept.dept noand dept.d name in ('ACCOUNTING','RESEARCH','SALES','OPERATIONS');15 rows selected.Execution PlanSELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=8 Bytes

36、=248)0 HASH JOIN (Cost=3 Card=8 Bytes=248)TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=3 Bytes=36)TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=16 Bytes=304)用这种方法产生的执行计划会有两列数字，第一列是statement_id，第二列是child_id。我们可以从 statement_id=0 处开始读，当该句有子句则先执行子句，该句就为其子句的父句； 如果该句有多个子句，则子句的执行顺序是从上到下；子句执行完执行父句

37、。在这个例子中，stateme nt_id=O有一个子句 stateme nt_id=1，因此在执行 stateme nt_id=O 之前要执行 statement_id=1，但 statement_id=1有两个子句分别为statement_id=2和3,因此这两句要最先执行。此执行计划的执行顺序为：2 >3 >1 >0后面的 cost 说明该 SQL 是基于成本优化的,如果没有 cost 则是基于规则的。通过这个例子可以知道学会看执行计划也不是什么难事,关键是要理解执行计划,学会 如何优化执行计划,下面将继续讨论。理解 CBO 访问路径访问路径就是从数据库中检索数据的方式

38、。通常来说,检索一个表中少量的数据行应该 使用索引访问,但是检索大量数据时全表扫描可能优于索引。全表扫描( Full Table Scans)全表扫描将读取 HWM 之下的所有数据块,访问表中的所有行,每一行都要经 WHERE 子句判断是否满足检索条件。当Oracle 执行全表扫描时会按顺序读取每个块且只读一次,因此如果能够一次读取多个数据块, 可以提高扫描效率, 初始化参数 DB_FILE_MULTIBLOCK_READ_COUNT 用来设置在一次 I/O 中可以读取数据块的最大数优化器何时会使用全表扫描在以下情况中优化器会使用全表扫描：1、无可用索引 如下面例子：SELECT last_n

39、ame, first_nameFROM employeesWHERE UPPER(last_name)=TOMIast_ name字段有索引，但在查询中使用了函数，因此该查询不会使用索引。如果想让这 个查 询走 索引 ，则 需要 建立 函数 索引 create index ind_upper_lastname on last_name (upper(Iast_name) 。特别要注意的是隐式转换，比如 coIx 字段是 varchar2 型但存放数字： where colx=123456，这时会发生隐式转换TO_NUMBER(colx)，此时colx上的索引也会失效。2、大量数据如果优化器认为

40、查询将会访问表中绝大多数的数据块，此时就算索引是可用的也会使用 全表扫描。3、小表如果一个表 HWM 之下的数据块比 DB_FILE_MULTIBLOCK_READ_COUNT 要少，只 需要一次 I/O 就能扫完，则使用全表扫描要比使用索引的成本低，此时会使用全表扫描。如果有这样小表访问频率又高，通常把它固定在内存中为好alter table table_namestorage(buffer_pool keep)。4、并行如果在表一级设置了较高的并行度，如 alter table table_name parallel(degree 10)，通常会 使 CBO 错误的选择全表扫描。通常不建议

41、在表级的设置并行。并行查询通常可以提高全表扫描的性能，建议在语句级用 HINTS 来实现并行，如 /*+full(table_name) parallel(table_name degree)*/ 。5、全表扫描 hints 如果想强制优化器使用全表扫描可以用提示 FULL 。I/O 是针对数据块的而不是行Oracle 的 I/O 是针对数据块的，因此被访问的数据块所占的百分比将影响 CBO 是否选 择全表扫描。通常一个数据块中存储着多条记录，被请求的记录要么聚集在少数几个块中， 要么分散在大量的数据块中。HWM(High Water Mark)HWM 是全表扫描范围的标记，每个全表扫描都要读

42、到 HWM 位置。当表 analyze 之后 可以在 DBA_TABLES.BLOCKS 查到 HWM ，当表被 drop、 truncate 或者 move 之后， HWM 将会被重置。需要注意的是，当一个表被大量删除记录之后，HWM 下面的大量数据块是空的，此时若对此表进行全表扫描， Oracle 仍然会读到 HWM 位置，会对全表扫描的性能产生 极坏的影响。Rowid 扫描Rowid 就是一个记录在数据块中的位置，由于指定了记录在数据库中的精确位置，因此 rowid 是检索单条记录的最快方式。如果通过 rowid 来访问表， Oracle 首先需要获得被检索记录的rowid， Oracl

43、e 可以在WHERE 子句中得到 rowid ，但更多的是通过索引扫描来获得，然后 Oracle 基于 rowid 来定位被检索的每条记录。优化器何时使用 Rowid并不是每个索引扫描都伴随着rowid的访问，如果索引中包含了被访问的所有字段，则不再需要通过rowid来访问表。注意:Rowid是Oracle表示数据存储的内部方法，它可能会由于版本的改变而改变。不推荐通过在WHERE中指定rowid来访问数据，因为行迁移和行链接会导致rowid变化，exp/imp也会使rowid变化。索引扫描索引不仅包含被索引字段的值，还包含表中行的位置标识rowid，如果语句只检索索引字段，Oracle直接从

44、索引中读取该值而不去访问表，如果语句通过索引检索其他字段值，则 Oracle通过rowid访问表中记录。索引扫描类型：索引唯一扫描(Index Unique Scans) 索引范围扫描(Index Range Scans 索引降序范围扫描(In dex Range Scans Desce ndi ng) 索引跳跃扫描(Index Skip Scans) 全索引扫描(Full Scans)快速全索引扫描(Fast Full Index Scans)索引连接(In dex Joi ns) 位图连接(Bitmap Joi ns)1、索引唯一扫描这种扫描通常发生在对一个主键字段或含有唯一约束的字段指定

45、相等条件时，只有单行 记录被访问。2、索引范围扫描索引范围扫描是检索数据的常用方式，返回的数据返照索引字段升序排列，字段值相同 的则按照rowid升序排列。如果在语句中指定了order by字句，而且排序字段是索引字段时Oracle将忽略order by子句。例如：SQL> select * from t;COLXCOLY11141 0 SQL> create index ind_t on t(coly);SQL> set autotrace on SQL> select * from t where coly>0;COLX COLY111213Execution

46、 Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'2 1INDEX (RANGE SCAN) OF 'IND_T' (NON-UNIQUE)没有使用 order by 结果集已经是按 coly 升序排列的。SQL> set autotrace traceonlySQL> select * from t where coly>0 order by coly;Execution PlanSELECT STATEMENT Optimizer=

47、CHOOSE0 TABLE ACCESS (BY INDEX ROWID) OF 'T'可以看到执行计划中无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;C

48、OLX COLYExecution Plan0 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、索引跳跃式扫描跳跃式扫描发生在复合索引中，它在逻辑上将索引分离为较小的子索引，当复合索引的in dex_ss某一个字

49、段不在查询中指定时，它将被跳过，从而提高索引扫描的效率。可以使用 提示强制使用跳跃扫描。举个例子：SQL> select* from employees;SEXEMPLOYEE。ADDRESS16F98ABCF100ABCF102ABCF104ABCM101ABCM103ABCM105ABCSQL> create in dex in d_sex_empid on employees(sex,employee_id); 索引结构如下图所示：Lev&l 1Lev&l 2SQL>set autotrace trace onlySQL>select/*+in d

50、ex_ss(employees in d_sex_empid)*/* from employees where employee_id=101;Executi on Pla n0SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=11)(Cost=3(Cost=21 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' Card=1Bytes=11)2 1INDEX (SKIP SCAN) OF 'IND_SEX_EMPID' (NON-UNIQUE)Card=1)

51、5、全索引扫描如果要使用全索引扫描必须满足两个条件，一是查询涉及的字段都包含在索引中，二是 至少一个索引字段具有非空属性。由于索引键的数据是有序的，因此全索引扫描可以用消除 排序操作。全索引扫描只需要一次 I/O。select emp no,en ame from big_emp order by emp no,en ame;Executi on Pla n0 SELECT STATEMENTOptimizer=CHOOSE (Cost=3 Card=1 Bytes=11)10 INDEX (FULL SCAN) OF ?BE_IX (Cost=2 Card=1)6、快速全索引扫描 快速全索引

52、扫描只访问索引本身，而不去访问表，因此只有查询涉及的字段都包含在索 引中时才会使用快速全索引扫描。如果想使用快速全索引扫描查询所涉及的字段必须全部包 含在索引中，而且索引中至少有一个字段具有非空属性。满足条件后可以使用 index_ffs 提 示来强制使用快速全索引扫描，快速全索引扫描只适用于CBO。快速全索引扫描并不能消除排序操作，因为索引键中的数据没有被排序。不同于全索引 扫描，快速全索引扫描是通过多块读取的方式来读取整个索引的，并可以设置并行方式。7、索引连接 只有查询涉及的所有字段都包含在索引中，才会使用索引连接，此时只通过访问索引就 能获得所有需要的数据，而不用访问表。索引连接只适用

53、于CBO ，且不能消除排序操作。可以通过 index_join 提示来强制使用索引连接。8、位图连接 位图连接使用一个位图作为键，然后通过映射函数将比特位转换为rowid 。只有 Oracle9i企业版才支持位图索引和位图索引连接。Sample Table ScansSample table scan是随机检索表中的数据，当 FROM 后面有 SAMPLE或SAMPLEBLOCK 子句时，会执行 Sample table scan。如： SELECT * FROM employees SAMPLE BLOCK (1);CBO 如何选择访问路径CBO 首先检查 WHERE 子句中的条件以及 FR

54、OM 子句，确定有哪些访问路径是可用的。 然后 CBO 使用这个访问路径产生一组可能的执行计划，再通过索引、表的统计信息评估每 个计划的成本，最后优化器选择成本最低的一个。例 1 ：SELECT *FROM employeesWHERE last_name = 'JACKSON'如果 last_name 具有唯一约束或者主键约束，优化器了解到只有一行数据被返回，这种 情况下查询具有很强的选择性，优化很可能走唯一索引扫描。例 2 ：还是 上 面 的语句 ， 如 果 last_name 不具 有唯 一 约束或主 键 约束，优化 器使用USER_TAB_COLUMNS.NUM_DIS

55、TINCT 和 USER_TABLES.NUM_ROWS 的统计信息来评 估查询的选择性，估算 last_name 为 jackson 的记录占了 employees 表的比例。例 3 ：SELECT *FROM employeesWHERE employee_id < 7500;评估这个查询的选择性时优化器使用 WHERE 子句中的边界值 7500 和 employee_id 字 段的 USER_TAB_COLUMNS.HIGH_VALUE、 USER_TAB_COLUMNS.LOW_VALUE，优化器假定在最小值和最大值之间 employee_id 是平均分布的，优化器确定值小于 7

56、500 的百分 比，然后把这个值作为这个查询的选择性。例 4 ： SELECT *FROM employeesWHERE employee_id < :e1; 优化器并不知道 e1 的值，绑定变量的值每次运行都可能不同，因此优化器不能使用前 面的方法来评估含有绑定变量的查询的选择性，在这种情况下优化器会使用内部缺省值试探 着估算一个选择性。例 5 ： SELECT *FROM employeesWHERE employee_id BETWEEN :low_e AND :high_e; 优化器会将这句改写为： employee_id >= :low_e employee_id <

57、;= :high_e 然后优化器仍然是用内部缺省值来试探着为其评估一个选择性。例 6 ： SELECT *FROM employeesWHERE employee_id BETWEEN 7500 AND 7800; 优化器会改写为： employee_id >= 7500 employee_id <= 7800优化器为每个条件独立的评估选择性（S1和S2）,然后用下列公式计算BETWEEN 的选择性： S=ABS（ S1+S2-1）理解连接CBO 如何运行连接语句为一个连接语句选择一个执行计划,优化器必须做出下列相关决策：1、访问路径 优化器必须给连接语句中的每个表选择一个可用来检索数据的路径。2、连接方法Oracle 必须为每对行源执行连接操作，连接的