Oracle UNDO 表空间治理实战：从 ORA-01555 到 undo 保留策略

dbaai

一、具体的问题

上个月出账期，一张跑了四十分钟的报表 SQL 在最后聚合阶段报错：

2. ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_1834771209$" too small

复制代码

有意思的是它不是每次都报。同一个作业连跑三天，第一天失败、第二天成功、第三天又失败；白天手动执行同一段 SQL 从来没出过问题。业务只能反复重跑，出账窗口被拖得很紧。

登上去看，UNDOTBS1 的使用率长期在 95% 以上，数据文件开着自动扩展，已经从初始的 4GB 涨到了 32GB。第一反应是"undo 给少了"，于是有人把数据文件再加 8GB——第二天照报不误。

这就是这个问题的典型陷阱：ORA-01555 在绝大多数情况下不是空间不够，而是空间被复用得太快。空间不够报的是另一个错，ORA-30036: unable to extend segment by ... in undo tablespace，两者的处理方向几乎相反，混在一起治就会像上面那样，加了盘还是报。

这篇文章给出一条能照着走的路径：先用 v$undostat 把"实际生效的保留时间"和"最长查询时长"这两个数摆在一起看，判断是不是结构性不匹配；再用 DBMS_UNDO_ADV 估算真实需要多少 undo；然后用 v$transaction 揪出把 undo 刷爆的会话；最后按优先级做治理，并在治理前后用同一组指标验证。

二、核心原理

UNDO 干两件事，第二件才是 1555 的根源

UNDO 表空间里的数据有两个用途：事务回滚（ROLLBACK 或实例恢复时把数据块还原）和读一致性（构造 CR 块）。

后者是 1555 的来源。当一个查询在扫描某一行时，发现这个块被别的事务改过了、且改动发生在本查询的 SCN 之后，它就必须顺着 ITL 里的 undo 地址，找到那块事务前镜像，拼出一个"该查询开始时样子"的块——这就是一致性读。如果那块前镜像已经被新事务覆盖掉了，查询就无路可走，只能报 1555。

UNDO 是环形复用的，覆盖时机由"空间 + 保留期"共同决定

UNDO 表空间按段（undo segment）和区间（extent）环形使用。新事务需要空间时，Oracle 按下面的顺序找地：

顺序	动作	后果
1	段内还有空闲 extent	直接用，不覆盖任何东西
2	段内没有，但表空间有空闲 extent	扩展段，仍不覆盖
3	表空间也没有空闲，但可以自动扩展	扩数据文件，仍不覆盖
4	扩不动了（到 maxsize 或磁盘满）	开始覆盖"尚未过期"的 undo

只有在第 4 步，才会真正威胁到长查询。这条链条解释了很多"看起来矛盾"的现象：为什么 autoextend 打开的时候不容易报错、为什么把 maxsize 调大就安静了几天、为什么磁盘写满会连着报 1555。

"过期"怎么算？由 undo_retention（默认 900 秒）定义，但这里有个关键细节：Oracle 10.2 之后默认开启 undo 自动调优（隐含参数 _undo_autotune），会按 undo 表空间的实际大小和负载动态调整保留时间。也就是说，undo_retention 设成 900，实际生效的可能只有 400 多秒；你把它调到 10800，如果空间没跟上，实际生效值照样上不去。真正生效的值在 v$undostat.tuned_undoretention，不在初始化参数里。这是排查 1555 时第一个要看、也是最常被跳过的一步。

两类根因，对应两套措施

根因类型	特征	主要措施
查询太长（比保留期还长）	maxquerylen > tuned_undoretention	优化 SQL、拆分查询、改走备库读
undo 被刷爆（空间周转太快）	undoblks 峰值极高、并发事务数大	给足空间、控制批量 DML、检查无索引外键
两者同时存在	长查询 + 高并发写	两条一起做，只做一条必然复发

本例属于第三类：报表要跑四十分钟，而当时实际生效的保留时间只有 412 秒——查询时长是保留期的七倍，中途只要有一个批量写事务把 undo 空间顶到上限，报表就必挂。

