Oracle9i 优化器介绍
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）
最终应用于查询的也可以是以上四种变换技术的任意组合。
视图合并
查询中的每个视图都会被解析器扩展到一个独立的查询块中，这个查询块本质上是用来
描述视图定义的，是视图的结果。优化器的一个任务就是去分析这个独立视图查询块(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、 访问路径
优化器必须给连接语句中的每个表选择一个可用来检索数据的路径。
2、连接方法
18
Oracle必须为每对行源执行连接操作，连接的方法包括嵌套循环、排序合并、散列连接、
笛卡尔积等。
3、连接顺序
如果需要连接的表多于两个，Oracle先连接其中两个表然后将其连接的结果与下一个表
做连接，直到所有的表都被连接。
CBO 如何选择连接方法
优化器评估每个连接方法的成本，然后选择成本最低的一个。如果一个返回多行的连接，
优化器将考虑如下三个因素：
l 当返回大量的结果集（大于1 万行），嵌套循环连接是效率很低的，优化器可能不会选
择它。嵌套循环的成本主要在把外表中所有被选择的行与内表匹配的过程，CBO 的连
接顺序可以用ORDERED 提示来改变。嵌套循环连接的成本计算公式：
cost = access cost of A + (access cost of B * number of rows from A)
l 如果你使用CBO，当返回大量结果集时使用散列连接效率是非常高的。散列连接的成
本计算公式：
cost = (access cost of A * number of hash partitions of B) + access cost of B
l 如果你使用RBO，当返回大量结果集时排序合并连接的效率比较高。排序合并连接的
成本主要在于把所有行源读到内存中，进行排序的过程，多块读取对排序合并连接会有
所帮助。排序合并连接的成本计算公式：
cost = access cost of A + access cost of B + (sort cost of A + sort cost of B)
当数据是预先排序的，后面两个排序成本为0。
CBO 如何运行Anti-joins
SELECT * FROM employees
WHERE department_id NOT IN
(SELECT department_id FROM departments
WHERE location_id = 1700);
优化器缺省是用嵌套循环来处理anti-joins的，但是如果使用了MERGE_AJ、HASH_AJ、
NL_AJ 提示，NOT IN 能够被转换为一个排序合并或hash anti-join。
CBO 如何运行Semi-joins
SELECT * FROM departments
WHERE EXISTS
(SELECT * FROM employees
WHERE departments.department_id = employees.department_id
AND employees.salary > 2500);
优化器缺省也是用嵌套循环来执行EXISTS 的，也同样可以通过MERGE_SJ、HASH_SJ、
NL_SJ 提示来调整。通常建议把NOT IN 用EXISTS 来改写，但是当NOT IN 的子查询中包含
OR 分支时不能转为EXISTS。
19
嵌套循环连接
对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内
表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行，因此整个查询返回
的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（CBO 默认外表是
驱动表），而且在内表的连接字段上一定要有索引。当然也可以用ORDERED 提示来改变CBO
默认的驱动表，使用USE_NL(table_name1 table_name2)可是强制CBO 执行嵌套循环连接。
散列连接
散列连接是CBO 做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据
源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配
的行。
这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成
本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的
分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量
提高I/O 的性能。
也可以用USE_HASH(table_name1 table_name2)提示来强制使用散列连接。如果使用散
列连接HASH_AREA_SIZE 初始化参数必须足够的大，如果是9i，Oracle建议使用SQL工作区
自动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整PGA_AGGREGATE_TARGET 即可。
排序合并连接
通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执
行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。可以使用
USE_MERGE(table_name1 table_name2)来强制使用排序合并连接。
以下情况Oracle 可能会选择使用排序合并连接：
l 两个表做非等值连接
l OPTIMIZER_MODE 被设置成RULE
l HASH_JOIN_ENABLE 设置成FALSE
l 已经事先排过序，优化器认为使用排序合并连接的成本要比散列连接低。
l HASH_AREA_SIZE 和SORT_AREA_SIZE 设置太小，优化器认为散列连接成本过高。
外连接
不论是嵌套循环外连接还是散列外连接，CBO不会根据成本去选择连接顺序，被驱动的
表总是含有（+）的一方。
SQL> select /*+ordered use_nl(t1 t2)*/ t1.msisdn,t2.msisdn from t1,t2 where
t1.msisdn(+)=t2.msisdn;
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=100 Card=99 Bytes=2178)
1 0 NESTED LOOPS (OUTER) (Cost=100 Card=99 Bytes=2178)
2 1 INDEX (FULL SCAN) OF ‘IND_T2′ (NON-UNIQUE) (Cost=1 Card=99
20
Bytes=1089)
3 1 INDEX (RANGE SCAN) OF ‘IND_T1′ (NON-UNIQUE) (Cost=1 Card=1 Bytes=11)
虽然使用了ORDERED 提示，试图以t1 作为驱动表，可是由于是外连接，仍然是以t2
作为驱动表。
换成散列连接也是一样：
SQL> select /*+ordered use_hash(t1 t2)*/ t1.msisdn,t2.msisdn from t1,t2 where
t1.msisdn(+)=t2.msisdn;
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=99 Bytes=2178)
1 0 HASH JOIN (OUTER) (Cost=4 Card=99 Bytes=2178)
2 1 INDEX (FULL SCAN) OF ‘IND_T2′ (NON-UNIQUE) (Cost=1 Card=99
Bytes=1089)
3 1 INDEX (FAST FULL SCAN) OF ‘IND_T1′ (NON-UNIQUE) (Cost=2 Card=999
Bytes=10989)
此时，外表为t1，内表为t2，连接保留t1 表与t2 不匹配的行，然后用t1 构建散列表，
最后由t2 表去探测t1 生成的散列表。
Full Outer Joins
select t1.msisdn,t2.msisdn
from t1
full outer join t2
on t1.msisdn=t2.msisdn
order by t2.msisdn
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1000 Bytes=36000)
1 0 SORT (ORDER BY) (Cost=15 Card=1000 Bytes=36000)
2 1 VIEW (Cost=6 Card=1000 Bytes=36000)
3 2 UNION-ALL
4 3 NESTED LOOPS (OUTER) (Cost=2 Card=999 Bytes=21978)
5 4 INDEX (FAST FULL SCAN) OF ‘IND_T1′ (NON-UNIQUE) (Cost=2
Card=999 Bytes=10989)
6 4 INDEX (RANGE SCAN) OF ‘IND_T2′ (NON-UNIQUE)
7 3 HASH JOIN (ANTI) (Cost=4 Card=1 Bytes=22)
8 7 INDEX (FULL SCAN) OF ‘IND_T2′ (NON-UNIQUE) (Cost=1 Card=99
Bytes=1089)
9 7 INDEX (FAST FULL SCAN) OF ‘IND_T1′ (NON-UNIQUE) (Cost=2
Card=999 Bytes=10989)
从执行计划看，实际上就是做了两个外连接，一个是t1.msisdn=t2.msisdn(+)走嵌套
循环，一个是t1.msisdn(+)=t2.msisdn 走散列连接，然后再UNION-ALL 两个行集。
21
结果像下面这样：
T1.MSISDN T2.MSISDN
————- ———–
10 10
20 201
20 202
30 114
30 115
30 116
…
270
280
178
207
一些和优化器相关的初始化参数
1、OPTIMIZER_FEATURES_ENABLE
每个版本的Oracle 优化器特性都不相同，特别是做了版本升级以后一定要修改这个参
数才可以使用仅被该版本支持的优化器特性。
可以赋予它的值如：9.2.0、9.0.2、9.0.1、8.1.7、8.1.6 等。
2、CURSOR_SHARING
这个参数会将SQL 语句中的常量用变量来替换，存在大量常量的OLTP 系统可以考虑启
用这个参数。但是有一点要明白，绑定变量虽然可以使大量的SQL 重用，减少分析时间，但
是当数据分布发生变化后，CBO 为绑定变量SQL 生成的执行计划可能不是最优（不会考虑具
体的变量值）。通常OLTP系统适用于绑定变量，OLTP系统特点是，SQL 运行频繁且时间相对
较短，SQL 的分析时间比重较大。如果在DSS 系统中，SQL 运行时间长，相比之下分析时间
微不足道，好的执行计划才是最重要的，因此DSS 系统绑定变量要另行考虑。
3、HASH_AREA_SIZE
这是散列连接时散列表的存放区域，如果使用散列连接这个参数值不能过小，如果散列
表不能完全存放于内存中，对散列连接性能的影响很大。如果是9i 建议启动工作区自动管
理，然后设置PGA_AGGREGATE_TARGET。
4、SORT_AREA_SIZE
内存排序区的大小，如果排序时内存区不够会写如磁盘。9i 同样建议启动工作区自动
管理，然后设置PGA_AGGREGATE_TARGET。
5、HASH_JOIN_ENABLED
如果启用这个参数，CBO 在考虑连接方法的时候将会考虑散列连接。
22
6、OPTIMIZER_INDEX_CACHING
这个参数表示被缓存的索引块所占百分比，可选值的范围是0-100。这个值会影响嵌套
循环连接，如果这个值设得较高，CBO 将更倾向使用嵌套循环。
7、OPTIMIZER_INDEX_COST_ADJ
优化器利用这个参数（是个百分比）把索引扫描的成本转换为等价的全表扫描的成本，
然后与全表扫描的成本进行比较。缺省值100，表示索引扫描成本与全表扫描成本等价。可
选值范围是0-10000。
8、OPTIMIZER_MAX_PERMUTATIONS
这个初始参数用来设定优化器最多考虑多少种连接顺序，优化器不断的产生可能的表的
连接的排列，直到排列数达到参数optimizer_max_permutations 为止。一旦优化器停止产
生新的可能连接排列，它将会从中选择出成本最小的排列。
9、DB_FILE_MULTIBLOCK_READ_COUNT
这个参数表示在全表扫描或索引快速全扫描时一次I/O 读的连续数据块数量（block#
连续，且一次I/O 不能超过extent）。
10、OPTIMIZER_MODE
优化器模式，也是优化器的优化目标。值为：RULE、CHOOSE、ALL_ROWS、FIRST_ROWS_n、
FIRST_ROWS。
11、PARTITION_VIEW_ENABLED
如果设置为TRUE, 该优化器将跳过分区视图中未被请求的分区，该参数还能更改基于
成本的优化程序从基础表统计信息计算分区视图统计信息的方式。
12、QUERY_REWRITE_ENABLE
如果设置为TRUE，优化器将利用可用的物化视图来重写SQL。
