Oracle SQL 执行计划分析：如何第一眼看慢在哪

性能分析时，执行计划大致分两类：

1. Explain Plan
   只看优化器“预估怎么跑”
2. Display Cursor / Allstats
   看 SQL “实际怎么跑”

性能分析优先看第 2 种。

一、预估执行计划

适合先快速看路径，但不能判断真实耗时。

EXPLAIN PLAN FOR
SELECT ...;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'TYPICAL'));

特点：

• 只有 E-Rows
• 没有 A-Rows、Buffers、A-Time
• 只能看预估，不代表真实执行

二、看真实执行计划

在 SQL 上加 gather_plan_statistics：

SELECT /*+ gather_plan_statistics */ ...
FROM ...
WHERE ...;

执行完后立刻查：

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +PREDICATE'));

如果抓错了 SQL，就先查 sql_id：

SELECT sql_id, child_number, last_active_time, sql_text
FROM v$sql
WHERE sql_text LIKE 'SELECT /*+ gather_plan_statistics */%'
ORDER BY last_active_time DESC;

再指定查看：

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID', CHILD_NUMBER, 'ALLSTATS LAST +PREDICATE'));

如果不用 hint，也可以开 session 统计：

ALTER SESSION SET statistics_level = ALL;

然后真正执行 SQL，再查：

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST +PREDICATE'));

但这个方法不如 gather_plan_statistics 稳，原因是：

• 客户端可能换连接
• 目标 SQL 可能复用旧游标
• 工具可能自动执行别的 SQL，导致 NULL, NULL 抓错

所以实践里更推荐：

SELECT /*+ gather_plan_statistics */ ...

三、怎么看输出

如果只有这种列：

| Id | Operation | Name | E-Rows |

说明拿到的是预估计划，或者这个 cursor 没采集到运行统计。

如果有这些列：

Starts | E-Rows | A-Rows | A-Time | Buffers | Reads

说明拿到的是实际执行统计。

重点看：

第一眼定位慢点，可以按这个顺序看：

先看 Buffers / A-Time 最大处
再看 A-Rows 是否暴涨
再看 Starts 是否反复执行
最后看关键条件是在 access 还是 filter

四、怎么看谓词

加上 +PREDICATE 后，会显示 access / filter。

• access
  用这个条件真正定位数据，通常更好
• filter
  数据先拿出来，再过滤

判断性能时很关键：

• 好的情况：高选择性条件进入 access
• 差的情况：高选择性条件只出现在 filter

例如：

access("W"."STATUS"='POTENTIAL')
filter("W"."CREATESTAMPA2">ADD_MONTHS(TRUNC(SYSDATE),-6))

表示 Oracle 先用 STATUS 定位，再回表用时间条件过滤。
如果 STATUS 命中很多行，这种路径可能会很慢。

五、常见误区

1. 有索引就一定快
   不对。索引如果把驱动顺序带偏，反而更慢。

2. EXPLAIN PLAN 就够了
   不够。它只有预估，没有真实执行统计。

3. ALTER SESSION SET statistics_level = ALL 一定生效
   不一定。如果不是同一 session，或者复用了旧游标，就看不到 A-Rows。

4. DISPLAY_CURSOR(NULL, NULL, ...) 一定拿到目标 SQL
   不一定。很多客户端会自动执行：

   • DBMS_OUTPUT.GET_LINE
   • 元数据查询
   • 分页 SQL

   所以最好按 SQL_ID + CHILD_NUMBER 精确查。

5. 看到 A-Rows 很小就以为表也很小
   不一定。客户端如果没有 fetch 完，ALLSTATS LAST 看到的是本次实际被消费的数据，不一定是完整结果集。

六、案例：一次 Oracle SQL 从 13.82 秒优化到 1.07 秒

1. 原始 SQL

SELECT /*+ gather_plan_statistics */
       a.NAME  AS wName,
       u.email AS mail
FROM WORKITEM w
JOIN WFASSIGNEDACTIVITY a
  ON w.IDA3A4 = a.IDA2A2
