处理ORA-01555:snapshot too old

【实验环境】
操作系统 RedHat 5.5
主机名 lxh
数据库版本 Oracle 10.2.0
字符集 GHS16GBK
实例名 prod
监听 LISTENER/1521

【实验过程】
1、创建undo表空间

SYS@ prod>create undo tablespace undo_small
datafile '/u01/app/oracle/oradata/prod/undo_small01.dbf' size 2m
autoextend off;

2、让系统使用该undo表空间

SYS@ prod>alter system set undo_tablespace = undo_small;

3、创建表

用普通用户创建一张表，我们将建立表T来查询和修改。注意我们在这个表中随机地对数据排序。CREATE TABLE AS SELECT图按查询获取的顺序将行放在块中。我们的目的只是把行弄乱，使它们不至于认为有某种顺序，从而得到随机的分布：
SYS@ prod>conn scott/tiger
SYS@ prod>create table t as select * from all_objects order by dbms_random.random;

4、创建一个主键约束，目的是创建一个索引

SCOTT@ prod>alter table t add constraint t_pk primary key(object_id);

5、收集表的统计信息，目的是让优化器使用索引

SCOTT@ prod>exec dbms_stats.gather_table_stats( user, 'T', cascade=> true );

6、开始修改表

SCOTT@ prod>begin
2 for x in ( select rowid rid from t )
3 loop
4 update t set object_name = lower(object_name) where rowid = x.rid;
5 commit;
6 end loop;
7 end;
8 /

7、运行查询

在运行上面修改的同时，在另一个会话中运行一个查询。这个查询要读表T，并处理每个记录。获取下一个记录之前处理每个记录所花的时间大约为1/100秒（使用DBMS_LOCK.SLEEP(0.01)来模拟，使用前先用SYS给SCOTT赋权）。在查询中使用了FIRST_ROWS提示，使之使用前面创建的索引，从而通过索引（按OBJECT_ID排序）来读出表中的行。由于数据是随机地插入到表中的，我们可能会相当随机地查询表中的块。这个查询只运行几秒就会失败：
SYS@ prod>grant execute on dbms_lock to scott;

SCOTT@ prod>declare
2 cursor c is
3 select /*+ first_rows */ object_name
4 from t
5 order by object_id;
6
7 l_object_name t.object_name%type;
8 l_rowcnt number := 0;
9 begin
10 open c;
11 loop
12 fetch c into l_object_name;
13 exit when c%notfound;
14 dbms_lock.sleep( 0.1 );
15 l_rowcnt := l_rowcnt+1;
16 end loop;
17 close c;
18 exception
19 when others then
20 dbms_output.put_line( 'rows fetched = ' || l_rowcnt );
21 raise;
22 end;
23 /
declare
*
ERROR at line 1:

ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19$" too small

ORA-06512: at line 21

可以看到，在遭遇ORA-01555:snapshot too old错误而失败之前，它只处理了19个记录。

8、解决方案

要修正这个错误，我们要保证做到两点：
1、数据库中UNDO_RETENTION要设置得足够长，以保证这个读进程完成。这样数据库就能扩大undo表空间来保留足够的undo，使我们能够完成工作。
2、undo表空间可以增长，或者为之手动分配更多的磁盘空间。
对于这个例子，我认为这个长时间运行的进程需要大约600秒才能完成。我的UNDO_RETENTION设置为900（单位是秒，所以undo保持大约15分钟）。我修改了undo表空间的数据文件，使之一次扩大10MB，直到最大达到2GB。
SYS@ prod>show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO_SMALL

SYS@ prod>select file#,name,bytes/1024/1024 M from v$datafile;

FILE# NAME M
---------- -------------------------------------------------- ----------
1 /u01/app/oracle/oradata/prod/system01.dbf 500
2 /u01/app/oracle/oradata/prod/undotbs01.dbf 1760
3 /u01/app/oracle/oradata/prod/sysaux01.dbf 290
4 /u01/app/oracle/oradata/prod/users01.dbf 5
5 /u01/app/oracle/oradata/prod/example01.dbf 100
6 /u01/app/oracle/oradata/prod/statspack01.dbf 300
7 /u01/app/oracle/oradata/prod/tbs1.dbf 10
8 /u01/app/oracle/oradata/prod/test3.dbf 1602
9 /u01/app/oracle/oradata/prod/undo_small01.dbf 2

9 rows selected.

设置undo表空间自动扩展，每次扩大10m,最大2G。
SYS@ prod>alter database datafile 9 autoextend on next 10m maxsize 2000m;

9、再次开始修改表

SCOTT@ prod>begin
2 for x in ( select rowid rid from t )
3 loop
4 update t set object_name = lower(object_name) where rowid = x.rid;
5 commit;
6 end loop;
7 end;
8 /

10、再次运行查询

SCOTT@ prod> declare
2 cursor c is
3 select /*+ first_rows */ object_name
4 from t
5 order by object_id;
6 l_object_name t.object_name%type;
7 l_rowcnt number := 0;
8 begin
9 open c;
10 loop
11 fetch c into l_object_name;
12 exit when c%notfound;
13 dbms_lock.sleep( 0.1 );
14 l_rowcnt := l_rowcnt+1;
15 end loop;
16 close c;
17 exception
18 when others then
19 dbms_output.put_line( 'rows fetched = ' || l_rowcnt );
20 raise;
21 end;
22 /

此次更新过程中，查询语句未报错。

【实验总结】

在设置了undo表空间自动扩展之后，我们成功地完成了工作，而且undo扩大得足够大，可以满足我们的需要。
在这个例子中，之所以会得到错误只是因为我们通过索引来读表T，而且在全表上执行随机读。如果不是这样，而是执行全表扫描，在这个特例中很可能不会遇到ORA-01555错误。原因是SELECT和UPDATE都要对T执行全表扫描，而SELECT扫描很可能在UPDATE之前进行（SELECT只需要读，而UPDATE不仅要读还有更新，因此可能更慢一些）。如果执行随机读，SELECT就更有可能要读已修改的块（即块中的多行已经被UPDATE修改而且已经提交）。ORA-01555这个错误的出现取决于并发会话如何访问和管理底层表。

吕星昊
2014.9.10

ITPub博客

【性能优化】处理ORA-01555:Snapshot Too Old

最新文章