DB / Oracle / 性能诊断

Oracle 优化器 Hint 详解

2026.09.06

Oracle 优化器 Hint 详解

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

1. 概述

Hint 是控制优化器的指令[1]：

优势：

  • 精准控制
  • 强制计划
  • 临时优化

风险：

  • 失效后错误
  • 维护困难

2. Hint 语法

2.1 基本语法

SELECT /*+ HINT_NAME */ ...
SELECT /*+ HINT_NAME(table) */ ...
SELECT /*+ HINT_NAME(table alias) */ ...

2.2 多个 HINT

SELECT /*+ INDEX(e idx_name) PARALLEL(e 4) */ ...

3. 优化器 HINT

3.1 优化器模式

/*+ ALL_ROWS */        -- 吿化（默认）
/*+ FIRST_ROWS(n) */   -- 前N行快
/*+ FIRST_ROWS_1 */    -- 响应快
/*+ CHOOSE */          -- 选择
/*+ RULE */            -- RBO（不推荐）

3.2 示例

SELECT /*+ FIRST_ROWS(10) */ * FROM employees WHERE dept_id = 10;

4. 访问路径 HINT

4.1 全表扫描

/*+ FULL(table) */

4.2 索引

/*+ INDEX(table idx_name) */
/*+ INDEX(table) */                    -- 任一索引
/*+ INDEX_ASC(table idx_name) */       -- 升序
/*+ INDEX_DESC(table idx_name) */      -- 降序
/*+ INDEX_FFS(table idx_name) */       -- 快速全扫描
/*+ NO_INDEX(table idx_name) */

4.3 示例

SELECT /*+ INDEX(e idx_emp_dept) */ * 
FROM employees e WHERE dept_id = 10;

5. JOIN HINT

5.1 JOIN 方法

/*+ USE_NL(table1 table2) */     -- Nested Loop
/*+ USE_HASH(table1 table2) */   -- Hash Join
/*+ USE_MERGE(table1 table2) */  -- Sort Merge
/*+ NO_USE_NL(table1 table2) */
/*+ NO_USE_HASH(table1 table2) */

5.2 JOIN 顺序

/*+ LEADING(table1 table2) */
/*+ ORDERED */   -- 按 FROM 顺序

5.3 示例

SELECT /*+ LEADING(d e) USE_NL(e) */ *
FROM employees e, departments d
WHERE e.dept_id = d.id;

6. 并行 HINT

/*+ PARALLEL(table 8) */
/*+ PARALLEL(table) */              -- 自动 DOP
/*+ NO_PARALLEL(table) */
/*+ PARALLEL_INDEX(table idx 4) */

6.1 PDML

ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(t 8) APPEND */ INTO t ...

详细见：Oracle 并行查询 Parallel Query。

7. 其他 HINT

7.1 APPEND

/*+ APPEND */    -- 直接路径 INSERT
/*+ APPEND_VALUES */

7.2 Cache

/*+ CACHE(table) */     -- 缓存
/*+ NOCACHE(table) */

7.3 Query Rewrite

/*+ REWRITE(mv_name) */
/*+ NO_REWRITE */

7.4 Result Cache

/*+ RESULT_CACHE */
/*+ NO_RESULT_CACHE */

7.5 Cardinality

/*+ CARDINALITY(table 1000) */    -- 强制基数

7.6 Monitoring

/*+ MONITOR */
/*+ NO_MONITOR */

8. HINT 验证

8.1 执行计划

EXPLAIN PLAN FOR SELECT /*+ INDEX(e idx) */ * FROM employees e WHERE ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

8.2 是否生效

Note
-----
- SQL plan baseline used
- HINT 应该体现在计划

9. HINT 失效原因

9.1 语法错误

-- 1. HINT 拼写错
-- 2. 别名错
-- 3. 索引名错

9.2 不兼容

-- 1. PARALLEL + RBO
-- 2. 多个冲突 HINT

9.3 优化器无法

-- 1. 索引不存在
-- 2. 视图无法 push

10. 常见 HINT 应用

10.1 强制索引

SELECT /*+ INDEX(e idx_emp_dept) */ *
FROM employees e WHERE dept_id = 10;

10.2 强制 Hash Join

SELECT /*+ USE_HASH(a b) PARALLEL(a 4) PARALLEL(b 4) */ *
FROM big_table_a a, big_table_b b WHERE a.id = b.id;

10.3 固定 JOIN 顺序

SELECT /*+ LEADING(d e) USE_NL(e) */ *
FROM departments d, employees e
WHERE e.dept_id = d.id AND d.location = 'NY';

10.4 并行查询

SELECT /*+ PARALLEL(s 8) */ SUM(amount) FROM sales s WHERE ...;

10.5 直接路径加载

INSERT /*+ APPEND PARALLEL(t 8) */ INTO target t
SELECT * FROM source;

11. SQL Plan Baseline 优先

-- SQL Plan Baseline 固定计划，比 HINT 稳定
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '&sql_id');

详细见：Oracle SQL Plan Baseline。

12. 常见坑与排错

12.1 HINT 不生效

-- 1. 检查语法
-- 2. 检查别名
-- 3. 检查对象存在
-- 4. 检查不冲突

12.2 优化器忽略

-- 1. 统计信息
-- 2. 索引状态
-- 3. SQL Plan Baseline

12.3 计划不稳定

-- 1. SQL Profile
-- 2. SQL Plan Baseline
-- 3. 固定计划

13. 最佳实践

  1. 谨慎使用 HINT：特殊情况
  2. 优先 SQL Profile/Baseline：稳定
  3. HINT 验证：生效
  4. 文档化：维护
  5. 定期复审：失效
  6. 统计信息：基础
  7. 索引优先：自然
  8. HINT 别名正确：生效
  9. 测试计划：验证
  10. 替代方案：长期

14. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Hints” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comments.html