【常见错误分析】ORA-01555错误解决一例

ORA-01555错误是一种在Oracle数据库中很常见的错误。尤其在Oracle 8i及之前的版本最多。从9i开始的undo自动管理,至现在的10g、11g中的undo auto tuning,使得ORA-01555的错误越来越少。但是这个错误,仍然不可避免。

ORA-01555错误的原因分析

1、SQL语句执行时间太长,或者UNDO表空间过小,或者事务量过大,或者过于频繁的提交,导致执行SQL过程中进行一致性读时,SQL执行后修改的前镜像(即UNDO数据)在UNDO表空间中已经被覆盖,不能构造一致性读块。

2、SQL语句执行过程中,访问到的块,在进行延迟块清除时,不能确定该块的事务提交时间与SQL执行开始时间的先后次序。

第1种情况,是最常见的。解决的办法无非就是增加UNDO表空间大小,优化出错的SQL,或者避免频繁地提交。

而第2种情况则是比第1种情况少很多。下面简单描述一下第2种情况发生的情景:

  • 有事务大量修改了A表的数据,或者A表的数据虽然被事务少量修改,但是一部分修改过的块已经刷出内存并写到了磁盘上。随即事务提交,提交时刻为SCN1。而提交时有数据块上的事务没有被清除。
  • 在SCN2时刻,开始执行SELECT查询A表,对A表进行全表扫描,而且A表很大。也可能是其他情况,比如是小表,但是是一个游标方式的处理过程,而处理过程中又非常耗时。

注意,这里SCN2与SCN1之间可能相隔了很远,从时间上来说,甚至可能有数十天。不管怎么样,这在SCN1至SCN2时间之间,系统中存在大量的事务,使得UNDO表空间的块以及UNDO段头的事务表全部被重用过。

  • SELECT语句在读A表的一个块时,发现表上有活动事务,这是由于之前的事务没有清除所致。

ORACLE根据数据块中ITL的XID检查事务表,这时会有2种情况

  • XID对应的事务表中的记录仍然存在并发现事务已经提交,可以得到事务准确的提交SCN(commit scn),称为SCN3,等于SCN1。很显然,由于查询的时刻SCN2晚于事务提交的时刻SCN1,那么不需要构造一致性读块。
  • XID对应的事务表中的记录已经被重用,这个时候仍然表明表明事务已经被提交。那么这个时候,Oracle没办法准确地知道事务的提交时间,只能记录为这样一个事实,事务提交的SCN小于其UNDO段的事务表中最近一次重用的事务记录的SCN(即这个事务表最老的事务SCN)。这里称这个SCN为SCN4。
  • SCN4可能远小于SCN2,那是因为事务很早之前就已经提交。也可能SCN4大于SCN2,这是因为SELECT语句执行时间很长,同时又有大量的事务已经将事务表重用。对于后者,很显然,Oracle会认为该事务的提交时间可能在SELECT开始执行之后。这里为什么说可能,是因为ORACLE只能判断出事务是在SCN4之前提交的,并不是就刚好在SCN4提交。而此时,利用UNDO BLOCK进行一致性读数据的构造也很可能失败,因为UNDO BLOCK很可能已经被覆盖,特别是SCN1远小于SCN2的情况下。在这种情况下,ORA-01555错误就会出现。

对于上面最后一段,在SCN4大于SCN2的情况下,之后的描述,我提到了几个“可能”,是因为我对此也不能完全确定,Oracle是否还会有其他的方法来判断事务的提交时间早于SCN2。而我自己的模拟测试始终没有模拟出ORA-01555。

我的测试过程是这样子的:

1、修改表T1,注意T1表已经足够大,比如几十万行数据以上。

2、flush buffer_cache,使未提交的事务修改的块全部刷出内存。

3、提交事务。

4、使用大量的事务(注意这些事务不含表T1),将UNDO表空间填满并确保所有事务表已经被全部重用过。

5、写一段代码,以游标方式打开表T1,在游标的循环中使用dbms_lock.sleep故意增加时间。

6、同时多个JOB会话产生大量与表T1无关的事务,将UNDO表空间填满并确保所有事务表已经被全部重用过。

