1

参考文档

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
 ;

花菜土豆粉
67 声望27 粉丝

Life with Oracle