ORA-01555系列：二、ORA-01555的场景分析与解决方案

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

本章将深入探讨ORA-01555的四种核心触发场景，为每种场景提供两个详细的案例分析，并附上针对性的优化建议，助你从根源上解决问题。

2.1 场景一：长事务与长查询

场景描述：
这是ORA-01555最经典的诱因。当一个查询的持续时间（Query Duration）超过了UNDO数据的保留时间，或者在查询期间有大量的DML操作，导致查询启动时所需的数据“前映像”（before-image）在UNDO中被覆盖，错误便会发生。

案例1：报表查询与批量更新冲突

背景： 某电商平台在每天凌晨2点运行一个销售额统计报表，该报表需要全表扫描订单表（ORDERS），耗时约1.5小时。同时，另一个批量任务在2:30开始更新前一天的订单状态，涉及数十万行数据。
问题： 报表任务在运行到一半时，频繁报出ORA-01555错误。
分析： 报表查询开始后，批量更新任务产生了大量的UNDO数据，迅速占用了UNDO表空间。由于UNDO空间有限，为了给新的DML操作腾出空间，Oracle覆盖了报表查询所需要的部分旧版本数据。
解决：
1. 时间窗口调整： 将批量更新任务调整到报表任务执行完毕后的4点再开始，错开时间窗口。
2. SQL优化： 优化报表SQL，利用分区表特性只扫描最近的分区，并将全表扫描改为索引扫描，查询时间缩短至20分钟，大大降低了风险。

案例2（新增）：应用层游标处理不当

背景： 一个Java应用需要从一个大表（TRANSACTIONS）中导出数据。开发人员使用了CURSOR FOR LOOP的方式，在循环体内部对每一条查询出的记录进行复杂的业务逻辑处理和网络I/O操作，然后才fetch下一条。
问题： 程序运行几分钟后，总是抛出ORA-01555异常。开发人员很困惑，因为单条SQL在客户端执行很快。
分析： 这个问题的根源在于游标打开（open cursor）和数据提取（fetch data）之间的时间间隔太长。open cursor时查询的快照就已确定，但由于循环体内处理耗时，导致整个fetch过程被拉长到数分钟。在此期间，其他事务对TRANSACTIONS表的修改覆盖了游标所依赖的UNDO数据。
解决：
1. 修改代码逻辑： 建议开发人员调整代码，先将查询结果一次性或分批次fetch到一个集合（如ArrayList）中，完全关闭数据库游标后，再对内存中的数据进行业务处理。
2. 分批处理： 如果数据量过大无法全部加载到内存，则采用分批查询的方式，例如每次查询10000条记录进行处理，处理完再查询下一批。

优化建议

SQL调优是第一要务：
- 通过索引、分区、并行查询等手段，尽一切可能缩短查询的执行时间。查询运行得越快，其受DML影响的“窗口期”就越短。
优化应用逻辑：
- 杜绝“取一条，处理一条”的慢速循环模式。 应该先快速获取数据，再进行业务处理。
- 避免在循环中提交（COMMIT in a loop）。 这会导致UNDO槽位被快速回收，极易引发ORA-01555。
合理调度任务：
- 将长时间运行的查询（如报表、数据抽取）与高频DML操作（如批量更新、数据加载）的时间窗口错开，尽量安排在系统负载低的深夜执行。
拆分大事务：
- 对于需要更新或删除大量数据的操作，应分解为多个小事务，分批提交，避免产生一个巨大的、长时间占有UNDO空间的事务。

2.2 场景二：UNDO空间不足

场景描述：
当UNDO表空间的大小不足以容纳所有事务所产生的UNDO数据，并且无法满足UNDO_RETENTION参数设定的保留时间时，Oracle会为了保证新事务的执行而“牺牲”已提交但未过期的UNDO数据，这个过程称为“窃取”（Steal）。

案例3：UNDO表空间固定大小导致的问题

背景： 某金融企业的数据库UNDO表空间被配置为固定大小（例如20GB），且数据文件未开启AUTOEXTEND。UNDO_RETENTION设置为3600秒（1小时）。
问题： 在一次月末结算期间，由于DML操作量剧增，即使查询的执行时间远小于1小时，也频繁出现ORA-01555。
分析： 通过查询V$UNDOSTAT，发现UNXPSTEALCNT（未过期UNDO块被窃取的次数）列的值在问题发生的时间段内持续大于0。这明确表明，尽管UNDO_RETENTION要求保留1小时，但由于空间严重不足，Oracle被迫窃取了这些本应被保留的UNDO块，导致查询失败。
解决：
1. 紧急扩容： 立即为UNDO表空间增加一个新的数据文件，并开启AUTOEXTEND，缓解了当时的压力。
2. 容量规划： 事后通过分析V$UNDOSTAT中的UNDOBLKS（使用的UNDO块数）和高峰期业务量，重新规划了UNDO表空间的大小，将其扩容至50GB，并保留AUTOEXTEND作为保险。

