估算Undo表空间大小

UNDO表空间大小评估参考:

要确定Oracle需要的UNDO 表空间的大小，需要以下三条信息： 

A、UR :以秒为单位的UNDO_RETENTION

SQL> show parameter undo_retention;

B、UPS:计算业务高峰期每秒产生undo数据块的个数

SQL> select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;

C、DBS:得到数据块大小

SQL> show parameter db_block_size;

 

参考计算公式：

UndoSpace = UR * UPS * DBS + DBS * 24

参考计算UNDO表空间大小语句：

  最大估算值：

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');

  正常估算值

    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 (sum(undoblks )/ (sum(end_time - begin_time) * 24 * 3600)) AS UPS FROM v$undostat),

     (SELECT value AS DBS FROM v$parameter WHERE name = 'db_block_size');

建议将UNDO表空间大小设为最大估算值以上。

出自Serapy的博客，转载务必保留： http://blog.sina.com.cn/s/blog_8c6d7ff60100vdrp.html