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.