案例4（新增）：UNDO_RETENTION保证（Guarantee）引发的空间问题

背景： DBA为了彻底解决ORA-01555问题，为UNDO表空间开启了RETENTION GUARANTEE选项。
问题： 一段时间后，数据库中所有的DML操作（INSERT/UPDATE/DELETE）全部挂起，最终失败，并报错ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’。
分析： RETENTION GUARANTEE是一个“双刃剑”。它强制Oracle必须满足UNDO_RETENTION的保留时间，绝不允许“窃取”未过期的UNDO。当UNDO表空间被写满，同时又存在一个长时间运行的查询（或仅仅是UNDO_RETENTION时间未到），导致所有UNDO块都无法被重用时，新的DML操作就无法获取到UNDO空间，从而导致数据库“暂停”DML。
解决：
1. 紧急处理： 临时关闭RETENTION GUARANTEE (ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;)，让DML操作可以继续。
2. 根本解决： 重新评估并大幅增加UNDO表空间的大小，确保其容量足以在GUARANTEE模式下支撑最长查询和高峰DML。通常，不建议轻易开启GUARANTEE，除非你对UNDO容量有十足的把握。

优化建议

科学估算UNDO容量：
- 使用V$UNDOSTAT的历史数据来估算UNDO空间需求。一个简单的公式是：所需空间 = (UNDOKBLKS * BLOCK_SIZE) + (MAXQUERYLEN * UNDO_RATE)。其中UNDO_RATE是每秒产生的UNDO量。
明智地使用AUTOEXTEND：
- 在生产环境中，为UNDO数据文件开启AUTOEXTEND是一个很好的保险策略，可以应对突发的DML洪峰。但必须设置一个合理的MAXSIZE，防止其无限制增长，耗尽磁盘空间。
理解UNDO_RETENTION：
- UNDO_RETENTION只是一个“目标”而非“承诺”（除非开启GUARANTEE）。它的有效性完全取决于是否有足够的空闲UNDO空间。因此，保障空间比单纯调大这个参数更重要。
建立监控告警：
- 对UNDO表空间的使用率设置监控（例如超过85%告警）。
- 对V$UNDOSTAT中的UNXPSTEALCNT和NOSPACEERRCNT（空间不足错误数）设置监控，一旦这些值大于0，就意味着UNDO配置存在问题。

2.3 场景三：并发DML与查询冲突

场景描述：
在高并发系统中，即使单个查询和DML操作都很快，但大量的并发操作在同一时间争抢和回收UNDO资源，也会导致某些查询“不幸地”踩到刚被回收的UNDO块上，从而引发ORA-01555。

案例5：报表查询与批量删除冲突

背景： 某保险公司在月底进行数据归档时，需要批量删除数百万条历史保单。与此同时，财务部门会运行多个报表查询来统计当月的业绩。
问题： 财务报表查询频繁失败，报ORA-01555。
分析： 批量删除操作是一个巨大的事务，产生了海量的UNDO信息。虽然报表查询本身不慢，但它运行期间，删除事务持续进行，导致UNDO空间被迅速消耗和覆盖。
解决：
1. 任务调度： 将批量删除任务调整到所有报表查询结束后的深夜执行。
2. DML优化： 将删除操作改为分批提交，例如每删除10万条数据就COMMIT一次，减小了单个事务对UNDO的冲击。

案例6（新增）：ETL过程中的高频COMMIT

背景： 一个ETL（数据抽取、转换、加载）作业，每从源系统抽取一条数据，经过转换后插入到目标表，然后立刻执行一次COMMIT。同时，一个BI仪表盘每分钟都会刷新，查询这个目标表。
问题： BI仪表盘的查询间歇性地报ORA-01555。
分析： 这种“循环内提交”（COMMIT in a loop）的设计是性能杀手，也是ORA-01555的温床。每一次COMMIT都使得该事务所使用的UNDO块可以被立即重用。对于BI这种持续运行的查询来说，它所依赖的UNDO数据在这种高频回收的模式下极易丢失。
解决：
1. 优化ETL作业： 修改ETL逻辑，改为批量处理。例如，每处理10000条数据后，再执行一次COMMIT。这大大降低了COMMIT的频率，延长了UNDO块的生命周期。
2. 使用物化视图： 为BI仪表盘创建一个物化视图（Materialized View），让查询直接访问物化视图。ETL作业完成后，再刷新物化视图。这样就将查询和DML操作完全分离开来。

