Oracle优化器

红太狼 2022-07-16 11:14 207阅读 0赞

本文转自 http://www.cnblogs.com/dongzhiquan/archive/2012/01/20/2328365.html

Oracle优化器介绍

本文讲述了Oracle优化器的概念、工作原理和使用方法，兼顾了Oracle8i、9i以及最新的10g三个版本。理解本文将有助于您更好的更有效的进行SQL优化工作。

RBO优化器

RBO是一种基于规则的优化器，随着CBO优化器的逐步发展和完善，在最新的10g版本中Oracle已经彻底废除了RBO。正在使用Oracle8i或9i的人们或多或少的都会碰到RBO，因此在详细介绍CBO之前，我们有必要简单回顾一下古老的RBO优化器。

在RBO中Oracle根据可用的访问路径和访问路径的等级来选择执行计划，等级越高的访问路径通常运行SQL越慢，如果一个语句有多个路径可走，Oracle总是选择等级较低的访问路径。

RBO访问路径

1级：用Rowid定位单行

当WHERE子句中直接嵌入Rowid时，RBO走此路径。Oracle不推荐直接引用Rowid，Rowid可能会由于版本的改变而变化，行迁移、行链接、EXP/IMP也会使Rowid发生变化。

2级：用Cluster Join定位单行

两个表做等值连接，一方的连接字段是Cluster Key，且WHERE中存在可以保证该语句仅返回一行记录的条件时，RBO走此路径。

3级：用带用唯一约束或做主键的Hash Cluster Key定位单行

4级：用唯一约束的字段或做主键的字段来定位单行

5级：Cluster Join

6级：使用Hash Cluster Key

7级：使用索引Cluster Key

8级：使用复合索引

9级：使用单字段索引

10级：用索引进行有界限范围的查找

如，column >\[=\] expr AND column <\[=\] expr或column BETWEEN expr AND expr

或column LIKE ‘c%’

11级：用索引字段进行无界限的查找

如，WHERE column >\[=\] expr 或 WHERE column <\[=\] expr

12级：排序合并连接

13级：对索引字段使用MAX或MIN函数

14级：ORDER BY索引字段

15级：全表扫描

如果可以使用索引RBO会尽可能的去用索引而不是全表扫描，但是在下列一些情况RBO只能使用全表扫描：

如果column1和column2是同一个表的字段，含有条件column1 < column2或column1 > column2或column1 <= column2或column1 >= column2，RBO会用全表扫描。

如果使用column IS NULL或column IS NOT NULL或column NOT IN或column != expr或column LIKE ‘%ABC’时，不论column有无索引，RBO都使用全表扫描。

如果expr = expr2，expr表达式作用了一个字段上，无论该字段有无索引，RBO都会全表扫描。

NOT EXISTS子查询以及在视图中使用ROWNUM也会造成RBO进行全表扫描。

以上就是RBO的全部可用访问路径。RBO优化器死板的根据规则来选择执行计划显然不够灵活，在RBO中也无法使用物化视图等Oracle提供的新特性，在Oracle8i时CBO已经基本成熟，因此Oracle强烈建议改用CBO优化器。下文将全面介绍CBO优化器。

CBO优化器结构

CBO是基于成本的优化器，它根据可用的访问路径、对象的统计信息、嵌入的Hints来选择一个成本最低的执行计划。

CBO主要包含以下组件：

*  l 查询转换器（Query Transformer）
 *  l 评估器（Estimator）
 *  l 计划生成器（Plan Generator）

如下图所示：

[![image][]][image 1]

查询转换器

查询语句的形式会影响所产生的执行计划，查询转换器的作用就是改变查询语句的形式以产生较好的执行计划。

从Oracle 8i开始就有四种转换技术：视图合并（View Merging）、谓词推进（Predicate Pushing）、非嵌套子查询（Subquery Unnesting）和物化视图的查询重写（Query Rewrite with Materialized Views）。

视图合并：如果SQL语句中含有视图，经分析后会把视图放在独立的“视图查询块”中，每个视图会产生一个视图子计划，当为整个语句产生执行计划时，视图子计划会被直接拿来使用而不会照顾到语句的整体性，这样就很容易导致不良执行计划的生成。视图合并就是为了去掉“视图查询块”，将视图合并到一个整体的查询块中，这样就不会有视图子计划产生，执行计划的优良性得到提升。

