ORA-01555: snapshot too old的分析与解决

应用程序抛了如下的异常：

SQL> exec PR_COMPARE_FREEZEBALANCE_TEMP();

begin PR_COMPARE_FREEZEBALANCE_TEMP(); end;

ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small
ORA-06512: at "MOCSACCT.PR_COMPARE_FREEZEBALANCE_TEMP", line 16
ORA-06512: at line 1

在告警日志中发现如下的异常：

[@more@]

Wed Mar 12 20:09:22 2008
ORA-01555 caused by SQL statement below (Query Duration=11325 sec, SCN: 0x000d.7a79d7d1):
Wed Mar 12 20:09:22 2008
SELECT B.ACCTID,NVL(B.FREEZEBALANCE,0) FROM T_ACCTBOOK B
Wed Mar 12 20:09:23 2008

查询undo的相关数据库参数，如下：

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean TRUE
undo_tablespace string UNDOTBS1
SQL>

SQL> select sum(bytes/1024/1024/1024) from dba_data_files where tablespace_name='UNDOTBS1';

SUM(BYTES/1024/1024/1024)
-------------------------
15.9980469

SQL>

对异常ORA-01555的解释如下：

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.
对异常ORA-01555的深入解释如下：

The usual cause of this error is that long-running transactions/queries are occurring within the database at the same time as short online transactions. When the short transactions complete, the rollback segments they have used is up for grabs and can be overwritten. As soon as that area is overwritten, the long-running queries/transactions can no longer maintain a read consistent picture of the data, and they fail with an ORA-1555. It's a scheduling problem. Run batch and long-running jobs at off-hours.

Example Scenario:
1.A long running Query (T1) is started.
2.A quick update (T2) is performed and committed on a table that T1 won't require for another 20 minutes. When T2 is committed it's rollback segment blocks and extents are kept but marked as inactive.
3.Another DML statement is issued (T3). Oracle assigns a rollback segment to T3 using a round robin algorithm. The assigned segment includes the same storage as the one previously used by T2. Thus it overwrites the inactive before-image of T2.
4.T1 now comes to the point in the query where it needs the before-image of the data that was changed by T2.
5.But T1 must read the before-image of the changed data records (for read consistency).
6.T1 attempts to read the before-image left from T2 -- only to find that it has been overwritten by T3.
7.T1 can no longer access the before-image of T2. T1 abends at this point.
8.ORACLE then issues: ORA-1555: snapshot too old (rollback segment too small).

可以采取的措施有：

1、应用程序尽量避免巨表的漫长查询操作，改传统的cursor游标为bulk collect；

2、尽量程序中不要使用大事务量的增删改操作，同时记得及时commit；

3、加大undo表空间和加大undo的retention。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38542/viewspace-1000873/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/38542/viewspace-1000873/