ORA-01555系列：三、ORA-01555总结与高级优化建议

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

在前两章中，我们深入探讨了ORA-01555的原理、场景和基础解决方案。本章将进行全面的总结，并提供一套从主动预防到高级诊断的完整策略，帮助你构建一个对ORA-01555有强大“免疫力”的数据库环境。

3.1 ORA-01555的本质回顾与深度剖析

要彻底征服ORA-01555，必须深刻理解其本质。它不仅仅是“快照过旧”，更是数据库在空间压力、时间限制和一致性读三者之间权衡的结果。

• 一致性读的核心 SCN： 当查询开始时，Oracle记录下当前的系统改变号（SCN）。为了保证读一致性，查询访问的任何数据块，其块头的SCN都不能晚于查询开始的SCN。如果晚了，就必须去UNDO段中寻找该SCN之前的“前映像”。
• UNDO的生命周期： UNDO数据分为三种状态：
  1. 活动（Active）： 事务未提交，UNDO必须保留。
  2. 未过期（Unexpired）： 事务已提交，但未超过UNDO_RETENTION设定的时间，Oracle会“尽力”保留。
  3. 已过期（Expired）： 超过UNDO_RETENTION时间，可以被覆盖。
• Oracle的“窃取”逻辑： 当新事务需要UNDO空间时，Oracle的分配逻辑（参考1555说明.txt）是：
  1. 优先使用UNDO表空间中的空闲空间。
  2. 若无空闲，则重用已过期的UNDO区段。
  3. 若仍无空间（例如所有UNDO都未过期），则尝试扩展数据文件（若开启AUTOEXTEND）。
  4. 若无法扩展，这是关键一步，Oracle会“窃取”未过期的UNDO区段来使用。这直接违背了UNDO_RETENTION的意愿，也是大量ORA-01555的直接原因。V$UNDOSTAT中的UNXPSTEALCNT列记录的就是这种“窃取”行为。

---

案例1（深度剖析）：TUNED_UNDORETENTION的“谎言”

• 背景： 一个数据库的UNDO_RETENTION参数设置为7200（2小时），但一个运行了30分钟的查询仍然报了ORA-01555。DBA感到困惑，因为查询时长远小于保留时间。
• 问题： DBA首先检查了V$UNDOSTAT中的TUNED_UNDORETENTION列，发现它的值只有约1800秒（30分钟），与参数设置严重不符。
• 分析： TUNED_UNDORETENTION是Oracle根据当前UNDO表空间的使用压力动态计算出的实际可达到的保留时间。当它的值远小于UNDO_RETENTION参数时，是一个强烈的信号：UNDO表空间空间不足，正在发生“窃取”！ 该DBA随后查询了UNXPSTEALCNT列，发现在错误发生的时间段内，该值持续增长。
• 结论： 问题根源并非查询太长或UNDO_RETENTION设置无效，而是UNDO表空间太小，无法在当前DML负载下满足2小时的保留承诺。诊断时，TUNED_UNDORETENTION和UNXPSTEALCNT比UNDO_RETENTION参数本身更能反映真实情况。

---

3.2 优化与预防的系统性策略

解决ORA-01555的最佳策略是预防。下面是一套从应用到数据库底层的立体化防御体系。

3.2.1 策略一：应用与SQL优化（第一道防线）

这是最重要、最有效、成本最低的防线。

优化建议：

1. 缩短查询时间： 任何超过30分钟的查询都应被视为潜在风险，进行严格审查和优化。
2. 杜绝循环内提交： 严禁在FOR LOOP中执行COMMIT。应改为批量处理，在循环外提交。
3. 采用“先取后算”模式： 应用代码应先快速将所需数据fetch到程序内存中，关闭数据库游标，然后再进行复杂的业务逻辑处理。
4. 显式关闭游标： 确保应用在完成数据提取后，能迅速、显式地关闭数据库游标，释放资源。

---

案例2（新增）：ETL作业引发的连锁反应

• 背景： 一个数据仓库的夜间ETL作业，其中一个步骤是从源表抽取数据，转换后更新到目标表。该步骤的代码逻辑是FOR c IN (SELECT ...)循环，循环体内执行UPDATE，然后COMMIT。
• 问题： 在ETL运行时，一个用于监控数据质量的后台查询（该查询会持续运行）频繁报ORA-01555。
• 分析： 循环内COMMIT导致UNDO块的生命周期极短，刚被使用就立刻变为“可重用”状态。监控查询作为一个长事务，其依赖的UNDO数据在这种高频的回收/重用模式下被迅速覆盖。
• 解决：
  1. 代码重构： 将ETL作业的UPDATE和COMMIT移出循环。改为每处理50000行记录后提交一次。
  2. 使用MERGE语句： 进一步优化，使用单条MERGE语句代替整个FOR LOOP，将多次DML操作合并为一次，大大减少了事务开销和对UNDO的压力。

3.2.2 策略二：UNDO配置与容量规划（坚实地基）

当地基不稳时，上层建筑再精美也无用。

优化建议：