谓词推进：不是所有的视图都能够被合并，对于那些不能被合并的视图Oracle会将相应的谓词推进到视图查询块中，这些谓词通常是可索引的或者是过滤性较强的。

非嵌套子查询：子查询和视图一样也是被放于独立查询块中的，查询转换器会将绝大多数子查询转换为连接从而合并为同一查询块，少量不能被转换为连接的子查询，会将它们的子计划安照一个高效的方式排列。

物化视图的查询重写：当query\_rewrite\_enabled=true时，查询转换器寻找与该查询语句相关联的物化视图，并用物化视图改写该查询语句。

关于“窥视”（Peeking）:

在Oracle9i中为查询转换器增加了一个功能，就是当用户使用绑定变量时，查询转换器可以“偷窥”绑定变量的实际值。

我们知道使用绑定变量虽然可以有效的减少“硬分析”，但它带来的负面影响是优化器无法根据实际的数据分布来优化SQL，很有可能本可以走索引的SQL却做了全表扫描。“窥视”正是为了解决这个问题，但是它并没有彻底的解决，Oracle只允许第一次调用时进行“窥视”，接下来的调用即使绑定变量的值发生了变化，也仍然是使用第一次生成的执行计划，这就造成了一个错误的执行计划会被多次使用，10g中的“窥视”也是如此。

评估器

评估器通过计算三个值来评估计划的总体成本：选择性（Selectivity）、基数（Cardinality）、成本（Cost）。

选择性：是一个大于0小于1的数，0表示没有记录被选定，1表示所有记录都被选定。统计信息和直方图关系到选择性值的准确性。如：name=’Davis’，如果不存在统计信息评估器将根据所用的谓词来指定一个缺省的选择性值，此时评估器会始终认为等式谓词的选择性比不等式谓词小；如果存在统计信息而不存在直方图，此时选择性值为1/count(distinct name)；如果存在统计信息也存在直方图，选择性值则为count（name）where name=’Davis’ / count（name）where name is not null。

基数：通常表中的行数称为“基础基数”（Base cardinality）；当用WHERE中的条件过滤后剩下的行数称为“有效基数”（Effective cardinality）；连接操作之后产生的结果集行数称为“连接基数”（Join cardinality）；一个字段DISTINCT之后的行数称为“DISTINCT基数”；“GROUP基数”（Group cardinality）比较特殊，它与基础基数和DISTINCT基数有关，例如：group by colx则GROUP基数就等于基础基数，但是group by colx,coly的GROUP基数则大于max ( distinct cardinality of colx , distinct cardinality of coly )且小于min ( (distinct cardinality of colx \* distinct cardinality of coly) , base cardinality)。

成本：就是度量资源消耗的单位。可以理解为执行表扫描、索引扫描、连接、排序等操作所消耗I/O、CPU、内存的数量。

计划生成器

计划生成器的作用就是生成大量的执行计划，然后选择其中总体成本最低的一个。

由于不同的访问路径、连接方式和连接顺序可以任意组合，虽然以不同的方式访问和处理数据，但是可以产生同样的结果，因此一个SQL可能存在大量不同的执行计划。但实际上计划生成器很少会试验所有的可能存在的执行计划，如果它发现当前执行计划的成本已经很低了，它将停止试验，相反当前计划的成本如果很高，它将继续试验其他执行计划，因此如果能使计划生成器一开始就找到成本很低的执行计划，则会大量减少所消耗的时间，这也正是我们为什么用HINTS来优化SQL的原因之一。

优化器模式及优化目标

除了上述的CBO优化器外，Oracle还有一种基于规则的RBO优化器，在8i以后Oracle就不再发展RBO了，有很多新特性在RBO中也不被支持，在最新的10g中RBO已被彻底废除。在10g前RBO与CBO共存，用户可以通过设置初始化参数OPTIMIZER\_MODE来决定到底使用哪个优化器，也可以用ALTER SESSION来改变当前SESSION中OPTIMIZER\_MODE的值。除此之外在SQL中嵌入HINTS可以指定具体某个SQL使用哪个优化器。

CBO虽然是基于成本的优化器，但仍然允许以“时间”或者说“响应速度”为优化目标，通过设置OPTIMIZER\_MODE或者对具体语句嵌入HINTS都可以指定优化目标。

OPTIMIZER\_MODE选项如下：

*  l ALL\_ROWS
 *  l FIRST\_ROWS\_n
 *  l FIRST\_ROWS
 *  l CHOOSE
 *  l RULE

