Oracle HINT 别乱加！常用 HINT 速查与踩坑

mahan

先说一个大前提
HINT 不是银弹，它是你跟优化器吵架的工具。吵架之前得先确认两件事：

统计信息是最新的（DBMS_STATS.GATHER_TABLE_STATS 跑过没？）
执行计划确实有问题（看过了没？）
统计信息过期导致的烂计划，加 HINT 是治标不治本。先收统计信息，再看要不要上 HINT。

场景速查卡：你遇到了什么问题？
你遇到的问题        用哪个 HINT        看执行计划关注什么
走了烂索引回表太多，不如全表扫        /*+FULL(t)*/        INDEX RANGE SCAN → TABLE ACCESS FULL
优化器没选最优索引        /*+INDEX(t idx_name)*/        换成你指定的索引
分页取最新数据        /*+INDEX_DESC(t idx_time)*/ + ROWNUM        INDEX RANGE SCAN DESCENDING
只查索引列，不想回表        /*+INDEX_FFS(t idx_name)*/        INDEX FAST FULL SCAN
禁止走某个烂索引        /*+NO_INDEX(t idx_name)*/        看走的路径是否符合预期
驱动表选错了 / 多表顺序不对        /*+LEADING(t1 t2 t3)*/        JOIN 顺序是否改变
小结果集驱动大表，大表有索引        /*+USE_NL(t)*/        NESTED LOOPS
大表 JOIN 大表，等值连接        /*+USE_HASH(t1 t2)*/        HASH JOIN
大表扫描太慢        /*+PARALLEL(t 4)*/        PX SEND / PX RECEIVE
批量 INSERT 太慢        /*+APPEND*/        DIRECT PATH INSERT
有 DG 的环境        确保 LOGGING 模式        别 NOLOGGING
分页首页要快        /*+FIRST_ROWS(20)*/        INDEX SCAN 优先返回
预估行数和实际差距大        /*+GATHER_PLAN_STATISTICS*/        E-Rows vs A-Rows
怀疑表有坏块        /*+FULL(t)*/ + 逐行遍历        报错里的 file# 和 block#
查静态小表太频繁        /*+RESULT_CACHE*/        RESULT CACHE 操作出现
线上 SQL 需要强制监控        /*+MONITOR*/        DBMS_SQL_MONITOR.REPORT_SQL_MONITOR
找到你要的了？往下看详细用法和案例。没找到？评论区说你的场景，下次可能就补上了。

测试环境搭建
想动手跑下面的案例，先建这几张表。数据量不需要太大，够看出执行计划差异就行。

-- Oracle 11g+ 环境
CREATE TABLE dept (
    dept_no   VARCHAR2(10) PRIMARY KEY,
    dept_name VARCHAR2(50) NOT NULL,
    location  VARCHAR2(30)
);

CREATE TABLE emp (
    emp_no    VARCHAR2(20) PRIMARY KEY,
    emp_name  VARCHAR2(50) NOT NULL,
    dept_no   VARCHAR2(10),
    salary    NUMBER(10,2),
    hire_date DATE,
    status    VARCHAR2(10) DEFAULT 'ACTIVE',
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_no) REFERENCES dept(dept_no)
);

CREATE TABLE orders (
    order_id    NUMBER PRIMARY KEY,
    customer_id NUMBER NOT NULL,
    order_date  DATE NOT NULL,
    amount      NUMBER(12,2),
    create_time TIMESTAMP DEFAULT SYSTIMESTAMP,
    status      VARCHAR2(20) DEFAULT 'COMPLETED'
);

CREATE TABLE customer (
    cust_id     NUMBER PRIMARY KEY,
    cust_name   VARCHAR2(50) NOT NULL,
    region      VARCHAR2(30)
);

