Oracle undo 表空间使用情况分析

参考文档

Master Note: High Undo Space Usage (文档 ID 1578639.1)
IF: High Undo Tablespace Space Usage Without any Active Transactions (文档 ID 1951404.1)
IF: High Undo Tablespace Space Usage due to Active Transactions (文档 ID 1951403.1)
Undo Remains Unexpired When Using Non-autoextensible Datafiles For The Undo Tablespace (文档 ID 1112431.1)
AUM Common Analysis/Diagnostic Scripts (文档 ID 877613.1)
What is the Undo Advisor and how to Use it through the DBMS_UNDO_ADV package (文档 ID 1580225.1)
Script - Check Current Undo Configuration and Advise Recommended Setup (文档 ID 1579035.1)

查看 undo 表空间使用情况

ACTIVE ：还有活动事务在使用 undo。这部分空间属于暂时不能使用的空间。
EXPIRED ：考虑到 undo retention 之后，这些 undo 已经过期了。这部分空间是可以重用的。
UNEXPIRED ：考虑 undo retention 之后，这些 undo 还没有过期，但是已经没有活动事务在使用了。在超过 undo retention 之后，这部分空间会变成 expired 状态，然后就可以重用了。

SQL> SELECT SUM(BYTES)/1024/1024/1024 FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME='APPS_UNDOTS1';

SUM(BYTES)/1024/1024/1024
-------------------------
               11.6328125

SQL> select sum(bytes /(1024*1024)) from dba_undo_extents where status='EXPIRED';

SUM(BYTES/(1024*1024))
----------------------
                1.0625

SQL> select sum(bytes /(1024*1024)) from dba_undo_extents where status='ACTIVE';

SUM(BYTES/(1024*1024))
----------------------
              1968.125

SQL> select sum(bytes /(1024*1024)) from dba_undo_extents where status='UNEXPIRED';

SUM(BYTES/(1024*1024))
----------------------
             146186.75

查看 undo retention

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900

SQL> select max(maxquerylen),max(tuned_undoretention) from v$undostat;

MAX(MAXQUERYLEN) MAX(TUNED_UNDORETENTION)
---------------- ------------------------
           70504                   119636

从上面结果可以看出，数据库根据算法计算出来的最大TUNED_UNDORETENTION要比最大MAXQUERYLEN大很多，上面看到的undo表空间使用，是UNEXPIRED类型占用了绝大部分的空间。undo表空间中的数据经过TUNED_UNDORETENTION之后才会由UNEXPIRED变成EXPIRED。TUNED_UNDORETENTION的优化，参考metalink上的文章Undo Remains Unexpired When Using Non-autoextensible Datafiles For The Undo Tablespace (文档 ID 1112431.1)进行，设置undo表空间中数据文件的扩展参数（即打开数据文件的auextend 开关，但设置数据文件的maxsize为数据文件的当前大小）。

大致的步骤如下：

SQL> ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <current_size>;

可以使用下面sql查看系统运行时，使用undo tablespace最多的session和sql

SELECT S.USERNAME,
       S.SID,
       S.SERIAL#,
       S.LOGON_TIME,
       s.SQL_ID,
       T.XIDUSN,
       T.UBAFIL,
       T.UBABLK,
       T.USED_UBLK,
       T.START_DATE,
       T.STATUS
  FROM V$SESSION S, V$TRANSACTION T
 WHERE S.SADDR = T.SES_ADDR
 ORDER BY t.USED_UBLK DESC
 ;