疑难解答:ORA-01555的场景模拟和解决方案

黄玮(Fuyuncat)

资深Oracle DBA,个人网站www.HelloDBA.com,致力于数据库底层技术的研究,其作品获得广大同行的高度评价.

前期分享了ORA-01555错误的原理及相关基础概念。

参考(高频错误:ORA-01555深入剖析)

今天我们来分析ORA-01555发生的场景和各种解决方案

1555发生的场景

测试环境

首先建立测试环境。由于我们只是要模拟1555错误的发生,所以需要建立一个小的回滚表空间,并且设置undo_retention时间为1(秒),以便回滚数据尽快被覆盖(呵呵,要防止1555错误发生,这就一定要避免的)。

1

一致性读导致的1555错误

开始读取表。

SQL> var cl refcursor SQL> begin open :cl for select * from demo.t_multiver; end; / PL/SQL procedure successfully completed.

更新表数据,产生回滚信息。

SQL> update demo.t_multiver set b = 111 where a = 1; 1 row updated. SQL> commit; Commit complete.

运行大批其他事务,充满所有回滚段,以致覆盖上面的回滚信息。回滚段可以通过dba_rollback_segs查看。

SQL> begin for i in 1..20000 loop update demo.t_dual set dummy=1; commit; end loop; end; / PL/SQL procedure successfully completed. SQL> / PL/SQL procedure successfully completed.

查询到更新过的数据记录,回滚信息已经被覆盖,所以报1555错误。

SQL> print :cl ERROR: ORA-01555: snapshot too old: rollback segment number 18 with name "_SYSSMU18$" too small no rows selected

2

延迟块清除导致的1555错误

开始读取表

SQL> var cc refcursor SQL> SQL> begin open :cc for select * from t_multiver; end; /

这时一个事务更新了该数据块,但在提交前,我们手工将buffer cache中的数据做了flush,再做提交。这时的数据块上只记录了锁标志,没有事务标志和Commit SCN。

PL/SQL procedure successfully completed. SQL> update t_multiver set b=115 where a=1; 1 row updated. SQL> alter system flush buffer_cache; System altered; SQL> commit; Commit complete.

进行非常多的事务,将回滚段中的事务信息表中的数据全部覆盖:

SQL> begin -- overwrite rollback slot for i in 1..40000 loop update t_dual set dummy=1; commit; end loop; end; / PL/SQL procedure successfully completed.

读取数据块前需要到回滚段的事务信息表中读取Itl中没有标记完全的事务的状态和Commit SCN,以判断是否需要进行一致性读。但是事务信息表中的数据都已经被覆盖,所以报1555错误:

SQL> print :cc ERROR: ORA-01555: snapshot too old: rollback segment number 20 with name "_SYSSMU20$" too small no rows selected

以上两个例子看起来是好像很类似,但是,他们的本质区别是:第一个实际上是在进行一致性读得时候发生的1555错误,而第二个例子是在判断是否需要进行一致性读得时候发生的1555错误。

解决1555错误的方法

现在,我们已经知道了1555错误产生的原因。那么,就可以总结出以下方法来解决1555错误问题:

1、扩大回滚段

因为回滚段是循环使用的,如果回滚段足够大,那么那些被提交的数据信息就能保存足够长的时间是那些大事务完成一致性读取。

2、增加undo_retention时间

在undo_retention规定的时间内,任何其他事务都不能覆盖这些数据。

3、优化相关查询语句,减少一致性读

减少查询语句的一致性读,就降低读取不到回滚段数据的风险。这一点非常重要!

4、减少不必要的事务提交

提交的事务越少,产生的回滚段信息就越少。

5、对大事务指定回滚段

通过以下语句可以指定事务的回滚段:

SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment;

给大事务指定回滚段,即降低大事务回滚信息覆盖其他事务的回滚信息的几率,又降低了他自身的回滚信息被覆盖的几率。大事务的存在,往往是1555错误产生的诱因。

6、使用游标时尽量使用显式游标并且只在需要的时候打开游标,同时将所有可以在游标外做的操作从游标循环中拿出。

当游标打开时,查询就开始了,直到游标关闭。减少游标的打开时间,就减少了1555错误发生的几率。

下面例子中,第一段代码发生1555错误的几率就大于第二段的:

例子一

declare cursor cl is select b from demo.t_multiver; v_b number; begin open cl; --do some thing without relation to the cursor. fetch cl into v_b; while cl%found loop --do other things without relation to the cursor. ... ... fetch cl into v_b; end loop; close cl; commit; END;

例子二:

declare cursor cl is select * from demo.t_multiver; begin --do some thing without relation to the cursor. --do other things without relation to the cursor. open cl; fetch cl into v_b; while cl%found loop ... ... fetch cl into v_b; end loop; close cl; commit; END;

7、使用回滚表空间自动管理

