Oracle-undo表空间使用率高案例

问题背景：

        用户报障，生产一套11.2.0.4的RAC集群数据库undo表空间undotbs2使用率超90%告警，并且从表空间的监控信息看，近一周表空间的使用率一直处于上升趋势。需要分析undo表空间使用率高的原因。

问题分析：

        对于undo表空间使用率高的问题，我们首先需要确认undo extent的使用状态，通过undo extent状态的使用情况来判断undo表空间使用率高的原因。通常，如果undo extent的主要使用状态为active，则说明当前数据库可能正在执行大事务，导致申请了大量的undo空间还没有释放，需要分析大事务的执行情况。如果undo extent的主要使用状态为unexpired，则说明数据库现在和之前可能有大量的事务被执行，导致undo被大量申请和释放，但undo extent目前的状态还在保留时间内，所以为unexpired，此外，undo保留时间过长也会导致大量的undo extent处于unexpired状态。如果undo extent的主要状态为expired，则说明unexpired的undo extent已经超过了保留时间，undo表空间正常会在短时间内回收这些空间，可能由于expired所在的undo段没有被使用或是Bug导致空间回收时间变长或是没有进行回收。

        检查当前undo表空间的使用情况，可以看到大量的undo extent状态为unexpired非过期状态。

        检查当前数据库的undo参数，可以看到undo保留时间参数undo_rentention设置为10800（3小时），但当前这个参数并不是强制保留时间，因为数据库默认的undo保留策略是自动调整undo保留时长算法，算法会根据数据库最长的查询语句执行时间、表空间的使用率和是否自动扩展、undo_rentention参数大小动态计算一个undo的保留时间。此外，可以通过隐含参数_undo_autotune设置为false禁用自动保留算法，启用非自动调整undo保留时长算法，此时undo的保留时长等于参数undo_rentention。

        当前数据库是自动调整undo保留时长算法，我们可以通过查询v$undostat或是dba_hist_undostat查询目前的undo保留时间大小。

        通过dba_hist_undostat查询最近的一次的自动保留算法计算值tuned_undoretention，节点一为56小时，节点二为607小时,可以看到两个节点的undo保留时间都非常的长，而最大的语句查询时间maxquerylen两个节点分别为1313秒，671秒，跟保留时间的差距很大，很明显，当前这个保留时间不是根据最长查询语句执行时间计算得出的，很有可能是根据表空间的使用率和是否自动扩展计算得出的。

select BEGIN_TIME,END_TIME,MAXQUERYLEN,TUNED_UNDORETENTION from DBA_HIST_UNDOSTAT order by 1,2;

        查看undo表空间的数据文件，可以看到当前undo表空间undotbs1，undotbs2的数据文件都是为非自动拓展，根据我们的测试，当undo表空间的数据文件为非自动拓展时，自动保留算法计算的保留时间tuned_undoretention跟当前的undo表空间的使用率存在一定的关联性，当前undo表空间的使用越空闲，自动保留时间tuned_undoretention越大。如果undo表空间的数据文件为自动拓展，保留时间tuned_undoretention等于最大的语句查询时间maxquerylen+X秒（MOS文档420525.1说是300秒，但实际测试不一定）和undo_retention两者的最大值。

        检查数据库的undo表空间使用繁忙程度，可以看到最近记录的undo block的使用数量为3730，undo表空间为60G，这个使用量对于当前的undo表空间配置来说是较为空闲的，因此，这里我们可以确认由于undo表空间使用空闲，导致undo自动保留算法计算的保留时间tuned_undoretention生成了一个较大值。

问题解决：

        通过对undo extent和undo表空间数据文件的分析，当前undo表空间使用率高的原因为有大量的undo extent的状态为非过期unexpired导致。undo extent的过期时间由undo保留时间所控制。当前undo保留时间为数据库的undo保留时长算法根据undo空间的使用空闲度计算生成，由于当前数据库undo表空间使用空闲，导致undo自动保留算法计算的保留时间tuned_undoretention生成了一个较大值，undo表空间的extent状态长时间为unexpired，空间无法进行回收，进而导致undotbs使用率持续上涨。

        解决方式一：

        将undo表空间的设置为自动拓展方式，并将maxsize大小设置为跟user_size一样的大小。此方式，保留时间tuned_undoretention等于最大的语句查询时间maxquerylen+X秒（MOS文档420525.1说是300秒，但实际测试不一定）和undo_retention两者的最大值。需要注意的是，如果数据库出现长时间的慢查询，可能也会导致tuned_undoretention产生一个较大值。

alter database datafile '' autoextend on next 10M maxsize 30G;

        解决方式二：

        可以通过隐含参数_undo_autotune设置为false禁用自动调整保留算法，启用非自动调整undo保留时长算法，使保留时间固定为undo_retention。参数可以在线生效，不需要重启数据库。

alter system set "_undo_autotune"= false;

本次案例，我通过解决方式二进行处理，禁用自动保留算法，启用非自动调整undo保留时长算法，将保留时间固定为undo_retention。调整之后，可以立马看undo extent的状态变为expired，随后，空间被回收，undo表空间的使用率也降了下来。

自动保留算法测试：

    当前undo的参数配置，数据库undo保留时间为自动调整保留算法模式，undo_retention为300。

        undo表空间的数据文件为自动拓展。

        跑一个超过300秒的慢查询。再查询自动保留时间值tuned_undoretention，可以看到tuned_undoretention大于当前的undo_rentention参数值，并且随最大查询时间maxquerylen变大而变大，但未发现maxquerylen与tuned_undoretention存在等值关系。

        将undo_rentention设置为10800，远大于maxquerylen值。

        再次查询tuned_undoretention值，可以看到值等于undo_rentention参数值。

        将undo表空间的数据文件设置为非自动拓展。

        再次查询tuned_undoretention值，可以看到值从10800变成了一个很大的值2068259。

        数据库执行一次大事务操作，模拟undo表空间使用繁忙。可以看到tuned_undoretention值从2068259被调小为399。

测试总结：

        数据库在自动调整保留算法模式下：

        1 undo表空间的数据文件为非自动拓展时，自动调整保留算法计算的保留时间tuned_undoretention跟当前的undo表空间的使用率存在一定的关联性，当前undo表空间的使用越空闲，自动保留时间tuned_undoretention越大。

        2 undo表空间的数据文件为自动拓展，保留时间tuned_undoretention等于最大的语句查询时间maxquerylen+X秒（MOS文档420525.1说是300秒，但实际测试不一定）和undo_retention两者的最大值。

Tip:欢迎关注公众号：勇敢牛牛的笔记，超100+的原创内容，每周不定期更新数据库技术文章。