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

文章转载于https://blog.csdn.net/duanning397/article/details/7668703

这几天遇到一个错误，我也不知道算不算错误吧，因为没有报错，只是在那突然的短短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住太长时间，导致系统很多活跃session都开始等待enq: US - contention。可以同时使用以下解决方法:

1. 设置event让SMON不自动OFFLINE回滚段。

alter system set events '10511 trace name context forever, level 1';

2. 设置参数_rollback_segment_count ：表示有多少rollback segment要处于online的状态；可以将该数值设置为数据库最繁忙的时候的回滚段数目。

alter system set "_rollback_segment_count"=;
这里以‘_’开头的为隐藏参数，通过show parameter 是看不到的，可以通过以下语句：

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '%_rollback_segment_count%';

3.  undo autotune bug多多。最好disable。

alter system set "_undo_autotune"= false;
这种方法就是关闭了UNDO的自动调整功能，同事也能解决掉UNDO表空间会在很长时间都一直保持着使用率是接近100%的问题。

4.  有一个patch: A fix to bug 7291739 is to set a new hidden parameter, _highthreshold_undoretention to set a high threshold for undo retention completely distinct from maxquerylen.

alter system set "_highthreshold_undoretention"=;