CREATE TABLE order_detail (
    detail_id   NUMBER PRIMARY KEY,
    order_id    NUMBER NOT NULL,
    product_name VARCHAR2(100),
    quantity     NUMBER,
    CONSTRAINT fk_detail_order FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- 关键索引
CREATE INDEX idx_emp_dept_sal ON emp(dept_no, salary);
CREATE INDEX idx_emp_status   ON emp(status);
CREATE INDEX idx_emp_dept     ON emp(dept_no);
CREATE INDEX idx_order_ctime  ON orders(create_time);
CREATE INDEX idx_order_cust   ON orders(customer_id, order_date);
CREATE INDEX idx_order_status ON orders(status);  -- 选择性差的索引，FULL 案例用

-- 插入测试数据
-- 1. 先插入 dept（无外键依赖）
BEGIN
  FOR i IN 1..50 LOOP
    INSERT INTO dept(dept_no, dept_name, location) VALUES ('D'||i, 'Dept_'||i, CASE WHEN MOD(i,3)=0 THEN 'BEIJING' WHEN MOD(i,3)=1 THEN 'SHANGHAI' ELSE 'GUANGZHOU' END);
  END LOOP;
  COMMIT;
END;
/

-- 2. 插入 customer（无外键依赖）
BEGIN
  FOR i IN 1..2000 LOOP
    INSERT INTO customer(cust_id, cust_name, region) VALUES (i, 'Cust_'||i, CASE WHEN MOD(i,5)=0 THEN 'NORTH' ELSE 'SOUTH' END);
  END LOOP;
  COMMIT;
END;
/

-- 3. 插入 emp（依赖 dept）
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO emp(emp_no,emp_name,dept_no,salary,hire_date,status)
    VALUES('E'||i,'Emp_'||i,'D'||(MOD(i,50)+1),
           5000+MOD(i,50)*200,
           ADD_MONTHS(SYSDATE,-MOD(i,120)),
           DECODE(MOD(i,100),0,'LEAVE','ACTIVE'));
  END LOOP;
  COMMIT;
END;
/

-- 4. 插入 orders（依赖 customer）
BEGIN
  FOR i IN 1..50000 LOOP
    INSERT INTO orders(order_id, customer_id, order_date, amount, create_time, status)
    VALUES (i, MOD(i,2000)+1, ADD_MONTHS(SYSDATE, -MOD(i,24)), 100+MOD(i,500)*10,
            SYSTIMESTAMP - MOD(i,10000)/24,
            DECODE(MOD(i,10), 0, 'CANCELLED', 1, 'PENDING', 'COMPLETED'));
  END LOOP;
  COMMIT;
END;
/

-- 5. 插入 order_detail（依赖 orders）
BEGIN
  FOR i IN 1..150000 LOOP
    INSERT INTO order_detail(detail_id, order_id, product_name, quantity) VALUES (i, MOD(i,50000)+1, 'Product_'||MOD(i,100), MOD(i,10)+1);
  END LOOP;
  COMMIT;
END;
/

-- 6. APPEND 案例专用：订单历史归档表（结构跟 orders 一样）
CREATE TABLE orders_history (
    order_id    NUMBER PRIMARY KEY,
    customer_id NUMBER NOT NULL,
    order_date  DATE NOT NULL,
    amount      NUMBER(12,2),
    create_time TIMESTAMP DEFAULT SYSTIMESTAMP,
    status      VARCHAR2(20) DEFAULT 'COMPLETED'
);

-- 7. RESULT_CACHE 案例专用：省份代码表（50 条，几乎不变）
CREATE TABLE province_code_table (
    province_code VARCHAR2(10) PRIMARY KEY,
    province_name VARCHAR2(50) NOT NULL
);
BEGIN
  FOR i IN 1..50 LOOP
    INSERT INTO province_code_table VALUES ('P'||i, 'Province_'||i);
  END LOOP;
  COMMIT;
END;
/

-- 注意：坏块案例中的 suspect_table 不在建表脚本里。
-- 那个案例是演示如何处理已有坏块的表，你不能故意造坏块来测试。
-- 如需验证 FULL HINT 的基本效果，可以用 orders 表替代：
-- SELECT /*+FULL(o)*/ COUNT(*) FROM orders o;
收集统计信息（建完表后执行）：

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'DEPT');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMER');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDER_DETAIL');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS_HISTORY');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PROVINCE_CODE_TABLE');

SELECT 'dept' AS table_name, COUNT(*) AS row_count FROM dept
UNION ALL
SELECT 'emp', COUNT(*) FROM emp
UNION ALL
SELECT 'customer', COUNT(*) FROM customer
UNION ALL
SELECT 'orders', COUNT(*) FROM orders
UNION ALL
SELECT 'order_detail', COUNT(*) FROM order_detail
UNION ALL
SELECT 'orders_history', COUNT(*) FROM orders_history
UNION ALL
SELECT 'province_code_table', COUNT(*) FROM province_code_table;

