Oracle Hint

  Oracle Hints 是一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划，它允许用户在 SQL 语句中插入相关的语法，从而影响 SQL 的执行方式。

Hint 的作用

  1. 调整目标 SQL 使用的优化器类型
  2. 调整优化器的优化目标
  3. 调整表的访问路径
  4. 调整表的连接类型
  5. 调整表的连接顺序
  6. 提高语句的并行程度

Hint 的弊端

  • Hint 是比较”暴力”的一种解决方式，不是很优雅。需要开发人员手工修改代码。
  • Hint 不会去适应新的变化，只是在现有的基础上产生更优的执行计划。
  • Hint 随着数据库版本的变化，可能会有一些差异、甚至废弃的情况。

Oracle stored outline

  Oracle stored outline（存储提纲）是最早的基于提示来控制 SQL 执行计划的机制，也是 9i 以及之前版本唯一可以用来稳定和控制 SQL 执行计划的工具。主要的作用是用来保持固定 sql 语句的执行计划，让 sql 语句的执行计划在数据库环境变更（统计信息、优化器参数、表结果变更等）后还能保持原来的执行计划。

使用场景

  1. 避免 SQL 的执行计划在统计数据不准确的情况（如未能及时收集表或索引的统计信息）下导致变化从而引起的性能降低。
  2. 避免容易因为 Bind Peeking 导致 SQL 执行计划变差从而引起的性能降低。
  3. 避免大规模分布实施的应用出现数据库版本、配置等区别引起的优化器产生不同的执行计划。

注意事项

  • 从 10g 以后，outline 基本已经被 Oracle 废弃并且不在维护。
  • 只有设置 use_stored_outlines 参数后才能启用 outline。
  • 创建 outline 需要 create any outline or execute_catelog_role 权限 。

SQL Profile

  SQL Profile 是 stored outline 的进化版，是为目标 SQL 提供除了统计信息之外的其他信息，比如运行环境、更准确的执行路径等，以帮助优化器为 SQL 语句选择更合适的执行计划。

优点

  1. 更容易生成、更改和控制
  2. 适用范围广
  3. 在不改变目标 SQL 文本的情况下达到稳定执行计划的效果

类型

  • Automatic 类型的 SQL Profile
    Automatic 类型的 SQL Profile 其实是目标 SQL 一些额外的调整信息，这些信息存储在数据字典中。Oracle 会使用类似于动态采样的方法来支持 Automatic 类型的 SQL Profile，在生成执行计划的时候 Oracle 会将调整信息同原 SQL 的相关统计信息等内容一起作用从而得到最新的执行计划
  • Manual 类型的 SQL Profile
    Manual 类型 SQL Profile 的本质是一堆来源 Outline Data 部分的 Hint 组合，可以在不改变目标 SQL 文本的情况下调整其执行计划

说明

  SQL Profile 实际上只是一种亡羊补牢、被动的技术手段，应用在那些执行计划已经发生了不好的变更的 SQL 上，即当我们发现这些 SQL 的执行计划已经出了问题时通过创建 SQL Pofle 来纠正、稳定这些 SQL 的执行计划。也就是说，即便通过创建 SQLProfile 解决了目标 SQL 执行计划变更的问题，依然不能保证系统后续执行的 SQL 的执行计划就不再发生不好的变更。

SPM（SQL Plan Management）

  SPM 是一种主动的稳定执行计划的手段，能够保证只有被验证过的执行计划才会被启用。SPM 既能够主动地稳定执行计划，又保留了继续使用新的执行效率可能更高的执行计划的机会。 SPM 启动后，每个 SQL 的所有执行计划都有对应的 SQL Plan Baseline，可以通过 DBA_SQL_PLAN_BASELINE 查看。只有 DBA_SQL_PLAN_BASELINE 表中 ENABLE 和 ACCEPTED 列的值均为’YES’的 SQL Plan Baseline 对应的执行计划才会被 Oracle 启用。

获取方法

  • 自动捕获（Automatic Initial Plan Capture）
  1. 设置 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = Ture
  2. Oracle 自动监控执行两次以上的 SQL 语句，将执行计划记入 Plan History。
  3. 生成的第一个执行计划被标记为 ENABLED 并且是 ACCEPTED，后续的执行计划会被标记为 ENABLED 但不是 ACCEPTED。
  4. 后续如果关闭了自动捕捉，针对存在 baseline 的 SQL，仍旧会有新的 PLAN 生成，新的 Plan 仍会进入 Plan History，标记为 ENABLED 但不是 ACCEPTED。
  • 手动导入（Manual Plan Capture）
    Oracle 支持通过 DBMS_SPM 包手动管理 SPM，可以将 SQL 执行计划从高速缓存或现有的 SQL 优化集中加载到 SPM。