【常见错误分析】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 条评论
登录 后参与评论

相关文章

来自专栏james大数据架构

SQL SERVER 内存分配及常见内存问题 简介

一、问题: 1、SQL Server 所占用内存数量从启动以后就不断地增加:       首先,作为成熟的产品,内存溢出的机会微乎其微。对此要了解SQL SER...

23710
来自专栏java架构师

Hadoop学习17--yarn配置篇-内存管理

这篇文章来自于:董的博客,记录备查 内存管理,主要是管理nodemanager上的物理内存和虚拟内存。 YARN允许用户配置每个节点上可用的物理内存资源,注意,...

2838
来自专栏鬼谷君

raid详解

832
来自专栏云霄雨霁

概念题知识点总结

1640
来自专栏PingCAP的专栏

TiDB Best Practice

本文档用于总结在使用 TiDB 时候的一些最佳实践,主要涉及 SQL 使用、OLAP/OLTP 优化技巧,特别是一些 TiDB 专有的优化开关。建议先阅读讲解 ...

7210
来自专栏沃趣科技

Oracle数据库12cR2版本的SQL计划管理

文章翻译自ORACLE WHITE PAPER SQL Plan Management with Oracle Database 12c Release 2 概...

33610
来自专栏决胜机器学习

《高性能MySQL》读书笔记(二) ——MySQL存储引擎概述

《高性能MySQL》读书笔记(二)——MySQL存储引擎概述 (原创内容,转载请注明来源,谢谢) 一、基础信息 mysql将数据库保存在数据目录下...

3625
来自专栏数据和云

Oracle 12.2新特性掌上手册 - 第七卷 Big Data and Data Warehousing

编辑手记:也许Oracle 12.2在内核上的智能改进只能让你眼前一亮,那今天基于Big Data和数据仓库的性能优化增强则会让你伸手触Oracle的强大灵魂。...

2777
来自专栏杨建荣的学习笔记

持续近7个小时的索引扫描的查询优化分析 (r5笔记第44天)

昨天客户的DBA反映有一个数据抽取的任务持续了很长时间最后超时退出了,让我看看有什么地方可以调优一下。 找到了对应的日志,发现在一个大表抽取的时候,抽取持续了将...

4055
来自专栏Python

Linux查看物理CPU个数、核数、逻辑CPU个数

一个物理封装的CPU(通过physical id区分判断)可以有多个核(通过core id区分判断)。 而每个核可以有多个逻辑cpu(通过processor区...

1050

扫码关注云+社区