##### CHOOSE #####

仅在9i及之前版本中被支持，10g已经废除。8i及9i中为默认值。

这个值表示SQL语句既可以使用RBO优化器也可以使用CBO优化器，而决定该SQL到底使用哪个优化器的唯一因素是，所访问的对象是否存在统计信息。如果所访问的全部对象都存在统计信息，则使用CBO优化器优化SQL；如果只有部分对象存在统计信息，也仍然使用CBO优化器优化SQL，优化器会为不存在统计信息对象依据一些内在信息（如分配给该对象的数据块）来生成统计信息，只是这样生成的统计信息可能不准确，而导致产生不理想的执行计划；如果全部对象都无统计信息，则使用RBO来优化该SQL语句。

##### RULE #####

仅在9i及之前版本中被支持，10g已经废除。

不论是否存在统计信息，都将使用RBO优化器来优化SQL。

##### ALL\_ROWS #####

在10g中为默认值。

不论是否存在统计信息，都使用CBO优化器，且把CBO的优化目标设定为“最小的成本”。

##### FIRST\_ROWS #####

CBO尽可能快速的返回结果集的前面少数行记录。

不论是否存在统计信息，都使用CBO优化器，FIRST\_ROWS导致CBO使用“试探法”来产生执行计划，这种方式其成本可能会稍大一些。

##### FIRST\_ROWS\_n #####

不论是否存在统计信息，都使用CBO优化器，并以最快的速度返回前n行记录，n可以是1,10,100,1000。

影响优化器模式及目标的HINTS：

*  l RULE：意义同OPTIMIZER\_MODE=RULE区别在于HINTS作用在语句级，10g中该HINTS已被废弃。
 *  l CHOOSE：意义同OPTIMIZER\_MODE=CHOOSE，10g中已被废弃。
 *  l FIRST\_ROWS：意义同OPTIMIZER\_MODE=FIRST\_ROWS，10g中已被废弃。
 *  l ALL\_ROWS：意义同OPTIMIZER\_MODE=ALL\_ROWS。
 *  l FIRST\_ROWS(n)：意义同OPTIMIZER\_MODE=FIRST\_ROWS\_n。
 *  l CPU\_COSTING：启用CPU成本计算，也就是在总成本中考虑CPU的成本，缺省是启用的。该HINTS是10g中新增加的。
 *  l NO\_CPU\_COSTING：关闭CPU成本计算，也就是在总成本中不考虑CPU的成本，只计算I/O的成本。该HINTS也是10g中新增加的。

访问路径

访问路径就是从数据库里检索数据的方式。优化器首先检查WHERE子句和FROM子句的条件，确定有哪些访问路径是可用的。然后优化器使用这些访问路径或各访问路径的联合，产生一组可能存在的执行计划，再通过索引、字段、表的统计信息评估每个计划的成本，最后优化器选择成本最低的执行计划所对应的访问路径。

如果SQL语句的FROM子句无SAMPLE或SAMPLE BLOCK，优化器在选择访问路径的时候会优先考虑语句中的HINTS。

优化器可用的访问路径如下：

*  l 全表扫描（Full Table Scans）
 *  l Rowid扫描（Rowid Scans）
 *  l 索引扫描（Index Scans）
 *  l 簇扫描（Cluster Scans）
 *  l 散列扫描（Hash Scans）
 *  l 表取样扫描（Sample Table Scans）

全表扫描

全表扫描将读取HWM之下的所有数据块，所有行都要经WHERE子句过滤看是否满足条件。当Oracle执行全表扫描时会按顺序读取每个块且只读一次，如果能够一次读取多个块，可以有效的提高效率，初始化参数DB\_FILE\_MULTIBLOCK\_READ\_COUNT用来设置在一次I/O中可以读取多少个数据块。 通常我们认为应该避免全表扫描，但是在检索大量数据时全表扫描优于索引扫描，这正是因为全表扫描可以在一次I/O中读却多个块，从而减少了I/O的次数。在使用全表扫描的同时也可以使用并行来提高扫描的速度。

CBO优化器何时会选择全表扫描：

1） 无合适的索引。

2） 检索表中绝大多数的数据。

3） 表非常小。比如，表中的块小于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） 太旧的统计数据。如果表没有进行过分析或很久没有再次分析，CBO可能会错误的认为表含有及少的数据块。