优化建议

隔离工作负载：
- 尽可能将OLTP（在线事务处理）和DSS（决策支持系统，如报表查询）分离开。最佳实践是使用读写分离架构，例如在备库（Standby Database）上执行报表查询。
优化提交策略：
- 坚决避免“循环内提交”。这不仅性能低下，还会给UNDO管理带来巨大压力。应遵循“批量处理，一次提交”的原则。
利用数据库特性：
- 对于读多写少的场景，可以考虑使用物化视图来固化查询结果，避免查询与DML的直接冲突。
- 在某些场景下，为查询设置更高的事务隔离级别（如SERIALIZABLE）或使用FLASHBACK QUERY，但需注意这可能带来的性能开销。

2.4 场景四：LOB字段相关问题

场景描述：
LOB（大对象）字段的UNDO管理机制与普通数据类型不同。它不完全使用UNDO表空间，而是有自己的一套基于PCTVERSION或RETENTION的机制。配置不当或对LOB的频繁更新，是LOB相关ORA-01555的根源。

案例7：LOB字段更新导致快照丢失

背景： 某内容管理系统将文章正文存储在CLOB字段中，该字段默认使用PCTVERSION 10（即LOB段空间的10%用于保存旧版本）。编辑人员频繁地对热门文章进行修改和保存。
问题： 在前台查询文章列表（包含部分正文预览）的长轮询请求中，偶尔出现ORA-01555。
分析： PCTVERSION 10意味着只有10%的LOB段空间用来存放“前映像”。对于频繁更新的热门文章，这部分空间很快被用完，旧版本的LOB数据被覆盖，导致长查询无法获得一致性读。
解决：
1. 调整LOB参数： 将该LOB字段的存储参数修改为RETENTION，使其版本保留策略与UNDO_RETENTION参数对齐，提供了更长的版本保留时间。
```
ALTER TABLE articles MODIFY LOB (content) (RETENTION);
```
2. 如果仍有问题，可以考虑增加PCTVERSION的值，例如PCTVERSION 20，但这会消耗更多磁盘空间。

案例8（新增）：RETENTION在MSSM表空间下失效

背景： 一个文档管理系统将文件存储在BLOB字段中，并配置了RETENTION。但是，DBA在创建存放LOB的表空间时，错误地选择了手动段空间管理（Manual Segment Space Management, MSSM）。
问题： 尽管UNDO_RETENTION设置得很高，但查询LOB表时仍然频繁出现ORA-01555，伴随ORA-22924（snapshot too old）。
分析： 这是一个非常隐蔽的陷阱。根据Oracle官方文档（如1555说明.txt中引用的Note 800386.1），当LOB段位于MSSM表空间时，RETENTION参数会被静默地忽略。这意味着LOB的版本保留根本没有生效。
解决：
1. 迁移LOB段： 创建一个新的、使用自动段空间管理（Automatic Segment Space Management, ASSM）的表空间。
2. 使用ALTER TABLE ... MOVE LOB ... STORE AS ... (TABLESPACE ...)命令，将出问题的LOB段迁移到新的ASSM表空间中。迁移后，RETENTION参数便能正常工作。

优化建议

选择正确的LOB版本管理策略：
- RETENTION：推荐用于AUM（自动UNDO管理）环境，它将LOB的版本保留与UNDO_RETENTION挂钩，更易于管理。
- PCTVERSION：一种更直接的基于空间的控制方式。在LOB更新极度频繁的场景下，有时调大PCTVERSION比依赖RETENTION效果更直接，但需要精确估算空间。
必须使用ASSM表空间：
- 存放LOB段的表空间必须使用ASSM，这是RETENTION生效的前提。创建表空间时请务必指定SEGMENT SPACE MANAGEMENT AUTO。
优化LOB操作：
- Oracle官方建议，对LOB的UPDATE操作对UNDO机制的压力最大。应尽量将业务逻辑优化为DELETE后INSERT，或者减少对LOB列的不必要更新。
保证LOB表空间充足：
- 用于版本控制的LOB数据会直接消耗LOB段所在表空间的空间。必须确保该表空间有足够的预留空间来保存旧版本。
——————作者介绍———————–
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)