游标记录数量太大，时间太长，造成ORA-01555错误，怎么解决？

Question

各位高手，现在做的系统中有一个用户表，1700多万条记录，另有一个用户信息表，与用户表的ID对应，现在要根据用户表来插入用户信息表的内容，对用户表使用了一个游标，每循环一次，取得相应信息，写入到用户信息表中，用户信息表的字段比较多，每个循环里面的操作挺多，并且不能放到循环外面，现在的运行时间很久，并且经常会出现ORA-01555错误，这样的情况怎么解决？

Answer 1

ORA-01555 snapshot too old: rollback segment number string with name "string" too smallCause: 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.

Answer 2

对于ORA-01555，一般考虑：
1、优化SQL，如不能优化，就把大查询分为小块分别进行。
2、加大回滚段或撤销段
3、用撤销段时增大UNDO_RETENTION参数值，该值为过期数据在撤销段中保留时间。
4、10g的话，配合第三点可在撤销表空间上启用撤销保留保证retention guarantee特性。

Answer 3

1.在空闲时间做，避免其它事务报0ra-01555
2.增加undo空间和undo_retention。 只要足够大都能解决.
3.把大业务的commit分成多个子commit。

Answer 4

1700万数据都要更新，UNDO_RETENTION = 36000了，现在是每5万条提交一次，原来字段数量少的时候才几分钟，后来增加了近三十个字段，5万条数据竟然要运行1个小时，已经分成若干个小块分别运行了，但是每次只能同时运行两三个小块，如果再多运行几个，同样会出现ORA-01555错误。

Answer 5

这个UNDO_RETENTION = 36000只是个期望值。如果你的撤销表空间不够大，这个参数也不顶用了。
如果实在没别的方法，你就配合retention guarantee来用。

Answer 6

把大业务的commit分成多个子commit。 
我以前也是这样做的，分成小块一部分一部分的运行。

Answer 7

Undotablespace表空间很大，原来设计的时候设置成不限制大小，现在已经到了70G了，数据库是9i的，UNDO_RETENTION的大小也应该有限制，不能设置太大的值吧？

Answer 8

这样操作肯定需要很大的undo表空间,建议将程序多分几段运行,一次只处理表中的部分数据,并且每一部分处理完后就commit

Answer 9

使用Forall、bulk collect性能会不会提高？

Answer 10

用这个东东理论上来说是性能是要高一些的，就好像是jdk中的preparestatment一样，这样可以通过减少在PL/SQL和SQL引擎之间的上下文切换来提高了效率，不过我没有用过。