三、实例参考（动手步骤）

步骤 0：先把现状和基线记下来

2. -- 参数与表空间属性
3. SHOW PARAMETER undo;
4. SELECT tablespace_name, retention, status FROM dba_tablespaces WHERE tablespace_name LIKE 'UNDO%';
6. -- 数据文件：重点看 autoextensible 与 maxbytes，别只看当前大小
7. SELECT file_name,
8.        ROUND(bytes/1024/1024)          AS cur_mb,
9.        autoextensible,
10.        ROUND(maxbytes/1024/1024)       AS max_mb
11. FROM   dba_data_files
12. WHERE  tablespace_name = 'UNDOTBS1';

复制代码

retention 这一列如果是 GUARANTEE，含义是"宁可让事务失败也不覆盖未过期 undo"，后面第五步会展开——这是把双刃剑。

步骤 1：用 v$undostat 定位，这是 undo 的体检表

2. SELECT TO_CHAR(begin_time,'MM-DD HH24:MI') AS begin_t,
3.        undoblks,            -- 该区间（10 分钟）消耗的 undo 块数
4.        txncount,            -- 区间内事务数
5.        maxquerylen,         -- 区间内最长查询时长（秒）★ 1555 的核心指标
6.        maxconcurrency,      -- 最大并发事务数
7.        tuned_undoretention, -- 实际生效的保留秒数 ★ 不是 undo_retention
8.        nospaceerrcnt,       -- 因 undo 空间不足失败次数
9.        ssolderrcnt          -- 区间内 ORA-01555 次数 ★
10. FROM   v$undostat
11. WHERE  begin_time > SYSDATE - 1
12. ORDER  BY begin_time;

复制代码

读法只有三条：

• maxquerylen > tuned_undoretention：结构性不匹配，长查询迟早撞上覆盖，这不是概率问题，是时间问题。
  
• ssolderrcnt > 0：区间内真实发生了 1555，把时间点和当天的批量作业对上。
  
• nospaceerrcnt > 0：同时存在空间不足，说明已经长期跑在"必须覆盖"的状态里。
  

本例跑出来的结果：tuned_undoretention 在 380～430 之间波动，夜间批处理时段的 maxquerylen 是 2874 秒，ssolderrcnt 当天累计 7 次。结论已经很清楚，不需要再猜。

步骤 2：算清楚到底需要多少空间，别拍脑袋

Oracle 11.2 起自带 DBMS_UNDO_ADV 包，比经验估算靠谱：

2. -- 按你期望的保留时间，反推需要多大的 undo 表空间
3. SET LONG 10000
4. SELECT * FROM TABLE(DBMS_UNDO_ADV.UNDO_ADVISOR(10800));   -- 传期望保留秒数
6. -- 未过期 undo 块的历史曲线（判断空间是否吃紧）
7. SELECT * FROM TABLE(DBMS_UNDO_ADV.UNEXPIREDBLKS()) ORDER BY 1;

复制代码

同时用 v$undostat 做一次粗算，两边对照：

2. SELECT ROUND(MAX(undoblks) * 8 / 1024, 1) AS peak_mb_per_10min,
3.        ROUND(MAX(undoblks) * 8 / 1024 / 600, 2) AS peak_mb_per_sec
4. FROM   v$undostat
5. WHERE  begin_time > SYSDATE - 7;

复制代码

峰值速率 × 期望保留秒数，就是必须能同时容纳的 undo 量。本例峰值速率约 1.9 MB/s，保留 3 小时需要约 20GB——而当时数据文件虽然 32GB，但 autoextend 的 maxsize 就卡在 32GB，已经触顶，这正是"第 4 步必然发生"的直接原因。

步骤 3：揪出两拨人——跑得久的和刷得猛的

