切换Oracle优化器的方式

在Oracle数据库中，可以通过修改参数OPTIMIZER_MODE来切换优化器模式，该参数控制优化器在生成执行计划时采用**基于规则（RBO）还是基于成本（CBO）**的策略。以下是详细操作步骤和注意事项：

一、优化器模式类型

1. RULE

   • 使用Oracle早期预设的15条访问路径规则（如索引优先于全表扫描）。
   • 已弃用：Oracle 10g后默认禁用，仅用于兼容旧应用。
   • 示例规则：
     • 单列索引优先于复合索引。
     • 全表扫描的优先级低于任何索引扫描。

2. FIRST_ROWS

   • 基于成本，但优先返回首行数据（适合OLTP系统，强调低延迟）。
   • 可能选择嵌套循环连接（Nested Loops）等快速返回结果的计划。
   • 子选项：
     • FIRST_ROWS_1：优化返回首1行。
     • FIRST_ROWS_10/100/1000：优化返回首N行。

3. FIRST_ROWS_N（Oracle 12c+）

   • 替代旧版FIRST_ROWS，更精细控制返回首批行的优化目标。

4. ALL_ROWS（默认）

   • 基于成本，优化整体查询吞吐量（适合数据仓库，强调资源高效利用）。
   • 可能选择哈希连接（Hash Join）或全表扫描等高吞吐量计划。

二、切换优化器模式的方法

1. 会话级修改（临时生效）

-- 查看当前会话的优化器模式
SHOW PARAMETER optimizer_mode;

-- 修改当前会话的优化器模式（仅对当前连接有效）
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
-- 或
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;

2. 系统级修改（永久生效）

-- 修改参数文件（SPFILE或PFILE）并重启数据库
-- 1. 编辑SPFILE（需DBA权限）
ALTER SYSTEM SET OPTIMIZER_MODE = FIRST_ROWS SCOPE=SPFILE;

-- 2. 重启数据库使更改生效
SHUTDOWN IMMEDIATE;
STARTUP;

• SCOPE选项：
  • MEMORY：仅修改当前实例（重启后失效）。
  • SPFILE：修改参数文件（需重启）。
  • BOTH：同时修改内存和参数文件（需重启）。

3. 使用SQL提示（Hint）强制指定

在SQL语句中通过提示覆盖会话或系统级设置：

-- 强制使用FIRST_ROWS模式
SELECT /*+ FIRST_ROWS */ * FROM employees WHERE salary > 5000;

-- 强制使用ALL_ROWS模式
SELECT /*+ ALL_ROWS */ * FROM large_table WHERE dept_id = 10;

三、验证优化器模式

1. 查看当前模式

   SELECT name, value, display_value 
   FROM v$parameter 
   WHERE name = 'optimizer_mode';
   

2. 分析执行计划
   使用EXPLAIN PLAN或DBMS_XPLAN查看优化器选择的计划是否符合预期：

   EXPLAIN PLAN FOR SELECT * FROM employees WHERE dept_id = 10;
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
   

   • 关键指标：
     • Operation（如TABLE ACCESS FULL、INDEX RANGE SCAN）。
     • Cost（成本值，模式不同可能导致成本估算差异）。
     • Cardinality（估算行数，反映选择性计算方式）。

四、切换优化器模式的注意事项

1. 兼容性问题

   • RULE模式已过时，可能导致性能下降或错误（如无法利用新特性如并行查询）。
   • 仅在迁移旧应用时临时使用，建议最终迁移到CBO。

2. 性能影响

   • OLTP系统：优先使用FIRST_ROWS以减少响应时间。
   • 数据仓库：使用ALL_ROWS以最大化吞吐量。
   • 混合负载：通过提示（Hint）或SQL Profile针对特定SQL优化。

3. 统计信息依赖

   • CBO模式（FIRST_ROWS/ALL_ROWS）依赖准确的统计信息。
   • 确保定期收集统计信息：

     EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');
     

4. 动态采样

   • 对未分析的表，CBO可能启用动态采样（OPTIMIZER_DYNAMIC_SAMPLING）。
   • 调整采样级别（0-11）以平衡估算精度和开销。

5. 参数冲突

   • 避免同时设置冲突参数（如OPTIMIZER_INDEX_COST_ADJ和FIRST_ROWS），可能导致优化器决策混乱。

五、示例场景

场景1：OLTP系统优化响应时间

-- 会话级切换到FIRST_ROWS
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;

-- 执行查询（优化器优先选择快速返回结果的计划）
SELECT /*+ INDEX(e idx_emp_salary) */ * 
FROM employees e 
WHERE salary > 5000 
AND ROWNUM < 10;

场景2：数据仓库优化吞吐量

-- 系统级切换到ALL_ROWS（需DBA权限）
ALTER SYSTEM SET OPTIMIZER_MODE = ALL_ROWS SCOPE=SPFILE;

-- 重启后执行大表聚合查询
SELECT dept_id, AVG(salary) 
FROM employees 
GROUP BY dept_id;

总结

• 推荐模式：Oracle 12c+默认使用ALL_ROWS（CBO），适合大多数场景。
• 临时调整：通过会话级设置或SQL提示快速验证不同模式的效果。
• 长期策略：结合统计信息管理、SQL调优和参数配置（如PGA_AGGREGATE_TARGET）综合优化性能。
• 避免误区：不要盲目切换模式，需通过执行计划分析实际性能差异。