Oracle优化器是Oracle数据库的核心组件，它的核心作用是为用户输入的每一条SQL语句生成一个最优的执行计划，直接决定了SQL语句的执行效率和资源消耗情况。优化器的决策逻辑和执行模式会随数据库版本和配置发生变化，理解其工作原理对数据库性能调优至关重要。
Oracle优化器的两种核心类型
Oracle数据库历史发展中出现过两种主要的优化器类型，分别是基于规则的优化器RBO和基于成本的优化器CBO，二者的决策逻辑存在本质区别。
基于规则的优化器RBO
RBO是Oracle早期版本使用的优化器，它的决策完全基于预先定义好的优先级规则，不会考虑表的数据量、数据分布等实际成本因素。规则优先级固定，比如走索引的优先级高于全表扫描，走唯一索引的优先级高于非唯一索引。
RBO的明显缺陷是灵活性不足，当表数据量发生巨大变化时，它依然会按照固定规则生成执行计划，很可能出现不合理的执行路径。从Oracle 10g版本开始，RBO已经被官方废弃，不再推荐使用。
基于成本的优化器CBO
CBO是当前Oracle数据库默认且主流的优化器，它会结合SQL语句的复杂度、表的统计信息、索引情况、系统资源状态等多方面因素，计算不同执行路径的成本，最终选择成本最低的执行计划。
CBO的核心依赖是统计信息，包括表的数据行数、数据块数量、列的数据分布、索引的层级等信息。如果统计信息过期或者缺失，CBO生成的执行计划很可能出现偏差。
优化器模式配置与调整
Oracle提供了不同的优化器模式参数，用户可以根据实际业务场景调整，参数名为OPTIMIZER_MODE，支持以下几种常用取值：
- ALL_ROWS：以最小化总体资源消耗为目标，适合报表类、批量处理类的SQL，优先保证整个查询的总完成时间最短。
- FIRST_ROWS_n：n可以取1、10、100、1000，以最快返回前n行数据为目标，适合分页查询、交互式查询场景，优先提升前几行数据的返回速度。
- FIRST_ROWS：旧版的快速返回少量数据的模式，现在已经逐渐被FIRST_ROWS_n替代，不推荐使用。
- CHOOSE：早期版本的模式，如果表有统计信息就用CBO，没有就用RBO，现在已经被废弃。
修改优化器模式的示例SQL如下：
-- 修改当前会话的优化器模式为ALL_ROWS ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS; -- 修改整个数据库的优化器模式，需要管理员权限 ALTER SYSTEM SET OPTIMIZER_MODE = FIRST_ROWS_100 SCOPE=BOTH;
影响CBO决策的常见方式
除了调整优化器模式，我们还可以通过其他方式干预CBO的决策，让执行计划更符合业务预期：
及时收集和更新统计信息
统计信息是CBO决策的基础，当表的数据发生大量变更后，需要及时更新统计信息，避免CBO基于过期信息生成错误计划。常用的统计信息收集语句如下：
-- 收集指定表的统计信息，包括表、列、索引的统计信息 EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'EMP', CASCADE => TRUE); -- 收集整个schema的统计信息 EXEC DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => 'SCOTT');
使用Hint提示优化器
如果CBO生成的执行计划不符合预期，可以在SQL中使用Hint提示，强制优化器按照指定方式生成执行计划。常用的Hint示例如下：
-- 强制使用全表扫描 SELECT /*+ FULL(emp) */ * FROM emp WHERE deptno = 10; -- 强制使用指定索引 SELECT /*+ INDEX(emp idx_emp_deptno) */ * FROM emp WHERE deptno = 10; -- 强制使用嵌套循环连接 SELECT /*+ USE_NL(e d) */ e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
调整系统统计信息
CBO还会参考系统的I/O速度、CPU速度等系统统计信息来计算成本，我们可以通过收集系统统计信息让CBO更准确评估不同执行路径的成本：
-- 收集系统统计信息，默认会评估I/O和CPU的性能 EXEC DBMS_STATS.GATHER_SYSTEM_STATS();
执行计划的查看与分析
调整优化器配置后，我们需要查看SQL的执行计划验证调整效果，常用的查看方式有两种：
使用EXPLAIN PLAN命令
-- 生成执行计划 EXPLAIN PLAN FOR SELECT * FROM emp WHERE deptno = 10; -- 查看生成的执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
使用AUTOTRACE功能
-- 开启AUTOTRACE，执行SQL后会同时显示执行计划和统计信息 SET AUTOTRACE ON; -- 执行需要分析的SQL SELECT * FROM emp WHERE deptno = 10; -- 关闭AUTOTRACE SET AUTOTRACE OFF;
分析执行计划时，重点关注全表扫描、索引范围扫描、嵌套循环连接、哈希连接等操作的代价，对比调整前后的成本变化，判断优化是否生效。