ORA-1555错误解决一例
ORA-01555错误是一种在Oracle数据库中很常见的错误。尤其在Oracle 8i及之前的版本最多。从9i开始的undo自动管理,至现在的10g、11g中的undo auto tuning,使得ORA-01555的错误越来越少。但是这个错误,仍然不可避免。而出现ORA-01555错误,通常有2种情况:
第1种情况,是最常见的。解决的办法无非就是增加UNDO表空间大小,优化出错的SQL,或者避免频繁地提交。而第2种情况则是比第1种情况少很多。下面简单描述一下第2种情况发生的情景:
对于上面最后一段,在SCN4大于SCN2的情况下,之后的描述,我提到了几个“可能”,是因为我对此也不能完全确定,Oracle是否还会有其他的方法来判断 事务的提交时间早于SCN2。而我自己的模拟测试始终没有模拟出ORA-01555。我的测试过程是这样子的:
在我的期望中,上面的测试,对于游标处理部分,应该会报ORA-01555错误。但实际测试并没有出现,对于这类情形,看起来Oracle还有其他的机制来发现块上的事务提交时间早于查询开始时间。
虽然测试没有达到预期的结果,但是对于事务提交后块没有清除引起的ORA-01555错误,需要几个充分的条件:表足够大,表上的事务提交后有没有事务清除的块,对大表进行长时间的查询比如全表扫描,查询开始后有大量的事务填充和UNDO表空间和重用了事务表。
而下面则是延迟块清除时引起的ORA-01555错误的一则案例。
首先进行简单的环境介绍,运行在HP-UX环境下的Oracle 10.2.0.3,主机只有4颗比较老的PA-RISC CPU。这个系统的特点是大数据量的批量处理,基本上都是大数据量的插入。每个月的数据有单独的表,表一般都是在几十G以上,大的表超过100G。CPU利用率长期保持在100%。
由于空间限制,需要定期将一些N个月之前的表导出备份到磁带上,然后将表删除。这些表,在导出时是不可能会有DML操作的。由于性能原因,导出时间过长(几个小时以上),在导出时经常会遇到ORA-01555错误而失败。这里不讨论怎么样提高性能使导出时间更短,这里只提出一种方法来解决ORA-01555错误。
从之前对ORA-01555错误的成因分析可以知道,这个ORA-01555错误,正是由于表上存在未清除的事务,同时导出时间过长,UNDO段头的事务表被全部重用,ORACLE在查询到有未清除事务的块时不能确定事务提交时间是否早于导出(查询)开始时间,这时候就报ORA-01555错误。
要解决这个错误,除了提高性能,那么从另一个角度来思考这个问题,可以想办法先清除掉表上的事务(即延迟块清除)。那么我们可以通过一个简单的SELECT语句来解决:
SELECT /*+ FULL(A) */ COUNT(*) FROM BIG_TABLE A;
SELECT COUNT(*),速度显然大大高于SELECT *,所需的时间也更短,出现ORA-01555错误的可能性就非常低了。
注意这里需要加上FULL HINT,以避免查询进行索引快速全扫描,而不是对表进行全表扫描。另外,需要注意的是,这里不能为了提高性能而使用PARALLEL(并行),测试表明,在表上进行并行查询,以DIRECT READ方式读取表并不会清除掉表上的事务。
如果表过大,SELECT COUNT(*)的时间过长,那么我们可以用下面的代码将表分成多个段,进行分段查询。
select dbms_rowid.rowid_create(1, oid1, fid1, bid1, 0) rowid1, dbms_rowid.rowid_create(1, oid2, fid2, bid2, 9999) rowid2 from (select a.*, rownum rn from (select chunk_no, min(oid1) oid1, max(oid2) oid2, min(fid1) fid1, max(fid2) fid2, min(bid1) bid1, max(bid2) bid2 from (select chunk_no, FIRST_VALUE(data_object_id) OVER(PARTITION BY chunk_no ORDER BY data_object_id, relative_fno, block_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) oid1, LAST_VALUE(data_object_id) OVER(PARTITION BY chunk_no ORDER BY data_object_id, relative_fno, block_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) oid2, FIRST_VALUE(relative_fno) OVER(PARTITION BY chunk_no ORDER BY data_object_id, relative_fno, block_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fid1, LAST_VALUE(relative_fno) OVER(PARTITION BY chunk_no ORDER BY data_object_id, relative_fno, block_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) fid2, FIRST_VALUE(block_id) OVER(PARTITION BY chunk_no ORDER BY data_object_id, relative_fno, block_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) bid1, LAST_VALUE(block_id + blocks - 1) OVER(PARTITION BY chunk_no ORDER BY data_object_id, relative_fno, block_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) bid2 from (select data_object_id, relative_fno, block_id, blocks, ceil(sum2 / chunk_size) chunk_no from (select /*+ rule */ b.data_object_id, a.relative_fno, a.block_id, a.blocks, sum(a.blocks) over(order by b.data_object_id, a.relative_fno, a.block_id) sum2, ceil(sum(a.blocks) over() / &trunks) chunk_size from dba_extents a, dba_objects b where a.owner = b.owner and a.segment_name = b.object_name and nvl(a.partition_name, \'-1\') = nvl(b.subobject_name, \'-1\') and b.data_object_id is not null and a.owner = upper(\'&owner\') and a.segment_name = upper(\'&table_name\')))) group by chunk_no order by chunk_no) a);
在上面的代码中trunks变量表示表分为的段数。
代入trunks,owner,table_name三条SQL,执行上面的代码,出来的结果类似如下:
ROWID1 ROWID2 ------------------ ------------------ AAAER9AAIAAABGJAAA AAAER9AAIAAABIICcP AAAER9AAIAAABIJAAA AAAER9AAIAAABMICcP AAAER9AAIAAABMJAAA AAAER9AAIAAABQICcP AAAER9AAIAAABQJAAA AAAER9AAIAAABWICcP AAAER9AAIAAABWJAAA AAAER9AAIAAABaICcP AAAER9AAIAAABaJAAA AAAER9AAIAAABcICcP AAAER9AAIAAABcJAAA AAAER9AAIAAABgICcP AAAER9AAIAAABgJAAA AAAER9AAIAAABkICcP AAAER9AAIAAABkJAAA AAAER9AAIAAABoICcP AAAER9AAIAAABoJAAA AAAER9AAIAAABsICcP
然后对每一个ROWID段执行类似下面的SQL:
SELECT /*+ NO_INDEX(A) */ COUNT(*) FROM BIG_TABLE A WHERE ROWID>=\'AAAER9AAIAAABGJAAA\' AND ROWID< =\'AAAER9AAIAAABIICcP\';
对表进行分段处理,除了此处的用法,完全可以用于手工多进程处理大批量数据。更完整的功能已经在11g中实现,此处不做过多介绍。
对于本文提到的导出数据遇到ORA-01555错误的表,按上述方法处理后,问题得到解决,表顺利导出。
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:老熊 来源: 老熊的三分地-Oracle、UNIX、数据恢复
- 标签: ORA-1555
- 发布时间:2012-04-09 12:25:19
- [67] Go Reflect 性能
- [66] Oracle MTS模式下 进程地址与会话信
- [63] 如何拿下简短的域名
- [61] android 开发入门
- [60] 【社会化设计】自我(self)部分――欢迎区
- [59] IOS安全–浅谈关于IOS加固的几种方法
- [59] 图书馆的世界纪录
- [54] 视觉调整-设计师 vs. 逻辑
- [49] 读书笔记-壹百度:百度十年千倍的29条法则
- [48] 界面设计速成