JOIN WTUSER u
  ON u.IDA2A2 = w.IDA3A2OWNERSHIP
WHERE w.STATUS = 'POTENTIAL'
  AND (
       a.NAME = '主物控组包料审核'
    OR a.NAME = '主物控电子料审核'
    OR a.NAME = '主物控客供料审核'
  )
  AND w.CREATESTAMPA2 > ADD_MONTHS(TRUNC(SYSDATE), -6);

2. 优化前执行计划摘要

优化前核心计划：

| Id | Operation              | Name                           | A-Rows | A-Time   | Buffers | Reads |
|---:|------------------------|--------------------------------|-------:|----------|--------:|------:|
|  0 | SELECT STATEMENT       |                                |     18 | 00:00:13.82 | 71332 | 51312 |
|  4 | VIEW                   | index$_join$_002               |    382 | 00:00:12.06 | 68502 | 50800 |
|  6 | INDEX FAST FULL SCAN   | PK_WFASSIGNEDACTIVITY          | 4519K  | 00:00:11.17 | 19593 |  2200 |
|  7 | INDEX FAST FULL SCAN   | WFASSIGNEDACTIVITY$COMPOSITE22 |    382 | 00:00:03.57 | 48909 | 48600 |

谓词信息：

7 - filter(("A"."NAME"='主物控客供料审核'
         OR "A"."NAME"='主物控电子料审核'
         OR "A"."NAME"='主物控组包料审核'))

第一眼看，慢点在 WFASSIGNEDACTIVITY。

Oracle 为了找 3 个 NAME，做了 INDEX FAST FULL SCAN，并且还把两个索引通过 ROWID 做了 index join。
最后只得到 382 行，但前面读了 5 万多个块。

3. 为什么已有索引没用好

WFASSIGNEDACTIVITY 现有索引：

PK_WFASSIGNEDACTIVITY              (IDA2A2)
WFASSIGNEDACTIVITY$COMPOSITE22     (IDA3PARENTPROCESSREF, NAME, STATE)

问题在于：

WFASSIGNEDACTIVITY$COMPOSITE22 = (IDA3PARENTPROCESSREF, NAME, STATE)

而 SQL 只有：

a.NAME IN (...)

没有：

a.IDA3PARENTPROCESSREF = ...

所以虽然索引里有 NAME，但 NAME 不是前导列，不能直接做高效的 INDEX RANGE SCAN。
执行计划里也印证了这一点：

filter("A"."NAME"=...)

而不是：

access("A"."NAME"=...)

4. 第一次索引方案失败

最自然的想法是建：

CREATE INDEX IDX_WFAA_NAME_ID
ON WFASSIGNEDACTIVITY (NAME, IDA2A2)
ONLINE;

但实际报错：

ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01450: 超出最大的关键字长度 (3215)

真正原因是：

NAME 字段定义太长，完整 NAME + IDA2A2 超过了单个索引 key 的最大长度。

这里 ORA-00604 只是外层递归 SQL 报错，关键是 ORA-01450。

5. 改用函数索引

因为目标 NAME 值都很短：

主物控组包料审核
主物控电子料审核
主物控客供料审核

所以改为函数索引：

CREATE INDEX IDX_WFAA_NAME100_ID
ON WFASSIGNEDACTIVITY (SUBSTR(NAME, 1, 100), IDA2A2)
ONLINE;

注意：

SUBSTR(NAME, 1, 100)

是按字符截取，不是按字节截取。

如果 SQL 中保留原始条件：

a.NAME IN (...)

Oracle 在本次环境中可以使用函数索引对应的隐藏列：

SYS_NC00048$

最终执行计划出现：

access("A"."SYS_NC00048$"='主物控客供料审核' OR ...)

说明函数索引已经生效。

6. 测试机验证结果

测试机加索引后：

| Id | Operation                       | Name                | A-Rows | A-Time      | Buffers | Reads |
|---:|---------------------------------|---------------------|-------:|-------------|--------:|------:|
|  0 | SELECT STATEMENT                |                     |      3 | 00:00:00.01 |      63 |    16 |
|  6 | INDEX RANGE SCAN                | IDX_WFAA_NAME100_ID |     10 | 00:00:00.01 |       8 |     3 |

