DB / Oracle / 性能诊断

Oracle Undo 与 Redo 调优

2026.09.06

Oracle Undo 与 Redo 调优

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

  • Redo：记录变更，保证持久性
  • Undo：记录前镜像，保证回滚和一致性

2. Redo 调优

2.1 Redo Buffer

SELECT bytes / 1024 / 1024 AS mb FROM v$sgainfo WHERE name = 'Redo Buffers';

-- 调整
ALTER SYSTEM SET log_buffer = 64M SCOPE=SPFILE;

2.2 Redo 日志组

SELECT 
  group#,
  thread#,
  sequence#,
  members,
  bytes / 1024 / 1024 AS mb,
  status,
  archived
FROM v$log;

2.3 大小调整

目标：每 15-30 分钟切换一次
-- 增大
ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/redo/redo04.log') SIZE 2G;

-- 删除旧的
ALTER DATABASE DROP LOGFILE GROUP 1;

2.4 多路复用

ALTER DATABASE ADD LOGFILE MEMBER 
  '/u02/redo/redo01b.log' TO GROUP 1,
  '/u02/redo/redo02b.log' TO GROUP 2;

2.5 等待事件

SELECT 
  event,
  total_waits,
  time_waited,
  average_wait
FROM v$system_event
WHERE event IN (
  'log file sync',
  'log file parallel write',
  'log buffer space',
  'log file switch (checkpoint incomplete)',
  'log file switch (archiving needed)'
);

2.6 调优

-- 1. log file sync
-- - 批量提交
-- - 高性能 Redo 磁盘

-- 2. log buffer space
-- - 增大 LOG_BUFFER
ALTER SYSTEM SET log_buffer = 128M SCOPE=SPFILE;

-- 3. log file switch (checkpoint incomplete)
-- - 增大 Redo 日志
-- - 优化检查点
ALTER SYSTEM SET fast_start_mttr_target = 300;

3. Undo 调优

3.1 Undo 表空间

SELECT 
  tablespace_name,
  file_name,
  bytes / 1024 / 1024 AS mb,
  autoextensible,
  maxbytes / 1024 / 1024 AS max_mb
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%';

3.2 自动 Undo

SHOW PARAMETER undo

ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH;
ALTER SYSTEM SET undo_tablespace = UNDOTBS1 SCOPE=BOTH;

3.3 Undo 使用

SELECT 
  tablespace_name,
  status,
  SUM(bytes) / 1024 / 1024 AS mb
FROM dba_undo_extents
GROUP BY tablespace_name, status;
-- UNEXPIRED: 未过期（可回滚）
-- EXPIRED: 已过期（可重用）
-- ACTIVE: 活跃事务

3.4 Undo 顾问

SELECT 
  to_char(begin_time, 'HH24:MI') AS begin_time,
  to_char(end_time, 'HH24:MI') AS end_time,
  tuned_undoretention,
  maxquerylen,
  maxqueryid
FROM v$undostat;

3.5 调整 Undo 大小

-- 1. 查看最长查询
SELECT MAX(maxquerylen) FROM v$undostat;

-- 2. 调整 undo_retention
ALTER SYSTEM SET undo_retention = 3600;

-- 3. 启用 GUARANTEE
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

4. ORA-01555 快照过旧

4.1 原因

  • 查询时间过长
  • Undo 数据被覆盖
  • undo_retention 太小

4.2 解决

-- 1. 增大 Undo 表空间
ALTER TABLESPACE undotbs1 ADD DATAFILE '/u01/oradata/orcl/undo02.dbf' SIZE 5G;

-- 2. 增大 undo_retention
ALTER SYSTEM SET undo_retention = 7200;

-- 3. 优化长查询
-- - 加索引
-- - 分批
-- - 减少数据量

-- 4. GUARANTEE
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

5. Redo 性能优化

5.1 Redo 日志磁盘

-- 单独磁盘
-- 高 IOPS（SSD）
-- RAID 1+0

5.2 批量提交

-- 减少 commit 频率
-- 批量操作
FORALL i IN 1..v_count
  INSERT INTO ...
COMMIT;

5.3 NOLOGGING 操作

-- 大批量加载
ALTER TABLE big_table NOLOGGING;
INSERT /*+ APPEND */ INTO big_table SELECT * FROM source;
ALTER TABLE big_table LOGGING;

5.4 直接路径

-- INSERT /*+ APPEND */ 不产生 undo
-- 仅 redo（NOLOGGING 时也不产生）

6. Undo 调优场景

6.1 长查询

-- 1. 确保 undo_retention > 查询时间
-- 2. 增大 Undo 表空间
-- 3. 优化查询

6.2 大事务

-- 1. 检查 Undo 使用
SELECT 
  s.sid,
  s.username,
  t.used_ublk,
  t.used_urec
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;

-- 2. 分批提交

6.3 Flashback

-- 需要 Undo 数据
-- 增大 undo_retention
ALTER SYSTEM SET undo_retention = 86400;  -- 1 天

7. 监控

7.1 Redo 生成速率

SELECT 
  to_char(begin_time, 'HH24:MI') AS begin_time,
  to_char(end_time, 'HH24:MI') AS end_time,
  redoblocks,
  redosize / 1024 / 1024 AS redo_mb
FROM v$sysmetric_history
WHERE metric_name = 'Redo Generated Per Sec'
ORDER BY begin_time DESC;

7.2 Undo 使用

SELECT 
  username,
  program,
  used_ublk * 8 / 1024 AS undo_mb
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr
ORDER BY used_ublk DESC;

8. 常见坑与排错

8.1 ORA-01555

-- 1. 增大 Undo
-- 2. 增大 undo_retention
-- 3. 优化长查询

8.2 ORA-30036: Undo 空间不足

-- 1. 增加 Undo 文件
-- 2. 启用 AUTOEXTEND
-- 3. 优化大事务

8.3 log file sync 高

-- 1. 批量提交
-- 2. 高性能磁盘
-- 3. 检查 commit 频率

8.4 log file switch 频繁

-- 1. 增大 Redo
-- 2. 减少事务频率

9. 最佳实践

9.1 Redo

  1. Redo 独立磁盘：性能
  2. 多组多路复用：可靠
  3. 适当大小：15-30 分钟切换
  4. 批量提交：减少 sync
  5. NOLOGGING 大批量：性能

9.2 Undo

  1. 自动 Undo 管理：默认
  2. undo_retention 合理：长查询
  3. 空间足够：避免 1555
  4. GUARANTEE：Flashback
  5. 监控长事务：避免占用

10. 参考资料

[1] Oracle Database Administrator’s Guide 19c, “Managing Undo” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-undo.html

[2] Oracle Database Administrator’s Guide 19c, “Managing Redo Logs” https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-redo-logs.html