选择合适的优化器目标

默认情况下，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
不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞吐量为优化目标。

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做最快响应的优化

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）

如下图所示：

查询变换器

被解析器解析过的查询语句进入查询变换器，表现出来的是一组查询块(query block)，这些查询块之间是相互关联的或者是嵌套的，查询的形式决定这些查询块相互之间如何被关联。查询变换器的主要目的就是决定改变查询的形式是否有利于产生一个好的执行计划。查询变换器使用四种不同的查询变换技术：

l 视图合并（View Merging）

l 谓词推进（Predicate Pushing）

l 非嵌套子查询（Subquery Unnesting）

l 物化视图的查询重写（Query Rewrite with Materialized Views）

最终应用于查询的也可以是以上四种变换技术的任意组合。

视图合并

查询中的每个视图都会被解析器扩展到一个独立的查询块中，这个查询块本质上是用来描述视图定义的，是视图的结果。优化器的一个任务就是去分析这个独立视图查询块(view query block)并产生一个视图子计划(subplan)，然后优化器在产生整个查询执行计划的同时使用视图子计划来处理剩余的查询部分。由于视图是被独立在整个查询之外被优化的，因此这种技术常常会导致一个不良执行计划的产生。

查询变换器通过将视图查询块合并到查询块中从而消除这种不良执行计划。绝大多数类型的视图是可以被合并的。在一个视图被合并后，它原有的视图查询块被包含到查询块中，也就是说视图查询块不存在了，因此也不再需要产生一个子计划。

谓词推进

对于那些不能合并的视图，查询变换器能够将相关的谓词从查询块中推进到视图查询块中。由于被推进的谓词能够用来访问索引或者用于过滤，这个技术通常可以改进那些不能被合并的视图子计划。

非嵌套的子查询

和视图一样，子查询也是用一个独立的查询块来代表的。子查询是被嵌套在主查询或其他子查询之中的，计划产生器在找到一个成本最低的执行计划之前被迫要试验所有可能的计划。由嵌套子查询产生的限制可以在转换为非嵌套的子查询和连接之后消除，经由查询转换器过滤之后绝大多数的子查询都会被转换为非嵌套的，然后这些非嵌套的子查询产生独立的子计划，这些子计划按照一种高效的方式进行排列，从而提高了整个查询计划的执行速度。

物化视图的查询重写

物化视图就是把一个查询的结果事先固化存储在一个表里，当发现和物化视图一致的查询语句就将相应的项用物化视图来重写。由于绝大多数的查询结果都事先计算好了，因此这种技术可以极大的提高查询速度。查询转换器负责查找和用户查询相关的所有物化视图，用其中的一个或多个来重写查询。利用物化视图来重写查询也是基于成本的，如果不使用物化视图的成本更低一些，则不会去使用物化视图。

评估器

评估器会产生下列三个度量值：

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（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获取行的成本倚赖于索引聚集因子（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

计划生成器

由于不同的访问路径、连接方式和连接顺序可以任意组合，以不同的方式访问和处理数据，但可以产生同样的结果，因此一个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、运行执行计划：

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的图形界面显示：

该图中每个图标左上角的数字就是执行的顺序号，利用图形工具很容易看出执行计划的执行顺序。 但是很多情况下我们没用配置图形工具的环境，而且图形工具消耗资源，也不太稳定，很多人也不太喜欢用图形工具。如果利用上面运行脚本的方法在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');

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则是基于规则的。

通过这个例子可以知道学会看执行计划也不是什么难事，关键是要理解执行计划，学会如何优化执行计划，下面将继续讨论。

Related posts:

1. Oracle9i优化器介绍（下）
2. sqlplus下如何看执行计划
3. Oracle Trace Level Event Numbers
4. 模糊查询like '%XX%' 的优化一例
5. oralce10g shrink