ORACLE优化器

一 ORACLE优化器概述
ORACLE优化器是用于生成SQL语句访问数据库时使用的执行计划的。ORACLE优化器通过使用ORACLE搜集的关于数据库对象的统计数据来生成SQL语句的访问计划（使用什么对象）并执行计划（使用何种操作）。


二 ORACLE优化器的分类
ORACLE优化器分为三类：
1） RULE优化器：基于规则的优化器相对比较简单，通过检查数据库的可用路径并将这些路径与路径表进行比较，从而确定SQL语句的执行计划。RULE优化的过程中不需要任何表或索引的统计信息，而且也会忽略任何表或索引的统计信息
2） COST优化器：基于开销的优化器相对比较复杂，通过使用数据库的结构和数据等信息来选择最优的执行计划。优化的过程中需要相关表和索引的统计信息。
3） CHOOSE优化器：在存在相关统计信息的情况下采用基于开销的优化器，在不存在相关统计信息的情况下才用基于规则的优化器。这是8I中默认的优化器工作方式


三 查询当前优化器的工作方式
查询当前优化器的工作方式比较简单的方法有以下两种：
1） 查看ORACLE数据库的初始化文件，看看初始化参数optimizer_mode的取值
2） SQL> select name,value from v$parameter where name = 'optimizer_mode';
NAME         VALUE
---------------     ---------------------------------------------------------
optimizer_mode  CHOOSE


四 修改优化器的工作模式
可以使用下面的两种方法来选择优化器模式（基于开销的模式和基于规则的模式）：
1） 初始化参数optimizer_mode
2） alter session 命令的 optimizer_goal参数
选择优化器模式时所能使用的选项
 choose：这个选项允许优化器根据特定表或索引的统计数据的可用性选择优化模式。如果sql语句中涉及到的表中有一个表有统计数据，那么优化器将选择基于开销的优化方法；否则如果所有表都没有统计数据，那么优化器将选择基于规则的优化模式
 rule：这个选项将导致优化器总是使用基于规则的优化方法，而不管系统中是否存在被访问表的统计数据
 all_rows：这个选项将导致优化器对所有的sql语句都使用基于开销的优化方法，即使对于系统中不存在可用统计数据的表，情况也是如此。这个选项的目的是使用最少的系统资源获得最大的吞吐量，力求完成sql语句的总花费为最少
 first_rows_n：这个选项将导致优化器对所有的sql语句都使用基于开销的优化方法，即使对于系统中不存在可用统计数据的表，情况也是如此。这个选项的目的是获得最快的响应时间，力求最快返回结果中的前n条记录


五 基于规则的优化器
因为我们现在的测试环境和所有的医保中心以及定点医疗机构都是采用默认的choose优化器选项，而且所有的表和索引等也没有相关的统计信息，所以实际上oracle数据库都是运行在rule的优化器模式下。下面详细的介绍一下基于规则的优化器模式，对基于开销的优化器仅做简单的介绍。
在基于规则的和基于开销的优化器中，基于规则的优化器相对来说比较简单。在基于规则的优化方法中，优化器通过检查数据库的可用路径并将这些路径与路径表进行比较，从而确定sql语句的执行计划。下面的表中包含了与各种不同访问路径相关的开销
级别 访问路径
1 通过rowid访问单行数据
2 通过簇连接访问单行数据
3 通过带惟一键或主键的散列簇访问单行数据
4 通过惟一键或主键访问单行数据
5 簇连接
6 散列簇键
7 索引簇键
8 复合键
9 单字段索引
10 在索引字段上有边界的范围内搜索数据
11 在索引字段上无边界的范围内搜索数据
12 排序合并连接
13 搜索索引字段的最大或最小值
14 对索引字段使用order by操作
15 全表扫描
举个最简单的例子来说明基于规则的优化器如果选择执行路径
建立测试用表

SQL> drop table test;
表已丢弃。
SQL> create table test
  2  (id varchar2(3),
  3  name varchar2(10))
  4  tablespace test;
表已创建。
SQL> insert into test values ('001','tom');
已创建 1 行。
SQL> commit;
提交完成。

没有建立的索引的情况下，该sql执行的唯一方式就是全表扫描（即使全表扫描被规则优化器认为是最慢的一种方式）

SQL> set autotrace on
SQL> select * from test where id='001';
ID  NAME
--- ----------
001 tom
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'

在test的id字段上建立索引后，上面这条sql语句就存在两种执行方案，一是全表扫描（15），另一种就是单字段索引（9）。Oracle根据前面的规则认为后者的开销比较小，所以优化器选择通过索引的扫描。

SQL> create index idx_test on test(id);
索引已创建。
SQL> select * from test where id='001';
ID  NAME
--- ----------
001 tom
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST' (NON-UNIQUE)

上面的这个例子说明了基于规则的优化器的工作方式。基于规则的优化器原理比较简单，可以给系统提供比较好的执行计划的稳定性。

六 基于开销的优化器
基于开销的优化器需要表或索引上的统计信息，他根据这些统计信息来为sql选择一条他认为是开销最小的执行路径。由于数据库里面的数据是一直在变化的，所以统计信息也应该及时的更新，这样才可以为基于开销提供准确的信息，为sql提供更加优化的方案。

SQL> alter session set OPTIMIZER_MODE=all_rows;
会话已更改。
SQL> analyze table test compute statistics;
表已分析。
SQL> select * from test where id='001';
ID  NAME
--- ----------
001 tom
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=6)
   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=1 Card=1 Bytes=6)

虽然test表上存在索引，但是由于表中仅有一条记录，所以全表扫描的开销应该比通过索引来查找记录要小的多。因此基于开销的优化器选择了全表扫描。从上面的两个例子的对比来看只要有了统计信息，基于开销的优化器比基于规则的优化器要聪明的多。

七 总结
一般来说8i以下的数据库使用基于规则的优化器比较合适，因为这些版本里面的基于开销的优化器并不完善，为sql语句提供的执行路径并不总是最优的；而8i以及以上的版本，基于开销的优化器已经比较完善了，可以很好的代替基于规则的优化器来工作了。