1. 科学规划容量： 根据V$UNDOSTAT的历史数据，找出高峰期每秒产生的UNDO量（UNDO_RATE）和最长查询时间（MAXQUERYLEN），估算所需空间。
2. 开启AUTOEXTEND： 强烈建议为UNDO数据文件开启AUTOEXTEND并设置合理的MAXSIZE，作为应对突发负载的保险。
3. 慎用RETENTION GUARANTEE： 仅在UNDO空间极其充裕，且业务要求对长查询有绝对保障时才考虑开启。开启后，必须配合严格的空间监控，否则有锁死DML操作的风险。
4. 监控核心指标：
   • V$UNDOSTAT.UNXPSTEALCNT：此值 > 0 是最直接的警报，说明空间不足。
   • V$UNDOSTAT.NOSPACEERRCNT：此值 > 0 说明曾发生DML因无法获取UNDO空间而失败的严重错误。
   • DBA_FREE_SPACE中UNDO表空间的剩余空间百分比。

---

案例3（新增）：混合数据文件引发的计算偏差

• 背景： DBA为UNDO表空间增加了数据文件，但一部分开启了AUTOEXTEND，另一部分没有。
• 问题： DBA发现TUNED_UNDORETENTION的值波动极大且不可预测，有时远低于预期，导致偶发ORA-01555。
• 分析： 根据Oracle MOS（1555说明.txt中提及），不建议在UNDO表空间中混合使用可扩展和不可扩展的数据文件，因为这可能干扰Oracle内部对保留时间与空间需求的计算算法，导致TUNED_UNDORETENTION失准。
• 解决： 将所有UNDO数据文件都统一设置为开启AUTOEXTEND，并配置了相近的MAXSIZE。之后TUNED_UNDORETENTION的值变得稳定，并且与实际负载情况相符。

3.2.3 策略三：高级诊断与监控体系（安全网络）

当问题发生时，需要有工具和方法快速定位。

优化建议：

1. 建立历史快照： 对于关键系统，可以设置一个调度作业，每5-10分钟将V$UNDOSTAT和V$SESSION_LONGOPS的关键列快照存储到自定义的日志表中。当ORA-01555发生时，你拥有的不再是瞬时值，而是一段完整的历史记录，可以清晰地看到问题发生前后的变化。
2. 利用AWR报告： AWR报告中的“Undo Segment Summary”部分提供了UNDO段的争用和使用情况，是事后分析的宝贵资料。
3. 善用诊断事件： 当遇到无法解释的ORA-1555（如01555_0.txt中提到的查询时长为0的案例），需要向Oracle Support求助时，可以根据官方建议设置诊断事件来捕获详细的内部信息。例如：

   -- 在提交SR前，根据Oracle工程师的指导使用
   ALTER SYSTEM SET EVENTS '10442 trace name context forever, level 10';
   ALTER SYSTEM SET EVENTS '1555 trace name errorstack level 3';
   

4. ORA-1555诊断脚本： 准备一个诊断脚本，当错误发生时立即执行。脚本应包含：
   • 从alert.log获取错误的精确时间、SQL ID和Query Duration。
   • 根据错误时间查询V$UNDOSTAT的历史记录（DBA_HIST_UNDOSTAT）。
   • 查询DBA_HIST_ACTIVE_SESS_HISTORY，查看在问题时间段内有哪些活跃的DML和长查询。
   • 检查DBA_LOBS和DBA_TABLESPACES，判断是否与LOB或MSSM表空间相关。

---

案例4（新增）：LOB问题的高级诊断

• 背景： 一个应用在查询包含BLOB的表时报ORA-1555，但错误信息中的undo segment name是空的（name ""），这与普通情况不同。
• 问题： DBA增加了UNDO表空间，调整了UNDO_RETENTION，但问题依旧。
• 分析： 根据1555说明.txt的指导，空的undo segment name是LOB相关ORA-1555的典型特征。DBA立即将排查方向转向LOB。他运行了以下查询：

  SELECT l.table_name, l.column_name, l.pctversion, l.retention, t.segment_space_management
  FROM dba_lobs l
  JOIN dba_tables t ON l.tablespace_name = t.tablespace_name
  WHERE l.table_name = 'PROBLEM_TABLE';
  

• 解决： 查询结果显示，该LOB字段的PCTVERSION和RETENTION都是NULL（默认值），且表空间是ASSM。问题在于该LOB的更新非常频繁，默认的PCTVERSION 10很快被耗尽。最终通过ALTER TABLE ... MODIFY LOB ... (PCTVERSION 40);解决了问题。这个案例展示了根据错误信息的细微差别来转换诊断思路的重要性。

3.3 总结：ORA-01555防治终极心法

1. 应用为王： 80%的ORA-01555问题根在应用。优先优化SQL和应用逻辑。
2. 空间是保障： UNDO_RETENTION只是个愿望，充足的UNDO空间才是实现愿望的基础。
3. 监控是眼睛： 别盲目调整参数，相信数据。持续监控UNXPSTEALCNT等核心指标。
4. LOB是特例： 遇到LOB要单独分析，检查PCTVERSION/RETENTION和ASSM。
5. 预防大于治疗： 建立规范、执行代码审查、合理规划任务，将问题扼杀在摇篮里。

通过这套系统的策略，你将不再畏惧ORA-01555，而是能够洞察其本质，从容地构建一个稳定、高效的数据库系统。

------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)

---