undo表空间居高不下和enq: US - contention

最新推荐文章于 2024-08-22 14:19:23 发布

duanning397

最新推荐文章于 2024-08-22 14:19:23 发布

阅读量6.9k

点赞数 1

分类专栏： Oracle运维文章标签： oracle10g system oracle 数据库 sql 优化

本文链接：https://blog.csdn.net/duanning397/article/details/7668703

版权

在某次软件运行中，由于2分钟内的表操作突然减缓，导致系统重启。虽然alert日志未报错，但在ASH中发现一个涉及UNDO表空间的SQL问题。经过研究，发现与undo_retention参数有关，该参数定义了撤销段的最短保留时间。默认情况下，Oracle会自动调整undo信息保存时间。为确保undo信息的保留，可以执行ALTER TABLESPACE UNDOTBS RETENTION GUARANTEE命令。

摘要由CSDN通过智能技术生成

这几天遇到一个错误，我也不知道算不算错误吧，因为没有报错，只是在那突然的短短2分钟内表的操作突然降低了，导致了该软件重新启动。查看alert日志没有报错，而是在ASH里找到了TOP SQL框有一个这样的错误，使我百思不得其解。查看该SQL语句只是简单的一个更新，并不需要优化。最后再百度、google的帮助下终于找到了错误原因，原来与UNDO的设置有关。首先来介绍下undo_retention参数，该参数是撤销段的最短保留时间，而在默认情况下Oracle将根据表空间的大小和历史使用情况，自动调整undo信息保存时间，同时忽略 undo_retention的值,除非undo_retention的guarantee 特性被启用.也就是执行以下命令：

ALTER TABLESPACE UNDOTBS RETENTION GUARANTEE;

在自动调整启用的情况下，实际的撤销信息最短保留时间可以通过查询V$UNDOSTAT视图上的TUNED_UNDORETENTION列获得。往往最短保存时间远远大于设定的UNDO_RETENTION。UNDO自动优化功能能够最大限度的使用undo表空间,满足大部分的sql执行,但是也带来一个问题：很多事务执行完毕之后，发现UNDO表空间会在很长时间都一直保持着使用率是接近100%的状态，active 状态的很少。这种接近状态还无法手工的收缩，甚至于重启数据库实例也无法缓解，而此时常常会收到undo表空间的监控报警。

再来说说enq: US - contention问题
这是oracle10g中开始出现的bug(在11.1.0.7中仍有这个BUG)，当因为系统activity增加或者降低的时候，oracle SMON进程会自动ONLINE或者OFFLINE rollback segments。这样导致某些与undo segments相关的latch或者enqueue被hold住