如果你的 AI Agent 能生成 SQL，但面对一个跑不动的查询时只会说「加个索引试试」——它差的不是诊断直觉，而是一套读懂执行计划、判断访问路径、修复基数估算的调优方法论。oracle/skills 里的https://github.com/oracle/skills/blob/main/db/performance/sql-tuning.md，可以提供帮助。

---

一、执行计划：一切调优的起点

通用大模型写 SQL 没问题，但一旦涉及的查询跑不动，AI 最常见的回答是「加个索引」或「看看执行计划」——它说得出工具名，却不知道怎么用。本文基于 sql-tuning.md，把 AI Agent 训练成一个能真正读懂计划、定位根因的调优助手。

1.1 两种看计划的方式，差距有多大？

Oracle 提供两种获取执行计划的手段，但它们在可信度上有本质区别：

-- 方式一：EXPLAIN PLAN（仅估算，不实际执行）
EXPLAIN PLAN FOR
SELECT e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id
WHERE  e.salary > 10000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

EXPLAIN PLAN 只做估算。它不读取实际数据，依赖的是数据字典中的统计信息。如果统计信息过期——比如批量加载了 500 万行之后忘了收集统计信息——EXPLAIN PLAN 会基于错误的基数生成一个看似完美、实则灾难的计划。

生产调优的黄金标准是 DBMS_XPLAN.DISPLAY_CURSOR 配合 ALLSTATS LAST：

-- 先执行目标 SQL，然后从游标缓存中取到它的 SQL_ID
SELECT sql_id, sql_text
FROM   v$sql
WHERE  sql_text LIKE '%last_name%employees%'
AND    sql_text NOT LIKE '%v$sql%';

-- 用真实行数 (A-Rows) 和真实执行次数 (Starts) 分析计划
SELECT * FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR('abc12345xyz', 0, 'ALLSTATS LAST')
);

ALLSTATS LAST 给了 EXPLAIN PLAN 永远无法提供的东西——每一行估算值和实际值并排展示。这是判断计划好坏的唯一可靠依据。

1.2 执行计划输出的关键列

列	含义	诊断价值
Id	操作步骤编号，子步骤缩进	理解执行顺序
Operation	Oracle 在做什么（TABLE ACCESS、INDEX RANGE SCAN 等）	最核心的诊断信息
Name	被访问的对象名	索引名 vs 表名，一眼看出是否走了索引
E-Rows	估算行数——优化器的预测	偏小→可能选了 NL 而非 HJ；偏大→可能选了 FULL 而非索引
A-Rows	实际行数（需要 ALLSTATS）	与 E-Rows 的差距直接反映统计信息质量
Starts	本步骤被执行的次数	NL 内表被重复执行 Starts = 驱动行数——过大意味着 NL 选错了
Cost (%CPU)	优化器成本估算和 CPU 占比	辅助判断

1.3 E-Rows vs A-Rows：一切坏计划的根源

一个铁律：E-Rows 与 A-Rows 的差距超过 10 倍，几乎必然产生坏计划。

假设优化器估算某一步返回 100 行（E-Rows=100），实际返回 10 万行（A-Rows=100K）。这 1000 倍的偏差会导致：

• 如果这步是 NL 的驱动表，内表会被访问 10 万次而非 100 次——执行时间从毫秒级膨胀到分钟级
• 如果这步本该用 Hash Join，但优化器因为「觉得」只有 100 行而选了 Nested Loops——灾难

基数估算错误是 SQL 性能问题的 #1 根本原因。 它的常见导火索包括：批量 DML 后未收集统计信息、多列关联查询缺少扩展统计信息、函数包裹 WHERE 条件使优化器无法推断选择性、以及倾斜数据缺少直方图。

---

二、访问路径：Oracle 怎么找到你的数据

Oracle 有五种核心访问路径，从最高效到最昂贵排列如下：

访问路径	机制	适用场景
INDEX UNIQUE SCAN	B-tree 唯一索引单次探测	WHERE pk_col = 123——最高效的单行访问
INDEX RANGE SCAN	B-tree 范围扫描	WHERE col BETWEEN 100 AND 200——选择性好的范围查询
INDEX SKIP SCAN	复合索引跳过前导列扫描	前导列基数很低时（如 (status, id) 查 id=5）
INDEX FAST FULL SCAN	多块 I/O 读取整个索引段	索引覆盖查询（所有需要列都在索引中）
TABLE ACCESS FULL	多块 I/O 读取整个表段	大比例数据检索（>15~20%）——不一定是坏事

