17,133
社区成员
发帖
与我相关
我的任务
分享游标记录数量太大，时间太长，造成ORA-01555错误，怎么解决？
各位高手，现在做的系统中有一个用户表，1700多万条记录，另有一个用户信息表，与用户表的ID对应，现在要根据用户表来插入用户信息表的内容，对用户表使用了一个游标，每循环一次，取得相应信息，写入到用户信息表中，用户信息表的字段比较多，每个循环里面的操作挺多，并且不能放到循环外面，现在的运行时间很久，并且经常会出现ORA-01555错误，这样的情况怎么解决？
...全文
请发表友善的回复…
发表回复
kinlin 2008-08-21
- 打赏
- 举报
1700万条,每次取出100万条，然后批插入或批更新，可能会好些
qfsb_p 2008-08-20
- 打赏
- 举报
用这个东东理论上来说是性能是要高一些的，就好像是jdk中的preparestatment一样，这样可以通过减少在PL/SQL和SQL引擎之间的上下文切换来提高了效率，不过我没有用过。
linzi 2008-08-08
- 打赏
- 举报
使用Forall、bulk collect性能会不会提高？
linzi 2008-08-05
- 打赏
- 举报
[Quote=引用 7 楼 vc555 的回复:]
这个UNDO_RETENTION = 36000只是个期望值。如果你的撤销表空间不够大，这个参数也不顶用了。
如果实在没别的方法，你就配合retention guarantee来用。
[/Quote]
Undotablespace表空间很大，原来设计的时候设置成不限制大小，现在已经到了70G了，数据库是9i的，UNDO_RETENTION的大小也应该有限制，不能设置太大的值吧？
这个UNDO_RETENTION = 36000只是个期望值。如果你的撤销表空间不够大，这个参数也不顶用了。
如果实在没别的方法，你就配合retention guarantee来用。
[/Quote]
Undotablespace表空间很大，原来设计的时候设置成不限制大小，现在已经到了70G了，数据库是9i的，UNDO_RETENTION的大小也应该有限制，不能设置太大的值吧？
lynx 2008-08-05
- 打赏
- 举报
把大业务的commit分成多个子commit。
我以前也是这样做的，分成小块一部分一部分的运行。
我以前也是这样做的，分成小块一部分一部分的运行。
vc555 2008-08-05
- 打赏
- 举报
这个UNDO_RETENTION = 36000只是个期望值。如果你的撤销表空间不够大，这个参数也不顶用了。
如果实在没别的方法，你就配合retention guarantee来用。
如果实在没别的方法，你就配合retention guarantee来用。
linzi 2008-08-05
- 打赏
- 举报
1700万数据都要更新，UNDO_RETENTION = 36000了，现在是每5万条提交一次，原来字段数量少的时候才几分钟，后来增加了近三十个字段，5万条数据竟然要运行1个小时，已经分成若干个小块分别运行了，但是每次只能同时运行两三个小块，如果再多运行几个，同样会出现ORA-01555错误。
kelsoncong 2008-08-05
- 打赏
- 举报
增加undo空间和undo_retention
oracledbalgtu 2008-08-05
- 打赏
- 举报
1.在空闲时间做，避免其它事务报0ra-01555
2.增加undo空间和undo_retention。 只要足够大都能解决.
3.把大业务的commit分成多个子commit。
[Quote=引用楼主 linzi 的帖子:]
各位高手，现在做的系统中有一个用户表，1700多万条记录，另有一个用户信息表，与用户表的ID对应，现在要根据用户表来插入用户信息表的内容，对用户表使用了一个游标，每循环一次，取得相应信息，写入到用户信息表中，用户信息表的字段比较多，每个循环里面的操作挺多，并且不能放到循环外面，现在的运行时间很久，并且经常会出现ORA-01555错误，这样的情况怎么解决？
[/Quote]
2.增加undo空间和undo_retention。 只要足够大都能解决.
3.把大业务的commit分成多个子commit。
[Quote=引用楼主 linzi 的帖子:]
各位高手，现在做的系统中有一个用户表，1700多万条记录，另有一个用户信息表，与用户表的ID对应，现在要根据用户表来插入用户信息表的内容，对用户表使用了一个游标，每循环一次，取得相应信息，写入到用户信息表中，用户信息表的字段比较多，每个循环里面的操作挺多，并且不能放到循环外面，现在的运行时间很久，并且经常会出现ORA-01555错误，这样的情况怎么解决？
[/Quote]
vc555 2008-08-05
- 打赏
- 举报
对于ORA-01555，一般考虑：
1、优化SQL，如不能优化，就把大查询分为小块分别进行。
2、加大回滚段或撤销段
3、用撤销段时增大UNDO_RETENTION参数值，该值为过期数据在撤销段中保留时间。
4、10g的话，配合第三点可在撤销表空间上启用撤销保留保证retention guarantee特性。
1、优化SQL，如不能优化，就把大查询分为小块分别进行。
2、加大回滚段或撤销段
3、用撤销段时增大UNDO_RETENTION参数值，该值为过期数据在撤销段中保留时间。
4、10g的话，配合第三点可在撤销表空间上启用撤销保留保证retention guarantee特性。
caoleione 2008-08-05
- 打赏
- 举报
增加回滚段空间 在循环游标时 进行批量提交 不是1700万数据都要循环更新吧 尽可能减少更新范围
cosio 2008-08-05
- 打赏
- 举报
[Quote=引用楼主 linzi 的帖子:]
各位高手，现在做的系统中有一个用户表，1700多万条记录，另有一个用户信息表，与用户表的ID对应，现在要根据用户表来插入用户信息表的内容，对用户表使用了一个游标，每循环一次，取得相应信息，写入到用户信息表中，用户信息表的字段比较多，每个循环里面的操作挺多，并且不能放到循环外面，现在的运行时间很久，并且经常会出现ORA-01555错误，这样的情况怎么解决？
[/Quote]
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.
各位高手，现在做的系统中有一个用户表，1700多万条记录，另有一个用户信息表，与用户表的ID对应，现在要根据用户表来插入用户信息表的内容，对用户表使用了一个游标，每循环一次，取得相应信息，写入到用户信息表中，用户信息表的字段比较多，每个循环里面的操作挺多，并且不能放到循环外面，现在的运行时间很久，并且经常会出现ORA-01555错误，这样的情况怎么解决？
[/Quote]
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.
qfsb_p 2008-08-05
- 打赏
- 举报
数据量如此庞大的表是不建议有太多字段的，否则处理起来很慢。
这样的场景除了在io上进行优化之外，主要的是程序设计上的优化，而程序的优化也需要根据你们自己的业务需求而来，比如
如果对于速度要求不高，而是要求稳定性的话，可以考虑每次先从用户表中取出若干条记录的主键存到一个队列中，然后针对
队列中的每条记录进行单独的处理，比如根据主键从用户表中读取，然后写入用户信息表。
当然如果能采用多线程编程，有一个线程不停的往队列中增加新的主键记录，而另一个或多个负责进行数据处理效果更好。
这样的场景除了在io上进行优化之外，主要的是程序设计上的优化，而程序的优化也需要根据你们自己的业务需求而来，比如
如果对于速度要求不高，而是要求稳定性的话，可以考虑每次先从用户表中取出若干条记录的主键存到一个队列中，然后针对
队列中的每条记录进行单独的处理，比如根据主键从用户表中读取，然后写入用户信息表。
当然如果能采用多线程编程，有一个线程不停的往队列中增加新的主键记录，而另一个或多个负责进行数据处理效果更好。
lws0472 2008-08-05
- 打赏
- 举报
这样操作肯定需要很大的undo表空间,建议将程序多分几段运行,一次只处理表中的部分数据,并且每一部分处理完后就commit