TABLE_NAME             ROW_COUNT
------------------- ----------
dept                            50
emp                         10000
customer                  2000
orders                         50000
order_detail                150000
orders_history                     0
province_code_table            50

关于执行计划查看方式的说明：

本文案例使用 EXPLAIN PLAN FOR 演示执行计划变化。它生成的是预估计划（不实际执行 SQL），足以判断 HINT 是否生效、访问路径和连接方式是否改变。

但在生产环境诊断线上 SQL 时，务必用 DBMS_XPLAN.DISPLAY_CURSOR 获取真实执行计划——它能告诉你实际行数与预估行数的偏差，这是 EXPLAIN PLAN FOR 做不到的：

-- 生产环境取真实计划（sql_id 从 v$sql 里拿）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));
绑定变量的坑： EXPLAIN PLAN FOR 不支持绑定变量窥视（bind variable peeking）。如果你的生产 SQL 用了绑定变量（WHERE dept_no = :b1），用 EXPLAIN PLAN FOR 看到的计划可能与真实执行完全不同，因为它不知道绑定变量的值。这时必须用 DISPLAY_CURSOR 查真实计划。

环境搭好了，下面开始讲每个 HINT 的用法和案例。

一、访问路径控制：走全表还是走索引
1. /*+FULL(table)*/ — 强制全表扫描
场景： 订单系统做月结报表，查一个月内所有订单金额合计。status 字段上有个索引（idx_order_status），选择性差——80% 的订单都是 COMPLETED，优化器非要走这个索引再回表，每行都回一次，IO 比直接全表扫还多。

-- 不加 HINT：优化器选了 idx_order_status，回表代价巨大
EXPLAIN PLAN FOR
SELECT COUNT(*), SUM(amount) FROM orders WHERE status = 'COMPLETED';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 加 FULL：全表扫，多块读，一趟搞定
EXPLAIN PLAN FOR
SELECT /*+FULL(o)*/ COUNT(*), SUM(amount) FROM orders o WHERE o.status = 'COMPLETED';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
什么时候加： 选择性差的索引（回表代价 > 全表扫）、小表（几百行全表扫比索引快）、统计信息不准导致优化器误选索引。

什么时候别加： 大表 + 选择性好的索引。这时候如果优化器坚持走全表扫，先检查统计信息是否过期，确认索引选择性后再考虑强制 /*+INDEX*/。

另一个用途：查坏块。 怀疑表有坏块（ORA-01578）时，用 FULL 做全表扫描来定位坏块位置：

-- 全表扫碰坏块：COUNT(*) 或逐行 SELECT 都行
-- 碰到坏块时报 ORA-01578，给出 file_id 和 block_id
SELECT /*+FULL(t)*/ COUNT(*) FROM suspect_table t;
-- 但 COUNT(*) 不一定会报错停止：如果坏块恰好在数据块空闲区域，
-- Oracle 可能跳过坏块返回一个比实际小的数字，不报 ORA-01578。
-- 想更彻底，用逐行 SELECT 让应用程序遍历每一行：
SELECT /*+FULL(t)*/ * FROM suspect_table t;
最准确的查坏块方法是用 DBMS_REPAIR.CHECK_OBJECT，它会逐块检查并报告所有坏块，不依赖全表扫描的行为：

SET SERVEROUTPUT ON
DECLARE
  v_corrupt  NUMBER;
BEGIN
  DBMS_REPAIR.CHECK_OBJECT(
    schema_name => USER,
    object_name => 'SUSPECT_TABLE',
    corrupt_count => v_corrupt
  );
  DBMS_OUTPUT.PUT_LINE('Corrupt blocks: ' || v_corrupt);
END;
/
2. /*+INDEX(table index_name)*/ — 强制走指定索引
场景： 告警系统里，按部门+薪水查员工。表上有两个索引：idx_emp_dept（单列）和 idx_emp_dept_sal（组合），优化器选了单列索引 idx_emp_dept，导致还要过滤 salary，不如直接走组合索引一步到位。

-- 优化器选了 idx_emp_dept（单列），再多读一轮过滤 salary
EXPLAIN PLAN FOR
SELECT e.emp_no, e.emp_name FROM emp e WHERE e.dept_no = 'D5' AND e.salary > 8000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 强制走组合索引，一次索引扫描搞定两个条件
EXPLAIN PLAN FOR
SELECT /*+INDEX(e idx_emp_dept_sal)*/ e.emp_no, e.emp_name FROM emp e WHERE e.dept_no = 'D5' AND e.salary > 8000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
什么时候加： 优化器没选最优索引（选了前缀索引而非组合索引、选了选择性差的索引）、需要精确控制访问路径。