2.1 函数包裹索引列：一条 WHERE 条件杀死一个索引

-- 这条 SQL 永远不会走 name 列上的索引
SELECT * FROM employees WHERE UPPER(name) = 'SMITH';

因为 UPPER(name) 是一个表达式，优化器看到的是「未知函数的结果」，无法与索引 name 关联。解决方式是建一个函数索引：

CREATE INDEX emp_upper_name_idx ON employees(UPPER(name));

2.2 隐式类型转换：最隐蔽的索引杀手

-- emp_id 是 NUMBER 类型，但传入的却是字符串 '100'
SELECT * FROM employees WHERE emp_id = '100';

Oracle 不会报错——它会自动把 emp_id 隐式转为 TO_NUMBER(emp_id)。结果是：索引失效，变成全表扫描。WHERE 条件中的字面值类型必须与列类型严格匹配。

---

三、连接方法：三张牌各打什么场景

Oracle CBO 在三种连接算法中选择，选择依据是基数估算、可用索引和 PGA 内存。

3.1 Nested Loops——小驱动集 + 内表索引

NESTED LOOPS
  TABLE ACCESS FULL   EMPLOYEES      (驱动表，返回少量行)
  INDEX RANGE SCAN    DEPT_ID_IDX    (内表，每条驱动行探测一次)

• 内存消耗几乎为零
• 驱动行数必须小（通常 < 1000），否则内表被重复访问的次数会让执行时间线性膨胀
• Starts 列如果等于驱动表的 A-Rows 且数值很大——NL 选错了

3.2 Hash Join——两大表无索引时的最优解

HASH JOIN
  TABLE ACCESS FULL   DEPARTMENTS    (Build 端——较小表，被哈希到内存)
  TABLE ACCESS FULL   EMPLOYEES      (Probe 端——较大表，逐行探测)

• 仅支持等值连接
• 如果 Build 端超过 PGA 内存，会溢出到 TEMP 表空间——性能急剧下降
• 哈希表在 PGA 中构建，不需要索引

3.3 Sort Merge Join——预排序数据的加速器

两表各自按连接键排序后再做归并。当输入已经有序（比如索引已经提供了排序）时，Sort Merge 可以跳过排序步骤，比 Hash Join 更快。但它的内存需求通常高于 Hash Join。

---

四、Optimizer Hints：双刃剑，而非万金油

Hint 是嵌入 SQL 注释中的优化器指令。它能强制指定访问路径、连接方法和连接顺序——但代价是绕过了 CBO 的自主决策能力。

4.1 常用 Hint 速查

-- 强制使用指定索引
SELECT /*+ INDEX(e EMP_DEPT_IDX) */ *
FROM   employees e
WHERE  department_id = 30;

-- 强制全表扫描（绕过索引）
SELECT /*+ FULL(e) */ *
FROM   employees e
WHERE  status = 'A';

-- 控制连接方法
SELECT /*+ USE_NL(e d) */ e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id;

SELECT /*+ USE_HASH(e d) */ e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id;

-- 控制连接顺序（e 做驱动表，d 做内表）
SELECT /*+ LEADING(e d) USE_NL(d) */ e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id;

-- 并行执行（4 个并行线程）
SELECT /*+ PARALLEL(e, 4) */ COUNT(*) FROM employees e;

-- 基数提示（当统计信息不准确时的临时补救）
SELECT /*+ CARDINALITY(e 100) */ *
FROM   employees e
WHERE  complex_function(salary) > 5000;

4.2 什么时候绝对不要用 Hint

Hint 有三个结构性缺陷，使其不适合作为长期调优方案：

1. 脆弱的依赖——表改名、索引重建、统计信息变化后，Hint 指定的路径可能反而变成最差路径
2. 绕过自适应优化——12c+ 默认开启的 OPTIMIZER_ADAPTIVE_PLANS 可以在执行中途根据实际行数切换连接方法，Hint 直接禁止了这个能力
3. 维护负担——应用代码里的 Hint 随版本迭代很容易被遗忘，变成「没人敢删的魔法注释」

前置原则：先用统计信息、索引、SQL 改写解决性能问题。只有调无可调时，才用 Hint 作为临时方案，并在下一个维护窗口换成 SQL Profile 或 Plan Baseline。

---

五、统计信息：CBO 的「眼睛」必须保持清晰

CBO 靠统计信息判断每张表有多少行、每列有多少不同值、数据如何分布。统计信息缺失或过期，是坏计划最常见（也最容易被忽视）的原因。

5.1 收集统计信息的标准姿势

