optimizer_mode优化器模式
查询优化器最主要的工作就是接受输入的SQL以及各种环境参数、配置参数，生成合适的SQL执行计划（Execution Plan）。
Query Optimizer一共经历了两个历史阶段：
- RBO: Rule-Based Optimization 基于规则的优化器
- CBO: Cost-Based Optimization 基于代价的优化器
关于RBO与CBO，一个形象的比喻：大数据时代到来以前，做生意或许凭借多年累计 下来的经验规则（RBO）就能够很好的做出决策，跟随市场变化。但是大数据时代，如果做生意还是靠以前凭经验做决策，而不是靠大数据、数据分析、数据挖掘做决策，那么就有可能做出错误的决策。这也就是越来越多的公司对BI、数据挖掘越来越重视的缘故
一、RBO优化器
RBO(Rule Based Optimizer,基于规则的优化器）就是在优化器里面嵌入15种规则，它根据ORACLE指定的优先顺序规则，对指定的表进行执行计划的选择。执行SQL语句符合哪种规则，就按照规则定制出相应的SQL执行计划。比如在规则中，索引的优先级大于全表扫描;RBO是根据可用的访问路径以及访问路径等级来选择执行计划，在RBO中，SQL的写法往往会影响执行计划，它要求开发人 员非常了解RBO的各项细则，菜鸟写出来的SQL脚本性能可能非常差。由于它他是一种过时呆板的优化器，Oracle官方已不再支持RBO服务。
15种规则及ranking:
- RBO Path 1: Single Row by Rowid
- RBO Path 2: Single Row by Cluster Join
- RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key
- RBO Path 4: Single Row by Unique or Primary Key
- RBO Path 5: Clustered Join
- RBO Path 6: Hash Cluster Key
- RBO Path 7: Indexed Cluster Key
- RBO Path 8: Composite Index
- RBO Path 9: Single-Column Indexes
- RBO Path 10: Bounded Range Search on Indexed Columns
- RBO Path 11: Unbounded Range Search on Indexed Columns
- RBO Path 12: Sort Merge Join
- RBO Path 13: MAX or MIN of Indexed Column
- RBO Path 14: ORDER BY on Indexed Column
- RBO Path 15: Full Table Scan
二、CBO优化器
CBO是一种比RBO更加合理、可靠的优化器，它是从ORACLE 8中开始引入，但到ORACLE 9i 中才逐渐成熟。进入Oracle 10g之后，Query Optimizer就已经将CBO（Cost Based Optimizer,基于代价的优化器）作为默认优化器，并且Oracle官方不再支持RBO服务。
它的思路是让Oracle获取所有的执行计划的相关信息，利用系统统计量进行各种执行路径试算，计算各种可能“执行计划”的“代价”，即COST，获取相对相对成本最低的执行计划，作为实际运行方案。它依赖数据库对象的统计信息，统计信息的准确与否会影响CBO做出最优的选择。如果对一次执行SQL时发现涉及对象（表、索引 等）没有被分析、统计过，那么ORACLE会采用一种叫做动态采样的技术，
动态统计量收集是Oracle CBO优化器的一种特性。优化器生成执行计划是依据成本cost公式计算出的，如果相关数据表没有收集过统计量，又要使用CBO的机制，就会引起动态采样(Dynamic Sampling）,。
动态采样（dynamic sampling）就是在生成执行计划是，以一个很小的采用率现进行统计量收集, 动态的收集表和索引上的一些数据信息。由于采样率低，采样过程快但是不精确，而且采样结果不会进入到数据字典中。
CBO查询优化器由查询转换器（Query Transform）、代价估算器（Estimator）和计划生成器（Plan Generator）组成。
CBO优化器有两种可选的运行模式：
2.1 FIRST_ROWS(n)
当设置优化器模式为：FIRST_ROWS(n)时，意味着Oracle在执行SQL语句时，优先考虑将结果集中的前n条记录以最快的速度反馈回 来，而其他结果并不需要同时反馈，也就是说在处理数据的时候，后面的数据可能还没提取出来，前面的数据已经返回给用户了，这种需求在网站搜索或者BBS的 分页上经常看到。比如每次只显示查询信息的前20条，这时设置FIRST_ROWS(20)就非常合适。对于分页操作，越靠前的页，显示结果需要的时间将越短。
需要注意的是排序使用的X必须创建有索引，否则CBO会忽略FIRST_ROWS(n)而使用ALL_ROWS.
2.2 ALL_ROWS
CBO的模式为ALL_ROWS时，意味着我们需要Oracle以最快的速度将SQL执行完毕，将结果集全部返回。它和FIRST_ROWS(n) 的区别在于，ALL_ROWS强调整体的执行效率，而FIRST_ROWS(n)强调以最快的速度返回前n条记录。ALL_ROWS在OLAP系统中使用 的比较多，它的目的在于快速获取执行结果的最后一条记录。
三、CHOOSE——两个时代的过渡
在Oracle 9i一个时期，optimizer_mode的默认参数取值为CHOOSE。那个时期是query optimizer从RBO向CBO转换的时代，Oracle选择了渐变式的转换。由于那时没有专门的夜间统计量收集作业，很多时候数据表是没有统计量的。加之动态抽样技术的不成熟，所以CBO的应用存在一些障碍。在这个时期，Oracle提出了CHOOSE作为优化器默认模式。
CHOOSE是介于自动选择切换RBO和CBO。如果SQL涉及的数据表中有一个有统计量，那么该SQL就是用CBO优化器。否则就是用RBO。动态采样技术是在CBO优化器前提下的技术方案。
SCOTT@PDBORCL> alter session set optimizer_mode=choose; 会话已更改。 SCOTT@PDBORCL> set autotrace traceonly; SCOTT@PDBORCL> exec dbms_stats.delete_table_stats('scott','emp',cascade_columns => true,cascade_indexes => true); PL/SQL 过程已成功完成。 SCOTT@PDBORCL> select ename from emp ; 已选择 14 行。 执行计划 ---------------------------------------------------------- Plan hash value: 3956160932 ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| EMP | ---------------------------------- Note ----- - rule based optimizer used (consider using cbo) 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 732 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed SCOTT@PDBORCL> exec dbms_stats.gather_table_stats (ownname => 'scott',tabname => 'emp'); PL/SQL 过程已成功完成。 SCOTT@PDBORCL> select ename from emp; 已选择 14 行。 执行计划 ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 84 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMP | 14 | 84 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 732 bytes sent via SQL*Net to client 544 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed SCOTT@PDBORCL>
四、optimizer_mode的查看与修改
通过优化器参数optimizer_mode，我们可以控制Oracle优化器生成不同模式下的执行计划。
4.1查看optimizer_mode
parameter optimizer_mode
SCOTT@PDBORCL> show parameter optimizer_mode NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_mode string ALL_ROWS SCOTT@PDBORCL> select name, value from v$parameter where name='optimizer_mode'; NAME VALUE -------------------------------------------------------------------------------- --------------------------------------- optimizer_mode ALL_ROWS SCOTT@PDBORCL>
4.2修改optimizer_mode
ORACLE 10g 优化器可以从系统级别、会话级别、语句级别三种方式修改优化器模式，非常方便灵活。 其中optimizer_mode可以选择的值有： first_rows_n,all_rows. 其中first_rows_n又有first_rows_1000, first_rows_100, first_rows_10, first_rows_1
系统级别
可以通过下面语句修改optimizer_mode
alter system set optimizer_mode=all_rows scope=both;
会话级别
会话级别修改优化器模式，只对当前会话有效，其它会话依然使用系统优化器模式。
alter session set optimizer_mode=first_rows_100;
语句级别
语句级别通过使用提示hints来实现。
select /*+ rule */ * from emp;
参考：