6） 在语句中嵌入了全表扫描的HINTS。

Rowid扫描

Rowid表示行在数据块中的具体位置，Rowid是查找具体行的最快方式。可以在WHERE子句中写入Rowid，但是不推荐这么做。通常都是通过索引来获得Rowid，但如果被检索的行都包含在索引中时，直接访问索引就能得到所需的数据则不会使用Rowid。

索引扫描

索引不仅包含被索引的字段值，还包含行的位置标识Rowid，如果语句只检索索引字段，Oracle将直接从索引中读取而不需要通过Rowid去访问表，如果语句通过索引检索其他字段值，则Oracle通过索引获得Rowid从而迅速找到具体的行。

索引扫描类型：

1） 唯一索引扫描（Index Unique Scans）

2） 索引范围扫描（Index Range Scans）

3） 索引降序范围扫描（Index Range Scans Descending）

4） 跳跃式索引扫描（Index Skip Scans）

5） 全索引扫描（Full Index Scans）

6） 快速全索引扫描（Fast Full Index Scans）

7） 索引连接（Index Joins）

在解释上述索引扫描类型之前，首先要明确一个问题——Oracle对I/O的评估是针对“块”的而不是“行”。优化器在决定是使用全表扫描还是索引扫描时，看的是所涉及块占全表的比例而不是检索的行占表的比例。当然如果一个块中只包含一个行数据，那么访问块和行是等同的，但是通常情况下都是一个块中含有多个行的数据，因此如果检索的行都聚集在少数块中则会大大降低I/O。

例如：一个有9行数据的表占据三个数据块，在STATUS字段上有一个非唯一索引，该字段共有三类不同的值分别是1、2、3。

第一种情况：

Block 1       Block 2        Block 3

-------       -------        --------

1  1  1       2  2  2        3  3  3

索引字段（STATUS）相同的值都聚集在表的同一个物理块中，这种情况下获取STATUS=1的数据只需读取表的一个物理块，即一次I/O。

第二种情况：

Block 1       Block 2        Block 3

-------       -------        --------

1  2  3       1  2  3        1  2  3

索引字段（STATUS）相同的值被分散在表中三个物理块，这时要得到STATUS=1的数据则要读取表的三个物理块，即三次I/O才能获得。

1）唯一索引扫描

在利用一个主键字段或含有唯一约束的字段选择一行记录时，通常发生唯一索引扫描。

2）索引范围扫描

索引范围扫描返回的数据返照索引字段值升序排列，值相同的按Rowid升序排列。如果在语句中使用了ORDER BY ASC子句，而且排序字段是索引字段时Oracle不会对ORDER BY再次排序。

例如：

SQL> select \* from t;

COLX              COLY

\--------------- ---------------

1                   3

1                   2

1                   1

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子句。

优化器在下列情况会使用索引范围扫描：

→ COL1 = :b1

→ COL1 > :b1

→ COL1 < :b1

→ COL1 LIKE ‘ABC%’会做索引范围扫描，而COL1 LIKE ‘%ABC’则不会。

→ 对于复合索引，通常只有复合索引的第一个字段包含在AND条件之中时才会使用复合索引。

3）索引降序范围扫描

如果在order by中指定了索引是降序排列的，或者使用了INDEX\_DESC提示，优化器会使用索引降序范围扫描。

例如：

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）跳跃式索引扫描

跳跃式索引扫描是用来提高复合索引效率的，通常当复合索引的第一个索引字段不在语句中指定时是无法使用复合索引的，此时如果复合索引的第一个索引字段DISTINCT值非常小，而复合索引的其他索引字段DISTINCT值非常大时，可以使用跳跃式索引扫描来跳过该复合索引的第一个索引字段。跳跃式扫描会使复合索引在逻辑上分裂成N个较小的索引，N值等于复合索引的第一个索引字段的DISTINCT值。

例如：

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)

索引IND\_SEX\_EMPID结构如下：

[![image][image 2]][image_image 2]

5）全索引扫描

当查询涉及的字段都包含在索引中，如果WHERE子句中谓词非第一个索引字段，或无WHERE子句但是被索引字段中至少有一个非空属性时，通常会做全索引扫描。全索引扫描结果集按索引字段排序。

6）快速全索引扫描