-- 单表收集（推荐使用 AUTO_SAMPLE_SIZE）
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname          => 'HR',
  tabname          => 'EMPLOYEES',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
  cascade          => TRUE   -- 同时收集该表所有索引的统计信息
);

-- 整个 Schema 收集
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
  ownname          => 'HR',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
  cascade          => TRUE
);

AUTO_SAMPLE_SIZE 使用 Oracle 的增量统计算法，以接近 100% 的精度、极小的采样成本完成统计——几乎没有理由手动指定采样百分比。

5.2 过期统计信息的探测

-- 超过 10% 数据变更的表会被标记为 STALE
SELECT owner, table_name, stale_stats
FROM   dba_tab_statistics
WHERE  owner = 'HR'
AND    stale_stats = 'YES';

批量加载、大事务提交后务必检查 stale_stats。如果表被标记为 YES，CBO 看到的行数可能只是实际行数的几分之一——这正是 E-Rows 远远小于 A-Rows 的最常见成因。

5.3 直方图：倾斜数据的必修课

-- 查看直方图的桶分布
SELECT endpoint_number, endpoint_value
FROM   dba_histograms
WHERE  owner = 'HR'
AND    table_name = 'EMPLOYEES'
AND    column_name = 'DEPARTMENT_ID'
ORDER BY endpoint_number;

当 SIZE AUTO 生效时，Oracle 自动为低基数列创建频率直方图（≤254 个不同值）或等高直方图（>254 个不同值）。手动干预通常不需要——除非某些列确实存在严重倾斜且自动直方图未触发。

5.4 扩展统计信息：修复关联列的盲区

当两个列存在关联（如 city 和 state），优化器默认假设它们独立，从而严重低估组合条件的选择性：

-- 创建列组扩展统计信息
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(
  ownname => 'HR',
  tabname => 'EMPLOYEES',
  extension => '(DEPARTMENT_ID, JOB_ID)'
) FROM dual;

-- 重新收集以填充扩展统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES',
  method_opt => 'FOR ALL COLUMNS SIZE AUTO');

---

六、索引策略：从发现缺失到安全上线

6.1 从 v$sql_plan 发现缺失索引

全表扫描不一定是坏事——但如果大表反复被全扫，这几乎一定是索引缺失的信号：

-- 从游标缓存中找出大表上的全表扫描
SELECT s.sql_id, s.executions, p.object_name, p.operation, p.options,
       s.disk_reads / NULLIF(s.executions, 0) AS reads_per_exec
FROM   v$sql s
JOIN   v$sql_plan p ON s.sql_id = p.sql_id AND s.child_number = p.child_number
WHERE  p.operation = 'TABLE ACCESS'
AND    p.options   = 'FULL'
AND    p.object_name NOT IN ('DUAL')
ORDER BY reads_per_exec DESC NULLS LAST
FETCH FIRST 20 ROWS ONLY;

按每次执行平均物理读排序，排在最前面的就是最急需索引的查询。

6.2 不可见索引：生产环境的「隐形测试」

新增索引有风险：它可能改变其他 SQL 的执行计划，甚至在极端情况下拖慢批量 DML。不可见索引完美解决了这个两难：

-- 创建不可见索引——不影响任何现有执行计划
CREATE INDEX emp_salary_idx ON employees(salary) INVISIBLE;

-- 只在当前会话中启用不可见索引，进行效果验证
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;
EXPLAIN PLAN FOR SELECT * FROM employees WHERE salary BETWEEN 5000 AND 8000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 验证通过后设为可见
ALTER INDEX emp_salary_idx VISIBLE;

6.3 索引使用监控

-- 12.2+ 查看索引使用统计
SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring
FROM   v$object_usage
WHERE  table_name = 'EMPLOYEES';

-- 对可疑索引开启监控
ALTER INDEX hr.emp_name_idx MONITORING USAGE;

发现从未使用过的索引后，在下一个维护窗口可以安全地将其删除——减少 DML 开销。

6.4 19c+ 自动索引

Oracle 19c 及以上版本支持自动索引：系统分析 SQL 工作负载，自动识别候选索引、创建、验证并在效果不佳时自动删除。在 DBA 资源有限的环境中，自动索引是索引优化的零成本起点。

---

七、SQL Profile 与 SQL Plan Baseline：生产环境的持久化护栏

Hint 写完就忘、统计信息可能在下一次自动收集任务中变化——生产环境需要更可靠的调优机制。

7.1 SQL Profile：修正估算，不锁定计划

