oracle优化器执行过程

Oracle优化器（Optimizer）是数据库的核心组件，负责将SQL语句转换为高效的执行计划（Execution Plan），其执行过程涉及复杂的成本估算、统计信息分析和算法决策。以下是Oracle优化器执行过程的详细解析，涵盖关键步骤、算法类型及优化技巧：

一、优化器类型

Oracle提供两种优化器模式，通过参数OPTIMIZER_MODE控制：

1. 基于规则的优化器（RBO, Rule-Based Optimizer）：

   • 原理：依赖预设的15条访问路径规则（如索引优先于全表扫描）。
   • 现状：Oracle 10g后已弃用，仅用于兼容旧应用。

2. 基于成本的优化器（CBO, Cost-Based Optimizer）：

   • 原理：通过统计信息计算不同执行计划的成本（I/O、CPU、网络等），选择最低成本的计划。
   • 现状：默认优化器，支持高级特性（如并行查询、自适应执行计划）。

二、CBO优化器执行过程

1. SQL解析（Parse）

• 语法解析：检查SQL语法是否正确，生成解析树（Parse Tree）。
• 语义解析：验证表、列、权限等对象是否存在，绑定变量替换。
• 输出：生成查询块（Query Block），每个块对应一个子查询或表访问。

2. 查询转换（Query Transform）

优化器尝试重写SQL以简化查询或利用更优访问路径，常见转换包括：

• 视图合并（View Merging）：将视图展开为基表操作。
• 子查询优化（Subquery Unnesting）：将子查询转为连接（JOIN）。
• 谓词下推（Predicate Pushdown）：将过滤条件提前到数据访问阶段。
• 物化视图重写（Materialized View Rewriting）：用预计算结果替代复杂查询。
• 示例：

  -- 原始SQL
  SELECT * FROM (SELECT * FROM employees WHERE dept_id = 10) WHERE salary > 5000;
  
  -- 转换后（视图合并+谓词下推）
  SELECT * FROM employees WHERE dept_id = 10 AND salary > 5000;
  

3. 统计信息收集（Statistics Collection）

优化器依赖统计信息估算成本，主要数据源包括：

• 表统计信息：行数、块数、平均行长度。
• 列统计信息：不同值数量（NDV）、数据分布（直方图）、空值比例。
• 索引统计信息：索引高度、聚簇因子（Clustering Factor）、叶块数。
• 系统统计信息：I/O性能、CPU速度（通过DBMS_STATS.GATHER_SYSTEM_STATS收集）。
• 动态采样：对未分析的表进行实时采样（参数OPTIMIZER_DYNAMIC_SAMPLING控制）。

4. 执行计划生成（Plan Generation）

优化器通过以下步骤生成候选计划并选择最优：

1. 访问路径选择：

   • 全表扫描（Full Table Scan）：适合小表或无合适索引。
   • 索引扫描（Index Scan）：包括唯一索引扫描、范围扫描、索引跳跃扫描等。
   • ROWID访问（Table Access by ROWID）：通过索引定位数据行。

2. 连接方法选择：

   • 嵌套循环连接（Nested Loops）：适合小表驱动大表，低延迟但高CPU。
   • 哈希连接（Hash Join）：适合等值连接和大数据量，内存敏感。
   • 排序合并连接（Sort Merge Join）：适合非等值连接或已排序数据。
   • 笛卡尔积（Cartesian Product）：无连接条件时的最后选择。

3. 连接顺序确定：

   • 动态规划（Dynamic Programming）：对小查询块（通常≤8张表）枚举所有可能顺序。
   • 贪心算法（Greedy Algorithm）：对大查询块逐步选择局部最优连接。
   • 遗传算法（Genetic Algorithm）：Oracle 12c引入，用于复杂查询的近似最优解。

4. 并行执行计划生成：

   • 根据PARALLEL提示或表属性决定是否并行化操作（如全表扫描、哈希连接）。

5. 成本估算：

   • 对每个候选计划计算总成本（TOTAL_COST），公式为：

     总成本 = I/O成本 + CPU成本 + 网络成本 + 内存成本
     

   • 成本单位为优化器内部单位（非实际时间或资源消耗）。

5. 执行计划固化（Plan Fixation）

• SQL Profile：通过DBMS_SQLTUNE捕获并存储优化器建议，强制使用特定计划。
• SQL Plan Baseline：记录已验证的执行计划，防止性能回退（Oracle 11g+）。
• SQL Patch：动态修改SQL以绕过优化器错误（如绑定变量窥探问题）。

三、优化器关键算法

1. 代价估算模型：

   • 选择性估算：基于列统计信息（如直方图）计算谓词过滤后的行数比例。
   • 基数估算（Cardinality Estimation）：预测操作（如连接、分组）的输出行数。
   • 示例：

     -- 假设employees表有1000行，dept_id列有10个不同值
     SELECT * FROM employees WHERE dept_id = 10;
     -- 优化器估算选择性=1/10，返回行数=1000*0.1=100
     

2. 自适应查询优化（Adaptive Query Optimization, Oracle 12c+）：

   • 自适应连接方法：在执行时根据实际数据分布切换连接方式（如哈希连接→嵌套循环）。
   • 自适应统计信息：动态调整统计信息以应对数据倾斜。
   • 自动重优化（Automatic Re-Optimization）：对多次执行的SQL，根据实际性能反馈调整计划。

四、优化器诊断与调优

1. 执行计划查看：

   • 使用EXPLAIN PLAN FOR或DBMS_XPLAN.DISPLAY生成并格式化计划。
   • 关键字段：Operation（操作类型）、Name（对象名）、Cost（成本）、Cardinality（估算行数）。

2. 优化器跟踪：

   • 开启10053事件跟踪优化器决策细节：

     ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
     -- 执行SQL后，跟踪日志写入用户转储目录（UDUMP）
     

3. 常见问题与解决：

   • 统计信息过时：定期运行DBMS_STATS.GATHER_TABLE_STATS。
   • 绑定变量窥探：使用OPT_PARAM('optimizer_adaptive_features', 'false')或SQL Patch。
   • 参数敏感计划：避免频繁修改OPTIMIZER_INDEX_COST_ADJ等参数。

五、示例：优化器决策流程

假设执行以下SQL：

SELECT e.name, d.dept_name 
FROM employees e JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary > 5000 AND d.location = 'NY';

优化器可能按以下步骤决策：

1. 解析与转换：无复杂转换需求。
2. 统计信息检查：
   • employees表：100万行，salary > 5000选择性=0.2（20万行）。
   • departments表：1000行，location = 'NY'选择性=0.1（100行）。
3. 连接顺序选择：
   • 方案1：先扫描employees（20万行），再哈希连接departments（100行）。
   • 方案2：先扫描departments（100行），再嵌套循环连接employees（20万行）。
   • 成本比较：方案1的I/O成本更低（哈希连接适合大表驱动小表）。
4. 执行计划生成：

   HASH JOIN
     TABLE ACCESS FULL employees (Cost=1000, Cardinality=200000)
     TABLE ACCESS BY INDEX ROWID departments (Cost=50, Cardinality=100)
       INDEX RANGE SCAN idx_dept_location (Cost=10, Cardinality=100)
   

总结

Oracle优化器的核心是通过统计信息和成本模型生成最优执行计划，其决策受数据分布、硬件配置和参数设置影响。理解优化器的工作原理有助于诊断性能问题（如全表扫描误用、连接顺序不当），并通过统计信息更新、SQL重写或提示（Hints）引导优化器选择更优计划。