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.