Oracle SQL Plan Directive 详解

适用版本：Oracle Database 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

SQL Plan Directive 是优化器自动学习的额外信息[1]：

2. 原理

2.1 自动创建

- 优化器发现估计不准
- 自动创建 Directive
- 记录列相关性
- 改进未来估计

2.2 类型

- 列组相关性
- 直方图建议
- 扩展统计建议

3. 查看

3.1 视图

SELECT directive_id, type, state, last_used, last_modified
FROM dba_sql_plan_directives;

SELECT * FROM dba_sql_plan_dir_objects;

3.2 详细

SELECT d.directive_id, d.type, d.state, d.reason,
       o.owner, o.object_name, o.col_name, o.col_group
FROM dba_sql_plan_directives d, dba_sql_plan_dir_objects o
WHERE d.directive_id = o.directive_id;

4. 状态

4.1 USABLE

- 可用
- 优化器使用

4.2 SUPERSEDED

- 被替代
- 扩展统计已创建
- 不再使用

5. 管理

5.1 刷新

-- 重新解析
EXEC DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE;

5.2 导出

-- 导出
DECLARE
  v_clob CLOB;
BEGIN
  v_clob := DBMS_SPD.PACK_STGTAB_DIRECTIVE(
    table_name => 'SPD_TAB',
    directive_id => '...'
  );
END;
/

5.3 导入

EXEC DBMS_SPD.UNPACK_STGTAB_DIRECTIVE('SPD_TAB');

6. 与扩展统计

6.1 关系

- Directive 建议扩展统计
- DBMS_STATS 创建扩展统计
- Directive 状态变 SUPERSEDED

6.2 创建扩展统计

-- 列组
SELECT dbms_stats.create_extended_stats('SCOTT', 'EMP', '(job, dept_id)') 
FROM dual;

-- 函数统计
SELECT dbms_stats.create_extended_stats('SCOTT', 'EMP', '(UPPER(name))') 
FROM dual;

7. 与 SQL Profile

7.1 区别

- Directive：对象级
- Profile：SQL 级
- Baseline：SQL+计划级

7.2 协作

- Directive 改进统计
- Profile 调整计划
- Baseline 固定计划

详细见：Oracle SQL Profile 详解、Oracle SQL Plan Baseline 基线。

8. 应用场景

8.1 列相关性

- 列相关
- 估计不准
- Directive 改进

8.2 数据倾斜

- 直方图
- Directive 建议
- DBMS_STATS 收集

8.3 复杂查询

- 多列条件
- 估计偏差
- Directive 校正

9. 性能影响

9.1 优化器

- 改进估计
- 更好的计划
- 自动

9.2 开销

- 解析时
- 轻微
- 监控

10. 常见问题

10.1 不生效

- 状态检查
- USABLE
- 刷新

10.2 过多

- 大量 Directive
- 监控
- 清理无效

11. 监控

11.1 使用

SELECT directive_id, last_used
FROM dba_sql_plan_directives
ORDER BY last_used DESC;

11.2 效果

-- 对比执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

12. 最佳实践

自动：允许
扩展统计：DBMS_STATS
监控：使用
测试：效果
不要禁用：除非必要
文档：记录
清理：过期
演练：定期
版本：12c+
升级：迁移

13. 参考资料

[1] Oracle Database SQL Tuning Guide 19c, “SQL Plan Directives” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/