在我的期望中,上面的测试,对于游标处理部分,应该会报ORA-01555错误。但实际测试并没有出现,对于这类情形,看起来Oracle还有其他的机制来发现块上的事务提交时间早于查询开始时间。

虽然测试没有达到预期的结果,但是对于事务提交后块没有清除引起的ORA-01555错误,需要几个充分的条件:表足够大,表上的事务提交后有没有事务清除的块,对大表进行长时间的查询比如全表扫描,查询开始后有大量的事务填充和UNDO表空间和重用了事务表。

延迟块清除时引发01555错误

首先进行简单的环境介绍,运行在HP-UX环境下的Oracle 10.2.0.3,主机只有4颗比较老的PA-RISC CPU。这个系统的特点是大数据量的批量处理,基本上都是大数据量的插入。每个月的数据有单独的表,表一般都是在几十G以上,大的表超过100G。CPU利用率长期保持在100%。

由于空间限制,需要定期将一些N个月之前的表导出备份到磁带上,然后将表删除。这些表,在导出时是不可能会有DML操作的。由于性能原因,导出时间过长(几个小时以上),在导出时经常会遇到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(*)的时间过长,那么我们可以用下面的代码将表分成多个段,进行分段查询。

在上面的代码中trunks变量表示表分为的段数。 代入trunks,owner,table_name三条SQL,执行上面的代码,出来的结果类似如下:

然后对每一个ROWID段执行类似下面的SQL:

SELECT /*+ NO_INDEX(A) */ COUNT(*) FROM BIG_TABLE A WHERE ROWID>='AAAER9AAIAAABGJAAA' AND ROWID< ='AAAER9AAIAAABIICcP';

对表进行分段处理,除了此处的用法,完全可以用于手工多进程处理大批量数据。更完整的功能已经在11g中实现,此处不做过多介绍。

对于本文提到的导出数据遇到ORA-01555错误的表,按上述方法处理后,问题得到解决,表顺利导出。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-04-18

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏王磊的博客

Microsoft SQL Server 2005 提供了一些工具来监控数据库

--WL 09-07-03 /*Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV)...

3255
来自专栏行者常至

Hibernate 主键介绍

492
来自专栏数据和云

从商用到开源:15个维度,全面剖析DB2与MySQL数据库的差异

编辑手记 MySQL是目前最流行的开源数据库,由于其部署方便,运维简单,被广泛用于互联网的各个领域。随着整体IT架构的变更,传统的金融,电信业务,也逐渐走上从商...

3267
来自专栏数据和云

YH6:Oracle Sharding 知识库

简单来说,Oracle的Sharding技术就是通过分区(Partioning)技术的扩展来实现的。以前一个表的分区可以存在于不同的表空间,现在可以存在于不同的...

2667
来自专栏Spark学习技巧

Phoenix边讲架构边调优

一 基础架构详解 1 概念 讲调优之前,需要大家深入了解phoenix的架构,这样才能更好的调优。 Apache Phoenix在Hadoop中实现OLTP和...

5848
来自专栏IT笔记

Quartz学习笔记(二)存储与持久化操作详细配置

这里使用的是quartz-2.2.2版本,新的版本API跟1.X.X版本还有有不少区别的,这里会在后面的教程讲到。 Quartz 的内存 Job 存储的能力是由...

3465
来自专栏james大数据架构

微软官方提供的用于监控MS SQL Server运行状况的工具及SQL语句

Microsoft SQL Server 2005 提供了一些工具来监控数据库。方法之一是动态管理视图。动态管理视图 (DMV) 和动态管理函数 (DMF) 返...

1847
来自专栏Netkiller

数据库恢复方案

数据库恢复方案 摘要 这里所谈的内容是对备份数据的恢复,不是对损坏数据表的恢复,或者说灾难恢复。 目录 1. 背景 2. 备份方式分析 3. 恢复方案 3.1....

2575
来自专栏乐沙弥的世界

PGA的设置与调整

    PGA,即程序全局区(Program Global Area),是Oracle体系机构的重要组成部分。Oracle 数据库对系统内存的总开销即是PGA+...

502
来自专栏数据和云

如何将MySQL GR 设置为多主模式

在MySQL 5.7.17版本中发布的MySQL Group Replication(后文简称为MGR)被很多人称为MySQL复制方案的正规军,可以一举取代现在...

3236

扫描关注云+社区