Oracle undo表空间大小估算

Oracle undo表空间大小估算

(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

上面的公式中，第1、3个参数都可以轻松的从参数文件中得到：
SQL> show parameter undo_retention

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_retention        integer  5400
SQL> show parameter db_block_size

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_block_size        integer  8192

现在重要的是要确定第2个参数：每秒钟生成undo block的总数，可以从 V$UNDOSTAT 中得到。

每秒生成的UNDO量：
SQL> SELECT (SUM(undoblks))/ SUM((end_time - begin_time) * 86400) FROM v$undostat;

(SUM(UNDOBLKS))/SUM((END_TIME-BEGIN_TIME)*86400)
--------------------------------------------------------------
          117.97590055

正常估算值：
SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select upper(value) from v$parameter where name = 'undo_tablespace'));

最大值估算：
SELECT (UR * UPS * DBS + DBS * 24) / 1024 / 1024 AS "undo size(M)"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
     (SELECT max(undoblks / ((end_time - begin_time) * 24 * 3600)) AS UPS FROM v$undostat),
     (SELECT value AS DBS FROM v$parameter WHERE name = 'db_block_size');

建议在业务量繁重的那一天进行统计，而将UNDO表空间大小设为最大估算值以上。

说明：本文整理于How To Size UNDO Tablespace For Automatic Undo Management [ID 262066.1]