最近从生产库恢复了一套数据库到测试环境，运行了一天，到第二天的时候，开发的同事反馈业务无法连接。我用sys用户测试能够正常连接，但是执行相关操作都会卡柱不动.最早因为有事儿没有跟着排查，只是重新做了一次恢复，今天问题复现，决定排查一下问题原因。查看日志发现有如下的信息
ORA-01555 caused by SQL statement below (SQL ID: ca0kuurgqgkq8, Query Duration=28991 sec, SCN: 0x0036.6a8f22fc):
DELETE FROM **_ale***uence WHERE execution_time < '2025-03-19 02:00:02'
Thu Mar 20 10:05:22 2025
Thread 1 advanced to log sequence 50 (LGWR switch)
Current log# 10 seq# 50 mem# 0: /u01/app/oracle/oradata/ncdb/redo10.log
Thu Mar 20 10:05:29 2025
Archived Log entry 157744 added for thread 1 sequence 49 ID 0xd9825ff2 dest 1:
Thu Mar 20 10:07:36 2025
Thread 1 advanced to log sequence 51 (LGWR switch)
Current log# 1 seq# 51 mem# 0: /u01/app/oracle/oradata/ncdb/redo01.log
Thu Mar 20 10:07:39 2025
Archived Log entry 157745 added for thread 1 sequence 50 ID 0xd9825ff2 dest 1:
Thu Mar 20 10:10:18 2025
Thread 1 advanced to log sequence 52 (LGWR switch)
Current log# 2 seq# 52 mem# 0: /u01/app/oracle/oradata/ncdb/redo02.log
Thu Mar 20 10:10:23 2025
Archived Log entry 157746 added for thread 1 sequence 51 ID 0xd9825ff2 dest 1:
Thu Mar 20 10:13:53 2025
Thread 1 advanced to log sequence 53 (LGWR switch)
Current log# 3 seq# 53 mem# 0: /u01/app/oracle/oradata/ncdb/redo03.log
Thu Mar 20 10:13:58 2025
Archived Log entry 157747 added for thread 1 sequence 52 ID 0xd9825ff2 dest 1:
Thu Mar 20 10:17:51 2025
Thread 1 cannot allocate new log, sequence 54
Checkpoint not complete
Current log# 3 seq# 53 mem# 0: /u01/app/oracle/oradata/ncdb/redo03.log
Thu Mar 20 10:26:29 2025
Thread 1 advanced to log sequence 54 (LGWR switch)
Current log# 4 seq# 54 mem# 0: /u01/app/oracle/oradata/ncdb/redo04.log
Thu Mar 20 10:26:36 2025
复制从日志可以看出关键记录如下关键信息：
ORA-01555 caused by SQL: DELETE FROM **_ale***uence
WHERE execution_time < '2025-03-19 02:00:02'
(执行时间=28,991秒, SCN: 0x0036.6a8f22fc)
复制该DELETE语句试图清理**_ale***uence表中两天前的数据，但执行时间长达8小时，最终因Undo数据被覆盖而失败。
ORA-01555是Oracle数据库中经典的“快照过旧”（Snapshot Too Old）错误，通常出现在长时间运行的查询或事务中。其本质是Oracle的读一致性机制无法找到足够的Undo数据来重建查询开始时的一致性视图。本文通过一个真实的运维案例，剖析该错误的成因，并提供系统的优化思路。
分析：为什么Undo数据会丢失？
1. 长事务的“蝴蝶效应”
事务时长与Undo占用正相关
单条DELETE操作扫描全表并逐行删除，产生大量Undo日志。Oracle的Undo表空间容量有限，若事务未及时提交，新事务会覆盖旧Undo段。
读一致性机制失效
其他会话在查询该表时，若需要访问被清理的数据版本，但对应的Undo已被覆盖，则触发ORA-01555。
2. 配置缺陷：Undo表空间的“三重困境”
容量不足：Undo表空间文件固定大小，未启用自动扩展。
保留策略失效：UNDO_RETENTION=900（默认15分钟），远低于8小时事务需求。
空间压力：高频DML操作（如批量插入）抢占Undo空间。
3. 索引缺失的全表扫描灾难
execution_time字段无索引，导致DELETE必须扫描全表。假设表有1亿行数据，实际需删除5000万行，全表扫描将产生至少5000万次Undo记录，远超Undo容量。
终结方案：从SQL优化到架构预防
1. SQL优化：化整为零的分批删除术
❌ 危险操作
DELETE FROM **_ale***uence WHERE execution_time < SYSDATE - 2; -- 全表扫描+长事务
复制✅ 安全方案
BEGIN
LOOP
DELETE FROM pub_alertsequence
WHERE execution_time < SYSDATE - 2
AND ROWNUM <= 5000; -- 每批删除5000行
EXIT WHEN SQL%ROWCOUNT = 0;
COMMIT; -- 释放Undo段
DBMS_LOCK.SLEEP(1); -- 缓解锁争用
END LOOP;
END;
复制✏️ 设计要点
批处理：单次删除量需根据Undo空间动态调整（建议总Undo空间的5%~10%）。
休眠机制：避免高频提交导致日志刷新瓶颈。
2. Undo配置调优：空间与时间的平衡艺术
（1）扩容与自动扩展
ALTER TABLESPACE UNDOTS1
ADD DATAFILE '+DATA' SIZE 1G
AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;
复制（2）动态保留策略
sql
– 查询最长查询时间（单位：秒）
SELECT MAX(maxquerylen) FROM v$undostat;
– 设置UNDO_RETENTION
ALTER SYSTEM SET UNDO_RETENTION=3600; – 1小时
（3）监视Undo健康度
sql
Copy Code
SELECT TO_CHAR(begin_time, ‘YYYY-MM-DD HH24:MI’) AS begin_time,
undoblks,
maxquerylen
FROM v$undostat
ORDER BY begin_time DESC;
3. 索引设计：让数据定位提速100倍
为execution_time创建函数索引，加速范围扫描：
sql
Copy Code
CREATE INDEX idx_pub_alertsequence_etime ON pub_alertsequence(execution_time)
TABLESPACE indx;
执行计划对比
无索引：FULL TABLE SCAN + 排序
有索引：INDEX RANGE SCAN → 耗时从小时级降至分钟级
4. 预防体系：建立长效保护机制
SQL审核：禁止未分批次的大规模DELETE/UPDATE。
Undo监控告警：设置空间使用率>80%时触发预警。
定期索引维护：每月分析索引使用率，清理无效索引。
总结：ORA-01555的本质是资源博弈
ORA-01555并非单纯的配置错误，而是事务时长、Undo容量、数据访问模式的综合博弈结果。解决此类问题需遵循以下原则：
控制事务粒度：大事务拆分为可管理的小批次。
预留缓冲空间：Undo容量= (最大事务Undo量 × 2)。
查询路径优化：索引是减少Undo占用的最佳武器。
评论
0 点赞
0 点赞