当查询涉及的字段都包含在索引中，且被索引字段中至少有一个非空属性时，可以使用INDEX\_FFS(table\_name index\_name)来使语句做快速全索引扫描。快速全索引扫描不同于全索引扫描，它使用多块读取的方式来读全部索引块，而且可以使用并行读取。快速全索引扫描的结果集不会排序。位图索引不能使用快速全索引扫描。

7）索引连接

索引连接是几个索引的散列连接。如果查询的字段上都存在索引，可以使用索引连接来避免访问表。

例如：

SQL> select\* from t;

COL1               COL2              COL3              COL4

\--------------- --------------- --------------- ---------------

1                     2                    3                   4

1                     1                    3                   4

1                     1                    5                   4

SQL> create index ind\_col1 on t(col1);

索引已创建。

SQL> create index ind\_col2 on t(col2);

索引已创建。

SQL> create index ind\_col3 on t(col3);

索引已创建。

不使用索引连接：

SQL> set autotrace traceonly

SQL> select col1,col2,col3 from t where col2>0;

Execution Plan

\----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T'

2 1 INDEX (RANGE SCAN) OF 'IND\_COL2' (NON-UNIQUE)

使用索引连接：

SQL> select /\*+index\_join(t ind\_col1 ind\_col2 ind\_col3)\*/col1,col2,col3 from t where col2>0;

Execution Plan

\----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=4 Bytes=156)

1 0 VIEW OF 'index$\_join$\_001' (Cost=70 Card=4 Bytes=156)

2 1 HASH JOIN

3 2 HASH JOIN

4 3 INDEX (RANGE SCAN) OF 'IND\_COL2' (NON-UNIQUE) (Cost=3 Card=4 Bytes=156)

5 3 INDEX (FAST FULL SCAN) OF 'IND\_COL1' (NON-UNIQUE) (Cost=3 Card=4 Bytes=156)

6 2 INDEX (FAST FULL SCAN) OF 'IND\_COL3' (NON-UNIQUE) (Cost=3 Card=4 Bytes=156)

簇扫描

在被索引的簇中，有着相同簇键值的行存储在同一数据块中。执行簇扫描时，首先通过扫描簇索引获得被检索行的Rowid，然后使用Rowid来定位具体的行。

散列扫描

散列扫描就是在一个散列簇中定位数据行。在一个散列簇中，具有相同散列值的行存储在相同的数据块中。在执行散列扫描时，首先通过一个散列函数来获得散列值，然后用散列值在数据块中定位具体行。

表取样扫描

当FROM子句后带有SAMPLE或SAMPLE BLOCK时，会执行表取样扫描来随机检索表中的数据。如：select\* from t sample block (1);

处理连接

影响一个连接语句执行计划的四个重要因素是：访问路径、连接方式、连接顺序和成本评估。

优化器如何处理连接顺序

优化器首先确定连接的表中是否包含其结果只有一行记录的表，如果存在这样的表，优化器在对连接表排序时会把这样的表放在最前端。如果是个外连接，含有（+）操作符的表一定排在不含（+）的表的后面。同理被转换成ANTI-JOIN或SEMI-JOIN的子查询，子查询的表一定排在外部表的后面，但是HASH-ANTI-JOIN和HASH-SEMI-JOIN在一定情况下可以违反此顺序。通常可以用ORDERED来指定连接顺序，但是ORDERED所指定的顺序如果违反了外连接的顺序，则ORDERED将被忽略。

优化器如何评估成本

嵌套循环连接，在于外表返回的每一行都要在内表中进行匹配的成本，成本计算如下：

cost of nested loop = access cost of outer + (number of rows from outer \* access cost of inner)

排序合并连接，在于把两个大表读入内存并进行排序的成本，成本计算如下：

cost of merge join = access cost of A + access cost of B + (sort cost of A + sort cost of B)

散列连接，在于将小表读入内存分成若干散列表，然后由大表对每个散列表都进行一次匹配的成本，成本计算如下：

cost of hash join = access cost of smaller + (access cost of bigger \* number of hash partitions of smaller)

以上成本计算公式不是绝对的，优化器对成本的评估还会受到其他因素的影响，比如：内存排序区过小会增加排序合并连接的成本，由于此种情况下的排序消耗了过多的CPU和I/O。多块读取会降低排序合并连接的成本，如果内表的连接字段存在索引也会降低嵌套循环连接的成本。

优化器可用的连接方式

1） 嵌套循环连接：适用于外表有效基数较小，内表连接字段含有索引，且查询整体返回结果集不太大（小于1万行）的情况下。HINTS：use\_nl

