[讨论贴] 有关ORA-01555的报错,大家有过解决经验和体会的,都来说说吧
这个报错如何完美解决,一直比较困惑我,希望能通过和大家一起讨论来有一个清晰的解决步骤。
报错内容:
ORA-01555 caused by SQL statement below (Query Duration=49186 sec, SCN: 0x0a0b.b7167aa2):(然后跟个SQL)
官方解释:
⑴in 9i/10g, no other good solution for the ora-01555
you should increase the undo_retentions in 9i and increase the undo space size
in 10g, oracle auto tune the undo_retentions thus, you have only choise to increase the undo space size.
⑵
ORA-01555 snapshot too old: rollback segment number string with name "string" too small
Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.
Action: If in Automatic Undo Management mode, increase the setting of undo_retention. Otherwise, use larger rollback segments.
下面是个人解决意见:(希望有经验的能指出我认识不对的地方,谢谢)
1、问题分析:
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800(秒)
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
(1)查看undo_retention 参数值为多少,默认为900秒,从本文档最开始的参数中也可以看到,此参数值为10800,好多高手也都建议将其改为10800,即3个小时。(显然,日志中的那条SQL执行时间几乎全部大于这个时间)
(2)根据undo_tablespace 对应的undo表空间名,执行:
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;查看其中的undo表空间名剩余空间有多少,(这个最好不定时多执行几次查看,因为我们不知道什么时候查看才是undo剩余最小的时候)。
执行:select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;查看其中的undo表空间总大小是多少
2、解决方案建议:
(1)对于上面查到的undo_retention的值,按理说我们需要增大到它到大于执行运行时间最长的事务所需的时间,也就是要大于alert日志中显示的这条SQL的最大执行时间-67288秒,但这值实在太大了,增大undo_retention的值并不是没有代价的,这会带来UNDO过分扩展,难以回收。为性能考虑,先试行将undo_retention的值增大为21600,即6个小时,这样影响不会太大,然后看效果如何。
ALTER SYSTEM SET undo_retention=21600 SCOPE=BOTH;
(2)对于上面查到的undo_tablespace的表空间剩余大小,如果确实空间不足,可适当增大。
而且由于上一步增大了undo_retention的时间,此处增大undo表空间可以说是必然的,除非undo表空间真的剩余很大。
参考:alter tablespace xxx add datafile '/app/xxx/xxx.dbf' size 1000M autoextent on next 50M maxsize 5000M ;
(希望有经验的能指出我认识不对的地方,谢谢)