2. -- 正在跑的长时间活跃会话（长查询的嫌疑名单）
3. SELECT s.sid, s.serial#, s.username, s.sql_id,
4.        s.last_call_et, s.event,
5.        SUBSTR(q.sql_text,1,90) AS sql_text
6. FROM   v$session s LEFT JOIN v$sql q ON q.sql_id = s.sql_id
7. WHERE  s.status = 'ACTIVE'
8. AND    s.last_call_et > 600
9. AND    s.type = 'USER'
10. ORDER  BY s.last_call_et DESC;
12. -- 此刻 undo 占用最大的事务（刷得猛的嫌疑名单）
13. SELECT s.sid, s.username, s.program,
14.        ROUND(t.used_ublk * 8 / 1024) AS undo_mb,
15.        t.used_urec,
16.        t.status
17. FROM   v$transaction t JOIN v$session s ON s.taddr = t.addr
18. ORDER  BY t.used_ublk DESC
19. FETCH FIRST 10 ROWS ONLY;

复制代码

本例第二条查出来一个 sid=412 的 ETL 会话，单个事务 undo_mb 到过 6.8GB——它在做一次没有索引外键的批量 DELETE，每次删除子表记录都要去检查父表引用，产生了几倍于数据量的 undo。这个会话就是每晚把 undo 顶到上限的那只手。

顺手记一下历史 1555 的时间点，方便和作业日志对齐：

2. # Linux
3. grep -n "ORA-01555" $ORACLE_BASE/diag/rdbms/*/*/trace/alert_*.log | tail -20

复制代码

2. # Windows
3. Select-String -Path "$env:ORACLE_BASE\diag\rdbms\*\*\trace\alert_*.log" -Pattern "ORA-01555" | Select-Object -Last 20

复制代码

步骤 4：按优先级治理

第一优先级：让保留时间真正生效，同时把空间给够（两者必须同时做）

2. -- 1) 先扩空间，别先改参数；maxsize 一定要留余量，触顶等于必然覆盖
3. ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
4.   AUTOEXTEND ON NEXT 512M MAXSIZE 32G;
6. ALTER TABLESPACE undotbs1
7.   ADD DATAFILE '/u01/app/oracle/oradata/orcl/undotbs02.dbf'
8.   SIZE 8G AUTOEXTEND ON NEXT 512M MAXSIZE 16G;
10. -- 2) 再抬保留期
11. ALTER SYSTEM SET undo_retention = 10800 SCOPE = BOTH;   -- 3 小时

复制代码

改完不要急着关自动调优。先观察一天 v$undostat.tuned_undoretention 是否真的上去了；如果空间给足后它仍然被压得很低，可以在评估过空间余量、且明确知道后果的前提下关闭：

2. ALTER SYSTEM SET "_undo_autotune" = FALSE SCOPE = BOTH;
3. -- 关闭后 tuned_undoretention 恒等于 undo_retention；
4. -- 空间不够时不再"静默覆盖"，而是事务直接报 ORA-30036 —— 从隐性问题变成显性问题

复制代码

第二优先级：把刷得猛的事务管住（本例的真正大头）

• 给外键列补索引。无索引外键的 DELETE / UPDATE 父表会产生大量 undo，这是最容易被忽略的一条。
  
• 大批量 DML 分批执行，但分批的粒度要配合长查询，见第五节的坑 2。
  

第三优先级：让长查询不再那么长

• 报表 SQL 做分区裁剪、加覆盖索引，把 2874 秒压到 2000 秒以内；
  
• 报表类只读查询改走 ADG 备库（ACTIVE DATABASE），主库不再承担四十分钟的一致性读——这是最干净的一刀。
  

步骤 5：用同一组指标验证

指标	治理前	治理后
tuned_undoretention	412 s	10800 s
夜间 maxquerylen	2874 s	1180 s（报表改走备库）
UNDOTBS1 使用率峰值	98%（触顶）	57%
ORA-01555 次数 / 日	7	0
ETL 单事务最大 undo	6.8 GB	1.1 GB（补了外键索引 + 分批）
出账作业耗时	含重跑 2 h 40 m	一次通过 38 m

