記一次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表空間使用的暴增。當然分析過程中，還有一些旁聽佐證。在此感覺沒有必要一一列舉了。