什么时候别加： 索引选择性真的不好时。这时候走索引比全表扫更慢，该用 /*+FULL*/ 或者先收统计信息让优化器自己选。

变体：INDEX_DESC

分页取最新数据，最经典场景：

-- 取最近 10 条订单，INDEX_DESC + ROWNUM 组合
SELECT /*+INDEX_DESC(o idx_order_ctime)*/ o.order_id, o.customer_id, o.amount
FROM   orders o
WHERE  o.create_time IS NOT NULL
AND    ROWNUM <= 10;
INDEX_DESC 的隐藏陷阱： 即使你加了 IS NOT NULL，Oracle 有时候仍然不走 INDEX RANGE SCAN DESCENDING，而是选 FULL SCAN + SORT ORDER BY + COUNT STOPKEY。原因是优化器觉得全表扫+排序的成本比降序索引扫描更低。

验证方法——加了 HINT 后一定要确认执行计划：

-- 执行后确认是否真的走了 INDEX RANGE SCAN DESCENDING
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
-- 如果看到 SORT ORDER BY + STOPKEY，说明没走降序索引
更稳的做法：给列加 NOT NULL 约束（而不是只在 WHERE 里过滤），这样优化器确定索引覆盖所有行，更容易选降序扫描：

ALTER TABLE orders MODIFY create_time TIMESTAMP NOT NULL;
3. /*+INDEX_FFS(table index_name)*/ — 快速全索引扫描
场景： 部门人数统计。只查 dept_no 和 emp_no，这两个字段刚好都在组合索引 idx_emp_dept_sal 的前两列里。走 INDEX_FFS 直接把索引当小表扫，不回表。

-- INDEX_FFS：索引全扫，不回表，适合覆盖索引场景
EXPLAIN PLAN FOR
SELECT /*+INDEX_FFS(e idx_emp_dept)*/ e.emp_no, e.dept_no FROM emp e WHERE e.dept_no = 'D5';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
什么时候加： 查询列全在索引里（覆盖索引），且索引远小于表。

什么时候别加： 需要排序结果时。INDEX_FFS 是无序的多块读，不能替代 ORDER BY，这时候该用普通 INDEX SCAN。

4. /*+NO_INDEX(table index_name)*/ — 禁止走某索引
场景： emp 表上有个 idx_emp_status 索引，status 只有 ACTIVE/LEAVE 两个值，选择性极差（99% 是 ACTIVE）。优化器经常误选这个索引，你想让它别走这个，但不指定走哪个——让优化器自己选其他路径。

-- 禁止走 idx_emp_status，让优化器选其他路径
EXPLAIN PLAN FOR
SELECT /*+NO_INDEX(e idx_emp_status)*/ e.emp_no, e.emp_name
FROM   emp e WHERE e.status = 'ACTIVE' AND e.dept_no = 'D5';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
什么时候加： 知道某个索引是坑，想排除它但不指定替代路径。

什么时候别加： 大表 + 不指定索引名。因为 NO_INDEX(e) 不带索引名时，会禁止该表所有索引，等于强制全表扫描：

-- 这等于 FULL(e)，但你不一定意识到
SELECT /*+NO_INDEX(e)*/ * FROM emp e WHERE e.status = 'ACTIVE';
大表上用这种写法是灾难。如果要排除所有索引不如直接写 /*+FULL(e)*/，至少意图明确。

二、连接顺序与方法：多表 JOIN 怎么连
这是性能差距最大的区域。驱动表选错了，同一个查询从 0.1 秒变成 30 秒很常见。

5. /*+LEADING(t1 t2 t3...)*/ — 指定多表连接顺序
LEADING 不只是指定"哪个表做驱动表"，它可以指定多张表的连接顺序。这是它的核心能力，很多人只用了单表版本，没发挥出真正的威力。

场景一：三表关联查员工信息。 emp 过滤后只剩几十行，dept 和 orders 都是大表。优化器把 orders 放前面做驱动表（因为表最大），全扫 50000 行再关联，直接崩了。