关键点是最后一行：1555 治理好了，观感上是"报错没了"，实质上是一个本来要跑三遍的作业变成了一遍。

四、实操检查清单

• [ ] 确认 v$undostat.tuned_undoretention 的真实值，不要只看 undo_retention 参数。
  
• [ ] 把 maxquerylen 与 tuned_undoretention 摆在一起比较，判断是否结构性不匹配。
  
• [ ] 核对 dba_data_files 的 autoextensible 与 maxbytes，确认 undo 有没有已经触到自动扩展上限。
  
• [ ] 查 v$undostat.ssolderrcnt 与 nospaceerrcnt，分清当前主要矛盾是"保留期不够"还是"空间不够"。
  
• [ ] 用 DBMS_UNDO_ADV.UNDO_ADVISOR 估算所需 undo 大小，不靠"给两小时就行"这类经验值。
  
• [ ] 用 v$transaction.used_ublk 排前 10，确认没有超大事务在无节制地刷 undo。
  
• [ ] 检查所有外键列是否都有索引，尤其是被批量删除/更新的父表。
  
• [ ] 用 v$session.last_call_et 抓长时间活跃会话，评估这些查询能否改走备库或物化视图。
  
• [ ] 检查 dba_tablespaces.retention 是否为 GUARANTEE，明确它对长事务的副作用。
  
• [ ] 扩 undo 数据文件时把 maxsize 留出余量，并加监控：剩余空间低于 20% 即告警。
  
• [ ] 修改 undo_retention 后连续观察三天 v$undostat，确认生效值真的抬上去了。
  
• [ ] 在告警系统里为 alert log 中的 ORA-01555 / ORA-30036 配置即时通知，不留静默失败。
  

五、几个容易踩的坑

坑 1：只调 undo_retention，不加空间。
自动调优还在生效时，这个参数更像"上限"而不是"保证"。空间不变，tuned_undoretention 就不会变，改了等于没改。

坑 2：把"分批提交"当成万能解药。
这是最反直觉的一条。拆分大事务确实能降低单个事务的 undo 峰值，但提交太频繁意味着 undo 空间被更快地标记为可复用。对一个要跑四十分钟的查询来说，一边是它还在读旧镜像，另一边是成百上千个小事务不停把空间释放掉、逼着 Oracle 去覆盖——结果反而更容易报 1555。正确做法是让分批的节奏与长查询窗口错开（比如报表期间暂停该表的高频 DML），或者干脆把长查询挪到备库。

坑 3：autoextend 开了就不管 maxsize。
自动扩展给人"不会满"的错觉，但 maxsize 一到，系统立刻从第 3 步跌到第 4 步——从"从容保留"变成"必须覆盖"。很多"昨天还好好的，今天突然报 1555"就是这个转折点。

坑 4：把 ORA-30036 和 ORA-01555 当同一件事治。
30036 是空间不够，要加、要腾；1555 是空间被覆盖，要留时间、要缩短查询。一个往上加参数、一个往下减时长，方向完全相反。只看"报错了"就动手，很可能把一个问题治成两个。

坑 5：随手把 _undo_autotune 关掉。
它是隐含参数，关掉后 tuned_undoretention 恒等于 undo_retention，好处是行为可预期；但如果空间不够，原来"静默覆盖"的部分会变成事务直接失败。要关，就得先把空间算够。

坑 6：给 undo 表空间设 RETENTION GUARANTEE 图省心。
开了它，长查询确实稳了，因为 Oracle 宁可让写事务失败也不覆盖未过期 undo。代价是大批量作业在 undo 吃紧时直接报 30036 崩在业务高峰——把失败从报表挪到了交易，可能是更糟的交易。

坑 7：指望用闪回查询绕过这个问题。
AS OF TIMESTAMP 这类闪回查询本身就要读 undo 构造历史版本，它依赖的正是那块可能被覆盖的前镜像。1555 的成因它一个都躲不掉，反而因为容易写进报表逻辑而更难发现。