回滚表空间自动管理是9i后的特性。他由Oracle自动管理回滚段的创建和回收。尽管有人认为这一特性是以后牺牲性能为代价的,或者有其他缺点而不建议使用。但我认为,这确实是Oracle一个很好的特性,特别是OLTP环境下应该使用它。并且10g中,这一特性大大增强了。

而在大型的数据仓库或者报表系统中,会有一些很大的查询作业存在,这时可以考虑使用手动管理,为某些大作业创建单独的回滚段。

以上总结了解决1555错误的各种办法,具体采用哪种方式,就需要根据错误产生的实际情况来决定了。

实例分析

实际上,你在了解了1555错误为什么会发生的前提,遇到了1555错误就不应该再手足无措了。但是,根据我个人的经验,大多数的1555错误的发生,其根本原因还是语句写得太烂,导致了大量的consistent gets和超长的执行时间,最后引发了1555错误。下面就是一个典型例子:

错误的发生

近来生产系统反馈,时常有作业被异常中止,导致应用程序被hung住。经过检查日志,是某个作业在运行时发生了1555错误,导致程序无法返回结果:

相关程序记录下的日志:

错误分析解决

这是一个典型的1555错误。检查引发该错误的PACKAGE,发现它只有一个入口函数main(及程序日志中记录的函数),但这个函数还调用了其他N个PACKAGE里面的函数。这是一个大作业,执行时,设置它使用了一个大的回滚段:RBS_BATCH1。

先看看相关配置:rollback tablespace空间为8G,undo_retention为1800。

看看回滚段的统计数据:

注意到RBS_BATCHT1的wait%是0.098%,这个值应该是比较好的一个值。

回过头再来看依法错误的语句:调用ext_pkg.main函数。在程序日志中已经记录下了输入参数,这就比较好办了:作一个trace,看看到底哪条语句的性能最差:

用tkprof处理trace文件后检查trace文件:

......

......

......

从trace文件中,发现有一条语句性能相当差,通过对这条语句做SQL Trace,发现它的consistent gets达到80万!

于是对该语句进行优化,调整了它的写法,并建立了缺少的索引(优化过程略)。最终将consistent gets数量降低到了5000。

重新安排上线,经过一周的观察,1555错误没再发生。

其实这个案例的解决是比较简单的,最终的处理就是将一条语句进行优化。

-------The End

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

原文发表时间:2016-08-26

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏DOTNET

【翻译】MongoDB指南/CRUD操作(三)

【原文地址】https://docs.mongodb.com/manual/ CRUD操作(三) 主要内容: 原子性和事务(Atomicity and Tran...

2579
来自专栏MongoDB中文社区

深入解析MongoDB Plan Cache

前段时间笔者遇到一个MongoBD Plan Cache的bug,于是深究了下MongoDB优化器相关源码。在这里分享给大家,一方面让大家知道MongoDB优化...

602
来自专栏从ORACLE起航,领略精彩的IT技术。

Oracle数据库该如何着手优化一个SQL

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

巧妙使用exchange partition的一个案例(r6笔记第1天)

前几天写过一篇文章讨论过分区表的在线重定义,其实就是另外一个分区表和现有的分区表做数据字典信息的交换 http://blog.itpub.net/2371875...

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

并行查询缓慢的问题分析(r5笔记第86天)

今天,数据迁移组的同事问我一个问题,说他们现在需要在迁移库中查看一些数据,但是查看的时候速度很慢,想让我们看看是不是数据库端出了什么问题。因为数据迁移的一些准备...

2566
来自专栏aoho求索

MySQL探秘(三):InnoDB的内存结构和特性

 常言说得好,每个成功男人背后都有一个为他默默付出的女人,而对于MySQL来说,这个“人”就是InnoDB存储引擎。  MySQL区别于其他数据库的最为重要的特...

1111
来自专栏张狗蛋的技术之路

MySQL探秘(三):InnoDB的内存结构和特性

 常言说得好,每个成功男人背后都有一个为他默默付出的女人,而对于MySQL来说,这个“人”就是InnoDB存储引擎。  MySQL区别于其他数据库的最为重要的特...

1072
来自专栏犀利豆的技术空间

Redis 的基础数据结构(三)对象

前两篇文章介绍了 Redis 的基本数据结构动态字符串,链表,字典,跳跃表,压缩链表,整数集合,但是使用过 Redis 的同学会发现,平时根本没有使用过这些数...

652
来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle应用开发实战一

Oracle介绍 ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式 数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/S...

2776
来自专栏cloudskyme

oracle10g分区的几种类型

为了简化数据库大表的管理,例如在数据仓库中一般都是TB级的数量级.ORACLE8以后推出了分区选项.分区将表分离在若于不同的表空间上,用分而治之的方法来支撑元限...

32711

扫码关注云+社区