SQL Profile 为特定 SQL 存储额外的统计修正因子，让 CBO 做出更准确的估算。它不锁定执行计划——如果数据分布变化，CBO 仍然可以自由选择新计划，但基于更准确的统计信息。

-- 使用 SQL Tuning Advisor 生成 Profile
DECLARE
  l_task_name VARCHAR2(30);
  l_sql_id    VARCHAR2(13) := 'abc12345xyz78';
BEGIN
  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id      => l_sql_id,
    scope       => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
    time_limit  => 60,
    task_name   => 'tune_task_1'
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_name);
END;
/

-- 查看调优建议
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_task_1') FROM dual;

-- 接受 SQL Profile 建议
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
  task_name    => 'tune_task_1',
  replace      => TRUE
);

7.2 SQL Plan Baseline：锁定已知良好的计划

SPM（SQL Plan Management）捕获已知良好的执行计划，并在优化器产生新计划时进行性能验证——只有验证通过的新计划才会被启用。这是防止计划回退的最后一道防线。

-- 从游标缓存加载已知良好计划作为基线
DECLARE
  l_count PLS_INTEGER;
BEGIN
  l_count := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'abc12345xyz78'
  );
  DBMS_OUTPUT.PUT_LINE('Baselines loaded: ' || l_count);
END;
/

-- 查看现有基线
SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
FROM   dba_sql_plan_baselines
ORDER BY created DESC;

-- 演化未接受的计划（测试并择优升级）
DECLARE
  l_report CLOB;
BEGIN
  l_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SQL_abc123',
    time_limit => 30,
    verify     => 'YES',
    commit     => 'YES'
  );
  DBMS_OUTPUT.PUT_LINE(l_report);
END;
/

两者的分工明确：

特性	SQL Profile	SQL Plan Baseline
核心作用	修正统计估算	防止计划回退
是否锁定计划	否——CBO 仍可自由选择	是——仅允许已验证的计划
适用阶段	调优阶段	上线后的稳定化阶段

---

八、常见误区与纠正

错误做法	后果	正确做法
WHERE UPPER(name) = 'SMITH' 使用普通索引	索引失效→全表扫描	创建函数索引 ON employees(UPPER(name))
WHERE emp_id = '100'（NUMBER 列传入字符串）	隐式 TO_NUMBER → 索引失效	类型严格匹配
批量加载后不收集统计信息	CBO 基于旧行数生成计划	大 DML 后立即 GATHER_TABLE_STATS
在应用代码中硬编码 Hint	表改名/重建后 Hint 指向不存在对象	用 SQL Profile/Baseline 替代
WHERE ROWNUM < 10 ORDER BY col	ROWNUM 在排序前过滤——返回随机 10 行	用子查询或 FETCH FIRST 10 ROWS ONLY
过度索引	DML 变慢、空间浪费	监控索引使用，删除未使用索引
忽略 TEMP 表空间溢出	Hash/Sort 溢出导致性能断崖式下降	增大 PGA 或减少中间结果集

---

九、总结

这张 SQL 调优知识体系的核心只有一个循环：看到坏计划 → 检查 E-Rows vs A-Rows → 修复统计信息或索引 → 用 Profile/Baseline 固化成果。

把这份 15KB 的指南配进 AI Agent，意味着它不再只会说「加个索引」——它能看到执行计划中 E-Rows 与 A-Rows 的鸿沟、能判断 NL/HJ/SMJ 的选择是否合理、能在批量 DML 后记得检查 stale_stats、能在调优完成后用 SQL Plan Baseline 锁定成果。这就是从「会生成 SQL 的 AI」到「会诊断 SQL 的 AI」的质变。

它提供什么	它不提供什么
执行计划的读取方法和诊断技巧	SQL 语法教程（那是 sql-patterns.md 的领域）
五种访问路径的选择逻辑和陷阱	具体的业务逻辑建议
三种连接算法的适用场景对比	操作系统级性能分析
Hint 的正确使用姿势和边界	非 Oracle 数据库的调优方法
统计信息、索引、Profile、Baseline 的完整操作链	应用层缓存策略

如果只记住一件事，记住这个：E-Rows 和 A-Rows 差距超过 10 倍的地方，就是你的维修点。 先修统计信息，再看索引，不到万不得已不用 Hint。

最后一条操作纪律：每次上线前，用不可见索引测试新增索引的效果；每次调优后，用 SQL Plan Baseline 锁定已验证的计划。这两步之间的空白，就是生产事故的温床。

---

源文件：https://github.com/oracle/skills/blob/main/db/performance/sql-tuning.md