计算undo表空间大小

计算undo表空间大小

Sizing an UNDO Tablespace

Determining a size for the UNDO tablespace requires three pieces of information

• (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)

上面是oracle给出计算undo表空空间大小的公式

1.       UP undo_retention可以查询v$paramter视图单位是秒

select value as UP from v$parameter where name='undo_retention'

SQL> select value as UP from v$parameter where name='undo_retention';

UP

--------------------------------------------------------------------------------

900

SQL>

2．UPS每秒产生的undo块通过v$undostat计算出单位块

select sum(undoblks)/sum((end_time-begin_time)*86400) as UPS from v$undostat;

SQL> select sum(undoblks)/sum((end_time-begin_time)*86400) as UPS from v$undostat;

 UPS

----------

.350380097

SQL>

3．DB_BLOCK_SIZE通过v$paramter可以得到

select value/1024 as DBS from v$parameter where name='db_block_size';

SQL> select value/1024 as DBS from v$parameter where name='db_block_size';

  DBS

----------

   8

SQL>

4．最后通过公式计算

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

用下面select直接可以计算出undo大小单位为K

select (UR * (UPS * DBS) + (DBS * 24)) as "size" from (select sum(undoblks)/sum((end_time-begin_time)*86400) as UPS from v$undostat),(select value as UR from v$parameter where name='undo_retention'),(select value/1024 as DBS from v$parameter where name='db_block_size');

SQL> select (UR * (UPS * DBS) + (DBS * 24)) as "size" from (select sum(undoblks)

/sum((end_time-begin_time)*86400) as UPS from v$undostat),(select value as UR fr

om v$parameter where name='undo_retention'),(select value/1024 as DBS from v$par

ameter where name='db_block_size');

 size

----------

2691.54825

SQL>

5.上面计算的undo大小最好是在业务高峰期计算的值