-- 优化器选了 orders 做驱动表（表最大），慢
EXPLAIN PLAN FOR
SELECT e.emp_name, d.dept_name, o.amount
FROM   emp e, dept d, orders o
WHERE  e.dept_no = d.dept_no
AND    e.emp_no = 'E5'
AND    o.customer_id = 5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- LEADING 指定三表顺序：先扫 emp（1行），再关联 dept（1行），再关联 orders
EXPLAIN PLAN FOR
SELECT /*+LEADING(e d o)*/ e.emp_name, d.dept_name, o.amount
FROM   emp e, dept d, orders o
WHERE  e.dept_no = d.dept_no
AND    e.emp_no = 'E5'
AND    o.customer_id = 5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
场景二：用户查自己的订单列表。 这比教科书里的 dept=3 行更贴近实际业务——用户点进"我的订单"，customer 过滤后只有 1 行，拿这 1 行去 NL probe orders 和 order_detail，秒出结果。

-- 用户 12345 查最近一个月的订单明细
SELECT /*+LEADING(c) USE_NL(o d)*/ o.order_id, d.product_name, d.quantity
FROM   customer c, orders o, order_detail d
WHERE  c.cust_id = 12345
AND    c.cust_id = o.cust_id
AND    o.order_id = d.order_id
AND    o.order_date >= ADD_MONTHS(SYSDATE, -1);
不加 HINT 时，优化器可能拿 orders 做驱动表（5 万行），每行都去 probe customer（1 行），完全反了。

什么时候加： 你清楚哪张表过滤后行数最少，知道最优连接顺序，优化器选反了。

什么时候别加： 不确定各表过滤后行数时。加错顺序比不加更慢。这时先收统计信息，让优化器自己选，或者用 /*+GATHER_PLAN_STATISTICS*/ 看 A-Rows 确认各表实际行数后再加。

LEADING vs ORDERED： ORDERED 按 FROM 子句顺序连接，LEADING 可以在 HINT 里指定顺序不用改 FROM。LEADING 更灵活、更好维护。ORDERED 的缺点是 FROM 里表顺序一变效果就变了，SQL 重构容易踩坑。

6. /*+USE_NL(table)*/ — 嵌套循环连接
场景： 查北京地区某部门的员工。dept 过滤后只剩几行（北京只有 3 个部门），emp 上有 idx_emp_dept 索引。NL：dept 每出一行，到 emp 索引里精确匹配，3 次 probe 搞定。

-- dept 过滤后只有几行，emp 有索引，NL 最快
EXPLAIN PLAN FOR
SELECT /*+LEADING(d) USE_NL(e)*/ d.dept_name, e.emp_name
FROM   dept d, emp e
WHERE  d.dept_no = e.dept_no
AND    d.location = 'BEIJING';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
不加 HINT 时，优化器可能选 Hash Join（先把两张表都全扫一遍再在内存里匹配），对于这个小结果集场景，Hash Join 的全表扫描是浪费。

什么时候加： 驱动表过滤后行数少（几十到几百行），被驱动表上有可用索引。在线交互查询需要快速返回第一行时。

什么时候别加： 驱动表过滤后行数多时。每行都 probe 一次被驱动表，10 万行 × 单次 probe = 慢到窒息。这时候该用 /*+USE_HASH*/。

7. /*+USE_HASH(t1 t2)*/ — 哈希连接
场景： 年底跑员工薪资汇总报表。emp 和 dept 等值连接，dept 上没有合适索引做 NL probe。Hash Join：把较小的 dept 在 PGA 里建 Hash 表，emp 全扫去匹配，一趟搞定。

-- 两张大表等值连接，Hash Join 是正确选择
EXPLAIN PLAN FOR
SELECT /*+USE_HASH(e d)*/ d.dept_name, COUNT(*), AVG(e.salary)
FROM   emp e, dept d
WHERE  e.dept_no = d.dept_no
GROUP  BY d.dept_name;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
什么时候加： 大表 JOIN 大表、等值连接且没有合适索引、批量处理和报表场景。

什么时候别加： 非等值连接（>、<、BETWEEN）Hash Join 不支持，该用 NL 或 Merge Join；PGA 太小时 Hash 表放不下会 spill 到 temp 表空间，性能断崖下降——这时要么调大 PGA，要么改用 NL。

怎么判断 Hash Join 是否溢出到磁盘：