2） 散列连接：适用于查询整体返回大量结果集，且有较小的连接表可以放入内存作为散列表的情况下。适用散列连接要注意HASH\_AREA\_SIZE要足够大，可以容下散列表。如果散列表无法完全放入内存，要设置较大的临时段，从而尽量提高I/O性能。HINTS：use\_hash

3） 排序合并连接：适用于查询整体返回大量结果集，两个大表做连接，且表已经排过序的情况下。当两个表已经排过序时，使用排序合并连接的性能可能会优于散列连接。HASH\_AREA\_SIZE和SORT\_AREA\_SIZE设置过小，可能会导致优化器避开散列连接而选择排序合并连接。HINTS：use\_merge

4） 迪卡尔积连接：当两个表没有任何连接条件时会使用此连接方式。

一些影响优化器的初始化参数

OPTIMIZER\_FEATURES\_ENABLE：每个版本的Oracle优化器特性都不相同，特别是做了版本升级以后一定要修改这个参数才可以使用仅被该版本支持的优化器特性。可以赋予它的值如：9.2.0、9.0.2、9.0.1、8.1.7、8.1.6等。

CURSOR\_SHARING：这个参数会将SQL语句中的直接量用变量来替换，存在大批直接量的OLTP系统可以考虑启用这个参数。但是要注意，绑定变量虽然可以使大量的SQL重用，减少分析时间，但是执行计划可能会不理想。通常OLTP系统适用于绑定变量，OLTP系统特点是，SQL运行频繁且时间相对较短，SQL的分析时间比重较大。如果在DSS系统中，SQL运行时间长，相比之下分析时间微不足道，好的执行计划才是最重要的，因此DSS系统不建议使用这个参数。

HASH\_AREA\_SIZE：这是散列表的存放区域，如果使用散列连接这个参数值不能太小，否则对散列连接性能影响很大。如果是9i建议启动工作区自动管理，然后设置PGA\_AGGREGATE\_TARGET。

SORT\_AREA\_SIZE：内存排序区的大小，如果排序时内存区不够会写入磁盘。9i同样建议启动工作区自动管理，然后设置PGA\_AGGREGATE\_TARGET。

HASH\_JOIN\_ENABLED：只有启用这个参数，CBO在考虑连接方式的时候才会考虑散列连接。

OPTIMIZER\_INDEX\_CACHING：这个参数表示被缓存的索引块所占的百分比，可选值的范围是0-100。这个值会影响嵌套循环连接，如果这个值设得较高，CBO将更倾向使用嵌套循环。

OPTIMIZER\_INDEX\_COST\_ADJ：优化器利用这个参数（是个百分比）把索引扫描的成本转换为等价的全表扫描的成本，然后与全表扫描的成本进行比较。缺省值100，表示索引扫描成本与全表扫描成本等价。可选值范围是0-10000。

OPTIMIZER\_MAX\_PERMUTATIONS：这个初始参数用来设定优化器最多考虑多少种连接顺序，优化器不断的产生可能的表的连接的排列，直到排列数达到参数optimizer\_max\_permutations为止。一旦优化器停止产生新的排列，它将会从中选择出成本最小的排列。

DB\_FILE\_MULTIBLOCK\_READ\_COUNT：这个参数表示在全表扫描或索引快速全扫描时一次I/O读的连续数据块数量（block\#连续，且一次I/O不能超过extent）。

OPTIMIZER\_MODE：优化器模式。值为：RULE、CHOOSE、ALL\_ROWS、FIRST\_ROWS\_n、FIRST\_ROWS。

PARTITION\_VIEW\_ENABLED：如果设置为TRUE, 该优化器将跳过分区视图中未被请求的分区，该参数还能更改基于成本的优化程序从基础表统计信息计算分区视图统计信息的方式。

QUERY\_REWRITE\_ENABLE：如果设置为TRUE，优化器将利用可用的物化视图来重写SQL。

[image]: /images/20220716/cc240750951e42a4b3897b4a38bcafd3.png
[image 1]: http://images.cnblogs.com/cnblogs_com/dongzhiquan/201201/20120120234708327.png
[image 2]: http://images.cnblogs.com/cnblogs_com/dongzhiquan/201201/20120120234709785.png
[image_image 2]: /images/20220716/cef03eed6b6b4a28b773bc698c19b0bc.png