记一次ORACLE的UNDO表空间爆满分析过程

<b></b>

这篇文章是记录一次ORACLE数据库UNDO表空间爆满的分析过程，主要整理、梳理了同事分析的思路。具体过程如下所示：

早上收到一数据库服务器的UNDO表空间的告警邮件，最早一封是7：55发出的（监控作业是15分钟一次），从告警邮件分析，好像是UNDO表空间突然一下子被耗尽了。

<b>DB</b>

<b>Tablespace</b>

<b>Allocated</b>

<b>Free</b>

<b>Used</b>

<b>% Free</b>

<b>% Used</b>

192.168.xxx.xxx:1521

UNDOTBS1

16384

190.25

16193.75

1.16

99

使用一些SQL分析了undo表空间使用情况，以及undo segment状态等等，非常想定位到是哪个或那些SQL耗尽了UNDO表空间，但是没有一个SQL能实现我的想法，抑或是我不了解。

既然直接入手，无法定位，那就曲线分析，首先检查、分析了一下redo log，发现在7点这段时间，日志切换了83次之多，横向、纵向对比，明显异常，如下截图所示：

<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160722000208372-50725577.png"></a>

生成了实例在7:00~8:00时间段的AWR报告，从下面指标我们可以看出，数据库实例在这段时间呢，其实是非常空闲的，因为DB Time为9.74（mins)

<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160722000209544-121827304.png"></a>

另外，从Time Model Statistics部分来看，主要时间花在background elapsed time，而不是DB Time,我们可以判断时间主要耗费在后台进程，而不是前台进程。另外sql execute elapsed time耗用了DB Time的70.36的时间。

<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160722000210654-2036253351.png"></a>

然后我们来看SQL order by Gets部分信息, 第一个SQL是删除WRH$_SQL_PLAN的记录，当然也有删除wrh$_sqltext、WRH$_SEG_STAT_OBJ表记录的SQL，如下所示

<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160722000211732-62264306.png"></a>

查看SQL ordered by Reads部分信息，发现主要也是删除系统表WRH$_SQL_PLAN记录 （这个表是非常大的）

<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160722000212497-1947174808.png"></a>

然后我们查看AWR报告的Tablespace IO Stats部分，IO主要集中在SYSAUX,UNDOTBS1这两个表空间，然后你会发现那个表WRH$_SQL_PLAN就是在SYSAUX下

<a href="http://images2015.cnblogs.com/blog/73542/201607/73542-20160722000213529-644495970.png"></a>

所以，上面种种证据显示，让我们几乎可以断定主要是下面这个SQL导致了UNDO表空间使用的暴增。当然分析过程中，还有一些旁听佐证。在此感觉没有必要一一列举了。