优化前：

A-Time  13.82s
Buffers 71332
Reads   51312

测试机优化后：

A-Time  0.01s
Buffers 63
Reads   16

执行路径从：

扫大量 WFASSIGNEDACTIVITY 索引，再 filter NAME

变成：

按 NAME 通过函数索引直接定位少量 WFASSIGNEDACTIVITY

7. 生产环境验证结果

生产环境加索引后：

| Id | Operation                       | Name                | A-Rows | A-Time      | Buffers | Reads |
|---:|---------------------------------|---------------------|-------:|-------------|--------:|------:|
|  0 | SELECT STATEMENT                |                     |     21 | 00:00:01.07 |    3261 |   782 |
|  7 | INDEX RANGE SCAN                | IDX_WFAA_NAME100_ID |    385 | 00:00:00.01 |      13 |     4 |
|  8 | INDEX RANGE SCAN                | WORKITEM$COMPOSITE2 |    391 | 00:00:00.60 |     780 |   102 |
|  9 | TABLE ACCESS BY INDEX ROWID     | WORKITEM            |     21 | 00:00:02.60 |     566 |   431 |

生产优化前：

A-Time  13.82s
Buffers 71332
Reads   51312

生产优化后：

A-Time  1.07s
Buffers 3261
Reads   782

大致效果：

生产计划里的关键证据：

7 - access(("A"."SYS_NC00048$"='主物控客供料审核'
         OR "A"."SYS_NC00048$"='主物控电子料审核'
         OR "A"."SYS_NC00048$"='主物控组包料审核'))

这说明生产也已经使用了函数索引。

8. 为什么优化后还有 1 秒

优化后主要成本不再是大范围扫描，而是少量随机回表：

6 TABLE ACCESS BY INDEX ROWID WFASSIGNEDACTIVITY
  A-Rows 385
  Buffers 407
  Reads 249

9 TABLE ACCESS BY INDEX ROWID WORKITEM
  A-Rows 21
  Buffers 566
  Reads 431

这已经是完全不同级别的问题。
从 5 万多物理读降到 782，主要瓶颈已经解决。

计划中有一处看起来奇怪：

9 WORKITEM A-Time 00:00:02.60
0 SELECT STATEMENT A-Time 00:00:01.07

子节点时间看起来比总时间还大。实际分析时不要把每一行 A-Time 简单相加，执行计划里的时间可能受统计口径、累计方式、缓存和显示误差影响。
判断整体效果时，优先看顶层 SELECT STATEMENT、Buffers、Reads 和关键路径变化。

9. 本次案例的关键经验

这次优化的关键不是“加一个索引”这么简单，而是几件事串起来：

1. 用 ALLSTATS LAST +PREDICATE 看真实执行，而不是只看 EXPLAIN PLAN
2. 先看 Buffers、Reads、A-Time，定位真正的资源消耗点
3. 注意 access 和 filter 的区别
4. 复合索引不是包含某列就一定有用，前导列非常关键
5. 客户端如果没有 fetch 完，A-Rows 可能误导判断
6. 普通索引遇到 ORA-01450 时，可以考虑函数索引
7. 优化必须在测试机和生产环境分别验证执行计划

最终这个 SQL 的核心问题是：

WFASSIGNEDACTIVITY 现有索引虽然包含 NAME，但 NAME 不是前导列。
Oracle 只能扫描索引后 filter NAME。
通过 SUBSTR(NAME,1,100) 函数索引，让 NAME 条件进入 access，
执行路径从大范围扫描变成精准定位。

最终索引：

CREATE INDEX IDX_WFAA_NAME100_ID
ON WFASSIGNEDACTIVITY (SUBSTR(NAME, 1, 100), IDA2A2)
ONLINE;

最终效果：

13.82s -> 1.07s
71332 Buffers -> 3261 Buffers
51312 Reads -> 782 Reads