-- 执行后看执行计划的 TempSpc 信息
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
-- 如果看到 "Max Tempseg Size: 500M" 之类的信息，说明 Hash Join 溢出到磁盘了
-- 或者看 v$sql_workarea_histogram
SELECT sql_id, operation_type, optimal_executions, onepass_executions, multipass_executions
FROM   v$sql_workarea WHERE sql_id = '&your_sql_id';
-- onepass_executions > 0 说明溢出了一次，multipass_executions > 0 说明溢出多次，性能很差
之前遇到 pga_aggregate_target 设太小，Hash Join 溢出到 temp，一条报表跑 20 分钟。调大 PGA 后 40 秒搞定。上线前确认 PGA 参数够用。

8. /*+USE_MERGE(t1 t2)*/ — 排序合并连接
实际用得少。Hash Join 在绝大多数场景比 Merge Join 快，除非数据已经按连接列有序（比如刚做过 INDEX ASC 扫描），能省掉排序开销。需要时再查文档，优先用 Hash Join。

三、并行控制：大表跑不动的救命稻草
9. /*+PARALLEL(table degree)*/ — 并行查询
场景： 日终清算，统计全天 50 万笔订单的金额分布。单线程全表扫 + 聚合跑了 3 分钟。开 4 个并行度后，40 秒完成。

-- 不并行：慢
EXPLAIN PLAN FOR
SELECT COUNT(*), AVG(amount) FROM orders WHERE order_date >= TRUNC(SYSDATE) - 30;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 并行 4：快
EXPLAIN PLAN FOR
SELECT /*+PARALLEL(o 4)*/ COUNT(*), AVG(amount) FROM orders o WHERE o.order_date >= TRUNC(SYSDATE) - 30;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);  -- 看到 PX SEND / PX RECEIVE 操作
什么时候加： 全表扫描大表、大表聚合 GROUP BY、大表 JOIN 大表、数据仓库批处理场景。

什么时候别加： OLTP 系统。之前有开发在报表 SQL 加了 /*+PARALLEL(t 16)*/，白天跑直接把 32 核库打到 CPU 100%。并行只适合夜间批处理或独立分析库。

PARALLEL vs DOP：别搞混了

这两个写法效果不同，线上误用 DOP 的危害比 PARALLEL 大：

/*+PARALLEL(t 4)*/ — 加在表上，只让这张表并行扫描，其他表不受影响
/*+DOP(4)*/ — 加在语句上，整个 SQL 所有表都被强制并行，包括你没打算并行的小表
-- PARALLEL：只有 orders 并行，dept 不受影响
SELECT /*+PARALLEL(o 4)*/ d.dept_name, COUNT(*)
FROM   dept d, orders o WHERE d.dept_no = o.dept_no GROUP BY d.dept_name;

-- DOP：dept 也被强制并行了，3 行的小表开并行纯粹浪费
SELECT /*+DOP(4)*/ d.dept_name, COUNT(*)
FROM   dept d, orders o WHERE d.dept_no = o.dept_no GROUP BY d.dept_name;
生产环境建议在表级别设 DEGREE，而不是在每条 SQL 里写 HINT——更可控：

ALTER TABLE orders PARALLEL 4;   -- 表级并行，所有查询自动生效
ALTER TABLE orders NOPARALLEL;  -- 关掉并行
⚠️ 线上高危： OLTP 系统开并行 = 自杀。确认场景是批处理/报表后再开。

四、数据加载：批量插入加速
10. /*+APPEND*/ — 直接路径插入
场景： 年底数据归档，把去年所有订单 INSERT 到历史表。普通 INSERT 赟 Buffer Cache，50 万条跑了 15 分钟。APPEND 直接路径写入，绕过 Buffer Cache，2 分钟搞定。

-- 批量插入归档数据
INSERT /*+APPEND*/ INTO orders_history
SELECT * FROM orders WHERE order_date < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -12);

COMMIT;  -- 必须立即 COMMIT，否则后续操作报 ORA-12838
什么时候加： 大批量 INSERT（万级以上）、数据迁移/归档、ETL 加载。

什么时候别加： 小批量插入（几百行，APPEND 开销反而更大）；需要在同一事务里继续操作该表时（INSERT 后还想 UPDATE 同一张表）——这时用普通 INSERT 就行，不需要 /*+NOAPPEND*/，默认就是常规插入。

APPEND 对 Data Guard 同步的影响：

APPEND 使用直接路径写入，默认 LOGGING 模式下仍然产生 redo，DG 正常同步，没问题。

