Sizing an UNDO tablespace

The formula shown here calculates the optimal size for an undo tablespace using the following variables:

  UR-the undo retention period expressed in seconds

  UPS-the number of undo blocks generated per second

  DBS-the size of data blocks

 

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

 

UR can be found from undo_retention parameter

DBS can be found from db_block_size parameter

UPS can be found from v$undostat view, which capture the statistics data of undo tablespace over 10 minutes period. The begin_time and end_time are always in 10 minutes interval, except the first record, which is the current state.

 

The following SQL can be used to find UPS

select sum(undoblks)/sum((24*3600*(end_time - begin_time))) UPS from v$undostat

 

The formula can be done with one SQL:

 

select

((UR*(UPS*DBS))+(DBS*24))/(1024*1024) SIZE_AS_MEGABYTES

from

(select

(select

(select value as ur from v$parameter where name = 'undo_retention'), value as dbs from v$parameter where name = 'db_block_size'), sum(undoblks)/sum((24*3600*(end_time - begin_time))) ups from v$undostat)

 

To get good result, that must be done when you database has heaviest workload. And also it should be adjusted frequently.