Oracle的优化器介绍

Oracle有两种类型的优化器：

• 基于规则的优化器
• 基于代价的优化器

基于规则的优化器--Rule Based Optimization（RBO）

基于规则的优化器中采用启发式的方法或者规则来生成执行计划。例如，如果一个查询的where条件包含一个谓词（predicate，就是一个判断条件，如‘=’，‘<’,‘>’等），而且该谓词上的引用的列有有效索引（比如 where order_number>30 中order_number是一个索引列）那么优化器将使用索引访问这个表，而不考虑其他因素（包括表中数据有多少，表中数据的易变性，索引的可选择性等）。此时，数据库中没有关于表与索引数据的统计描述，如表中有多少行，每行的可选择性等。优化器也不考虑实例参数，如multi block i/o、可用排序内存的大小等，所以RBO优化器有时就选择了次优化计划作为真正的执行计划，导致性能不高。

下面举一个例子🌰

对于查询语句

SELECT * FROM emp 
WHERE dept_no=10

如果使用的是基于规则的优化器，而且deptno列上存在有效索引，则会通过deptno列上的索引来访问emp表。在绝大多数情况下这是比较高效的，但是在一些特殊情况下，使用索引访问也有比较低效的时候。

下面举例说明：

第一种情况：emp表较小，该表可能只存放在几个数据块中。此时使用全表扫描反而比索引访问效果要好。因为表比较小，极有可能数据全存在内存中，所以此时用全表扫描是最快的。如果使用索引扫描，需要先从索引中找出符合记录的rowid（rowid直接存放着该行的物理存储地址），然后再一一根据这些rowid从emp中将数据取出。在这种条件下，效率会比全表扫描差一些。

第二种情况：emp表较大时，而且deptno=10条件能查询出表中大部分数据比如（50%）。举个具体点的数据，假如该表有4000万行数据，共放在50万个数据块中，每个数据块为8k，则该表约为4G。这么多数据只能放在磁盘中，此时该查询如果通过索引查询，那么db_file_multiblock_read_count的参数为200。如果采用全表扫描，则需要 50万/200=2500次I/O操作，但是用索引扫描的话因为要读出4000万*50%=2000万的数据，假设在读这2000万数据的时候，有99.9%的命中率，那么还是需要20000次I/O操作，远远大于上面的2500次，因此在这种情况下用索引扫描会降低性能。全表扫描的时间是固定的，但是用索引扫描的时间会随着数据的增多是查询时间相应的延长。

基于代价的优化器--Cost Based Optimization（CBO）

Oracle把一个代价引擎集成到数据库内核中，用来估计每个执行计划需要的代价，该代价将每个执行计划所耗费的资源进行量化，从而CBO可以根据这个代价选择出最优的执行计划。查询所耗费的资源可以被分为3个基本组成部分：I/O代价、CPU代价、network代价。

如何选取当前数据库使用何种优化器

由optimizer_mode的初始参数决定。这个参数有下面几种可能的选值：

• RULE：使用RBO优化器
• CHOOSE：根据实际情况选择优化器，如果数据字典包含被引用的表的统计数据，即引用的对象已经被分析，则使用CBO优化器，否则则为RBO优化器。
• ALL_ROWS：是CBO优化器使用的第一种具体优化方法，是以数据吞吐量为主要目标，以便可以用最少的资源完成执行任务
• FIRST_ROWS：是CBO使用的第二种优化方法，是以数据响应时间为主要目标，以便快速查询出开始的几行数据。
• FIRST_ROWS_[1|10|100|1000]：为优化器使用的第三种具体的优化方法，让优化器选择一个能够把响应时间减小到最小的查询执行计划，以迅速产生查询结果的前n行。