但 NOLOGGING 模式下，APPEND 不写 redo，主库快了但备库收不到这些数据，备库上该表会出现坏块（ORA-01578），需要手动重建。

-- 检查表的日志模式
SELECT table_name, logging FROM user_tables WHERE table_name = 'ORDERS_HISTORY';

-- 有 DG 的生产环境，确保 LOGGING 模式
ALTER TABLE orders_history LOGGING;

-- 没有 DG 或允许备库重建的场景，可以 NOLOGGING 提速
ALTER TABLE orders_history NOLOGGING;
INSERT /*+APPEND*/ INTO orders_history SELECT * FROM orders WHERE ...;
COMMIT;
ALTER TABLE orders_history LOGGING;  -- 完成后恢复
⚠️ 线上高危： 有 DG 的库千万别 NOLOGGING + APPEND，备库会出坏块。见过一次，运维在主库做了 NOLOGGING 归档，切换到备库时数据全丢了，4 小时重建。

APPEND 其他注意点：

必须 COMMIT 后才能对该表做 DML，否则报 ORA-12838
会抬高 HWM，DELETE 后空间不自动回收
不能用在有触发器或外键引用约束的表上
表级锁，不是行级锁
五、优化器目标：响应时间 vs 吞吐量
11. /*+FIRST_ROWS(n)*/ — 快速返回前 N 行
场景： 用户在 App 里查看订单列表，首页只显示 20 条。优化器选了全表扫描 + 排序的方案（总体成本最低），但用户等了 5 秒才看到第一行。FIRST_ROWS(20) 让优化器选 INDEX SCAN 方案，0.05 秒出结果。

-- 报表模式：整体跑完最优，但第一行返回慢
SELECT customer_id, order_date, amount
FROM   orders WHERE customer_id = 5 ORDER BY order_date DESC;

-- 交互模式：前 20 行最快返回
SELECT /*+FIRST_ROWS(20)*/ customer_id, order_date, amount
FROM   orders WHERE customer_id = 5 ORDER BY order_date DESC;
什么时候加： 分页查询、用户界面需要快速看到首页数据、在线交互查询。

什么时候别加： 报表/批处理需要全量跑完的。FIRST_ROWS 选的方案总成本更高，如果用户会翻到最后一页，反而更慢。这种场景该用 /*+ALL_ROWS*/（默认行为，不需要显式加）。

12. /*+ALL_ROWS*/ — 最小化总资源消耗
CBO 默认行为，日常不需要显式加。只在需要覆盖会话级 OPTIMIZER_MODE 设置时才用：

-- 会话设了 FIRST_ROWS，但这条报表需要全量
ALTER SESSION SET optimizer_mode = FIRST_ROWS_10;
SELECT /*+ALL_ROWS*/ dept_no, COUNT(*), SUM(salary) FROM emp GROUP BY dept_no;
六、诊断辅助：不是调优，是调试
13. /*+GATHER_PLAN_STATISTICS*/ — 执行计划统计
场景： 某条 SQL 莫名其妙慢了，看执行计划觉得没问题——优化器预估每步 100 行，看起来合理。但实际呢？加了 GATHER_PLAN_STATISTICS 发现某步预估 100 行实际跑了 10 万行。问题一目了然。

-- 第一步：带统计执行
SELECT /*+GATHER_PLAN_STATISTICS*/ e.emp_name, d.dept_name
FROM   emp e, dept d
WHERE  e.dept_no = d.dept_no AND e.dept_no = 'D5';

-- 第二步：查看 E-Rows（预估） vs A-Rows（实际）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));
输出关键看两列：

E-Rows = 优化器预估行数
A-Rows = 实际行数
差距大就是问题根源——统计信息过期或数据分布倾斜。

-- 确认统计信息是否过期
SELECT table_name, last_analyzed FROM user_tables WHERE table_name IN ('EMP', 'DEPT');

-- 如果很久没收，先收再看
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMP');
也可以在会话级别设置：ALTER SESSION SET statistics_level = ALL; 当前会话所有 SQL 都收集统计。调试完记得关掉，有额外开销。

14. /*+MONITOR*/ — 强制 SQL Monitor 记录
SQL Monitor 默认只记录执行时间超过 5 秒或并行执行的 SQL。如果你那条慢 SQL 刚好 4.9 秒，SQL Monitor 不记录，你抓不到实时进度。

