<让oracle跑得更快-4> 优化器（optimizer）

59yq60581a

Oracle数据库中优化器（optimizer）是sql分析和执行的优化工具，它负责制定sql的执行计划，也就是它负责保证sql执行的效率最高，比如优化器决定oracle以什么样的方式访问数据，是全表扫描（Full Table Scan, FTS）,索引范围扫描（Index Range Scan）还是全索引快速扫描（Index Fast Full Scan, INDEX_FFS）;对于表关联查询，它负责确定表之间以一种什么方式来关联，比如hash_join还是nested loops或是merge join。这些因素直接决定着sql的执行效率，所以优化器是sql执行的核心，它做出的执行计划好坏，直接决定着sql的执行效率。
Oracle的优化器有两种，基于规则的优化器（RBO）和基于代价的优化器（CBO）,从oracle10g开始，RBO已经被弃用（但是我们依然可以通过HINT方式来使用它）。
4.1 RBO基于规则的优化器
在8i之前，oracle使用的是一种叫做RBO(Rule Based Optimizer，基于规则的优化器)的优化器，它的执行机制非常简单，就是在优化器里嵌入若干种规则，执行的sql语句符合哪种规则，就按照规则制定出相应的执行计划，比如说表上有个索引，如果谓词上有索引的列存在，则oracle会选择索引，否则选择全表扫描；又比如，两个表关联的时候，按照表在sql中的位置来决定哪个是驱动表，哪个是被驱动表。
下图是RBO在选择执行计划的一个优先级列表。

但事实是，走索引未必比走全表扫描快，还要看数据分布等。比如，首先创建一个分布极不均匀的表：
Create table t as select 1 id, object_name from dba_objects;
Update t set id=99 where rownum = 1; ---这样整个表就只有第一行的id=99；其余都是1.
Create index ind_t on t(id);
Select id, count(*) from t group by id;
结果：
ID   COUNT(*)
----------------------------
151080
99    1

在RBO的年代里，这两条的执行计划时这样的：
Select /*+ rule */ * from t where id=99;
执行计划

最后一句提示当前使用的优化器是RBO，建议使用CBO。
再次查询Select /*+ rule*/ * from t where id=1;
执行计划：

使用hint /*+ rule*/的方式强制让oracle使用RBO优化器来产生执行计划，结果非常让人失望，oracle在id字段有索引的情况下，毫无悬念地全部选择了索引。
实际上我们知道，对于id=1,几乎所有的数据全都符合谓词条件，选择索引只能增加额外的开销（因为oracle要首先访问索引数据块，在索引上找到了相应的键值，然后按照键值上的rowid再去访问表中的相应数据），既然我们几乎要访问表中所有的数据，那么全表扫描自然是最优的选择，很遗憾的是，RBO让我们失望地做出了错误的选择。
RBO的情况就是这样，它是一种呆板、过时的优化器，已经在oracle10g以后的版本上被彻底剔除掉了（但仍然能通过使用hint来强制使用）。
4.2 CBO基于成本的优化器
从8i开始，oracle引入了CBO(Cost Based Optimizer, 基于成本的优化器)，它的思路是让oracle获取所有执行计划相关的信息，通过对这些信息做计算分析，最后得出一个代价最小的执行计划作为最终执行计划。CBO是一种比RBO更理性的优化器。
还是拿上面的例子，看下CBO的表现：
Exec dbms_stats.gather_table_stats(user, ‘t’, cascade=>true);
Select /*+ all_rows */ * from t where id=1;
执行计划：

Select /*+ all_rows */ * from t where id=99;
执行计划：

CBO表现的非常完美，CBO会根据表的统计信息做出正确的执行计划：
Id=1, 全表扫描
Id=99, 索引扫描
因为这样的选择代价是最小的。从10g开始， oracle已经彻底丢弃了RBO。即使在表、索引没有被分析的时候，oracle依然会使用CBO。此时，oracle会使用一种叫做动态采样的技术，在分析sql的时候，动态的收集表、索引上的一些数据块，使用这些数据块的信息及字典表中关于这些对象的信息来计算出执行计划的代价，进而挑选出最优的执行计划。
比如：
Create table t (x int, y varchar2(2000));
Insert into t select object_id, object_name from dba_objects;
Commit;
Create index t_ind on t(x);
Alter session set sql_trace=true;
Select count(x) from t where x <100;
Count(x)
------
98
Alter system flush shared_pool;
Set autotrace trace exp;
Set linesize 120;
Select * from t where x=100;

可以看到，当表没有做分析的时候，oracle会使用动态采样来收集统计信息，这个动作只有在sql执行的第一次，即硬分析阶段使用，后续的软分析将不再使用动态采样，直接使用第一次sql硬分析时生成的执行计划。
CBO优化器有两种可选的运行模式：
（1）FIRST_ROWS(n)
（2）ALL_ROWS
当设置优化器模式为FIRST_ROWS(n)时，意味着oracle在执行sql时，优先考虑将结果集中的前n条记录以最快的速度反馈回来，而其他的结果并不需要同时返回。这种需求在一些网站搜索或者BBS的分页上经常看到，比如每次只查询信息的前20条或BBS的前20个帖子，这时设置FIRST_ROW(n)就非常合适，优化器并不需要同时将所有符合条件的结果返回。比如：
Select /*+ first_rows(10) */ b.x, b.y
from (select /*+ first_rows(10) */ a.*, rownum rnum
from (select /*+ first_rows(10)*/ * from t order by x) a where rownum <= 20) b
where rnum >= 10;
这是一个典型的分页的例子，每次从结果取出10条记录。需要注意的是，排序使用的字段x必须创建有索引，否则CBO会忽略FIRST_ROWS(N)而使用ALL_ROWS.
ALL_ROWS在OLAP系统中使用得比较多，它的目的在于用最快的速度获得sql执行的最后一条记录，而不是前n条数据。和FIRST_ROWS(n)正好相反，ALL_ROWS强调sql整体的执行效率，而FIRST_ROWS(n)强调以最快的速度返回前n行，而不管所有的结果返回时长，可能最后一条要很长时间之后才能获得。