Oracle undo 表空間使用情況分析

花菜土豆粉發表於2015-01-08
Oracle

參考文件

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
 ;

相關文章