undo的乱78糟

INSERT产生最少的Undo，Update产生的Undo居中，而Delete操作产生的Undo最多。

对于INSERT操作，回滚段只需要记录插入记录的rowid，如果回退，只需将该记录根据rowid删除即可；
对于UPDATE操作，回滚段只需要记录被更新字段的旧值即可（前镜像），回退时通过旧值覆盖新值即可完成回退；
对于DELETE操作，Oracle则必须记录整行的数据，在回退时，Oracle通过一个反向操作恢复删除的数据。

回滚段过小，快照过旧：ORA-01555: snapshot too old: rollback segment number 8 with name ""_SYSSMU8$"" too small

 

一般导致ORA-01555的原因是：
1. 回滚段数量不足，导致回滚段Transaction Slot被overwrite
2. 回滚段剩余空间不足，导致回滚段被overwrite
3. undo_retention(要大于执行运行时间最长的事务所需的时间)设置太小，导致expired回滚段被overwrite.

undo_retention的实现是要取决于undo tablespace的大小，即使undo_retention设置很大，但是undo tablespace的大小不足以支持，还是会出现unexpired回滚段信息被overwrite，从而导致ORA-01555。
回滚段一般有3种状态：active（未提交的事务），expired（已经提交的事务，超过undo_retention)，unexpired(已经提交的事务，超过undo_retention)。expired回滚段有可能在后续的应用中被系统overwrite。

往往是因为跑的时间太长了，好好查查为什么跑这么长时间。业务逻辑不能修改的话就好好优化一下.

1.我最近也碰到了这个问题，使用物化视图解决了。把数据访问量大，表集中的中间结果拿出来了，根据需要定时刷新。
2.先暂时把undo_retention=10800调整为undo_retention=14400
看看还有没有ORA-01555报错。
另外，今天观察AGGRE_PM要跑4个多小时。太慢了，需要报表的人配合做下优化。

 

可能的解决办法：
1. 合理设置undo tablespace大小和undo_retention大小
2. 对于涉及到大数据量的update/delete操作，要分批Commit，减少对回滚段的冲击。
3. 对于查询SQL，要调整执行规划，增加合适的索引来缩短查询时间。

 

 

在oracle 9i中。我们可以根据v$undostat的信息，估算出这个时期需要的undo表空间大小
以下是计算需要的undo表空间平均值的sql语句
select
 ur undo_retention,
 dbs db_block_size,
 ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
  from (select value as ur
          from v$parameter
         where name = 'undo_retention'),
       (select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups
          from v$undostat),
       (select value as dbs
          from v$parameter
         where name = 'db_block_size')
另外，以下是按照峰值计算需要的undo表空间的sql语句
select
 ur undo_retention,
 dbs db_block_size,
 ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
  from (select value as ur
          from v$parameter
         where name = 'undo_retention'),
       (select (undoblks / ((end_time - begin_time) * 86400)) ups
          from v$undostat where undoblks in (select max(undoblks) from  v$undostat ) ),
       (select value as dbs
          from v$parameter
         where name = 'db_block_size')
建议按照峰值估算和分配undo表空间大小，可能会浪费些存储空间，但是能够避免应用程序潜在的undo空间不够问题。

 

v$undostat 中，UNXPBLKREUCNT  Number of unexpired undo blocks reused by transactions一般来说该参数都应该等于0，但是这个参数在那段时间是大于0的。因为只有当undo tablespace不够存放undo_retention时间段内的数据的时候，才会发生unexpired undo extents stealing。

 

 

那么为什么在系统不是很繁忙的时候会出现undo不够用的情况呢，如果说不够用，那在波峰时段应该问题更加严重才对。查看stats$undostat.tuned_undoretention参数发现了问题所在。从10.2版本开始，oracle默认采用自动调整undo retention的方法，根据你undo tablespace的大小以及系统的繁忙程度(v$undostat中信息）自动调整undo_retention参数，所以在10g的数据库上你会经常发现undo tablespace永远是满的，因为当你undo tablespace有空闲空间时，系统自动调大undo_retention来保留更多的undo blocks。这一方法有利于时间长的查询，但是对于典型的OLTP系统来说不太适用，因为OLTP上不太可能跑如此长时间的查询，而且在很繁忙的OLTP上还会导致上面所遇到的问题。oracle真是吃力不讨好。出问题前一天，数据库做维护被重启过，因为刚起来数据库很空闲，所以v$undostat.tuned_autoretention很大，undo tablespace被撑满，虽然tuned_autoretention一直在降，但是还是没有赶上系统warm up的速度，导致数据库出现了问题。

该功能可以通过_undo_autotune参数被disable，disable后v$undostat不在更新。

_undo_autotune : enable auto tuning of undo_retention

该参数可以在线修改：

alter system set “_undo_autotune” = false;