oracle如何查询undo表空间大小,Oracleundo表空间大小估算

(UR) UNDO_RETENTION in seconds (UPS) Number of undo data blocks generated per second (DBS) Overhead varies based on exte

(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]

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：php中文网