计算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大小最好是在业务高峰期计算的值