加 /*+MONITOR*/ 强制记录，不管执行时间多短：

SELECT /*+MONITOR*/ * FROM orders WHERE amount > 10000;

-- 查看 SQL Monitor 报告
SELECT DBMS_SQL_MONITOR.REPORT_SQL_MONITOR(
  sql_id => '&sql_id',
  type   => 'TEXT'
) FROM DUAL;
什么时候加： 调试线上慢 SQL，需要看实时执行进度和每步耗时；SQL 执行时间刚好在 SQL Monitor 阈值边缘（不够 5 秒但你想看详细进度）。

什么时候别加： 高频执行的 OLTP SQL。每条都 Monitor 有性能开销，只在调试特定 SQL 时用。

七、额外场景：静态小表频繁查
15. /*+RESULT_CACHE*/ — 结果集缓存
场景： 系统里有个省份代码表，50 条数据，一天被查几万次。每次都走索引扫描再返回结果，其实结果集永远是那 50 条，完全可以缓存。

-- 省份代码表，数据几乎不变，查询极频繁
SELECT /*+RESULT_CACHE*/ province_code, province_name
FROM   province_code_table
ORDER  BY province_code;

-- 第二次执行时，执行计划里会出现 RESULT CACHE 操作，直接从缓存返回
什么时候加： 查询频次高、数据几乎不变的小表（国家代码、省份、币种、枚举字典表）。

什么时候别加： 数据频繁变化的表。RESULT_CACHE 的失效机制是基于依赖对象的修改，但失效不够及时可能导致返回过期数据。另外，RESULT_CACHE 占共享池内存，别在大结果集上用。

⚠️ 线上高危： RESULT_CACHE 在 RAC 环境下有缓存同步开销，多节点间需要传递缓存结果。如果你的 RAC 节点间网络延迟高，RESULT_CACHE 可能反而更慢。单实例环境效果最好。

HINT 使用三条铁律
1. HINT 写错了不报错，只是被忽略。

/*+FULL(t)*/ 写成 /*+FULL(a)*/ 但 FROM 里没有别名 a，Oracle 不报错，默默忽略。你以为加了 HINT，优化器根本没理你。

验证方法——看 Hint Report：

-- 生产环境取真实执行计划的 ADVANCED 格式
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ADVANCED'));
-- Hint Report 部分会显示：
--   "hint not used" → 说明被忽略
--   没有 "hint not used" → 说明生效了
什么是 Hint Report？ 这是执行计划的附带信息，记录了每个 HINT 的最终状态——被使用了、被忽略了、还是被覆盖了。它只在 DISPLAY_CURSOR 的 ADVANCED 格式里出现，EXPLAIN PLAN FOR + DISPLAY 里看不到。

另外，执行计划末尾的 Note 部分也能辅助判断：如果出现 - dynamic statistics used 或 - SQL profile used，说明有其他机制介入，HINT 可能被覆盖。

2. HINT 里的表别名必须和 SQL 里的一致。

-- 错：HINT 用表名，SQL 里有别名
SELECT /*+FULL(emp)*/ * FROM emp e WHERE e.dept_no = 'SALES';  -- HINT 无效！

-- 对：HINT 用别名
SELECT /*+FULL(e)*/ * FROM emp e WHERE e.dept_no = 'SALES';
SQL 里给了别名后，HINT 只能用别名，不能用表名。这条规则害了无数人。

3. 12c 以后考虑用 SPM 替代 HINT。

HINT 写在 SQL 里，改 SQL 要走发版。SPM 可以不改 SQL 固定执行计划，生产更推荐。紧急用 HINT 救火，之后迁移到 SPM：

DECLARE
  v_plans NUMBER;
BEGIN
  v_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => '&hint_sql_id',
    fixed  => 'YES'
  );
END;
/
口诀速记
统计先收再看计划，INDEX 配 DESC 分页快。
DESC 可能不走降序，NOT NULL 约束更靠谱。
LEADING 能排多张表，顺序写对别搞反。
小表驱动用 NL，大表 JOIN 上 HASH。
Hash 溢出看 TempSpc，PGA 调大再上线。
并行别在白天开，DOP 跟 PARALLEL 别搞混。
APPEND 记得 COMMIT，DG 别开 NOLOGGING。
HINT 拼错不报错，别名写对是关键。
Hint Report 看 ADVANCED，被忽略就别纠结。