Optimize Your UNDO Parameters
This tip comes from Ricardo Franco Argona, DBA, IBM in Ste-Foy, São Paulo, Brazil.
When you are working with UNDO (instead of ROLLBACK) there are two important things to consider:1.) the size of the UNDO tablespace, and
2.) the UNDO_RETENTION parameter.
There are two ways to proceed :
1) You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter :
select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)", substr(e.value,1,25) "UNDO RETENTION (Secs)",
round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION (Secs)"
from (select sum(a.bytes) undo_size from v$datafile a, v$tablespace b, dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size' ;
2) If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.).
Allocate the appropriate size to the UNDO tablespace according to the database activity:
select d.undo_size/(1024*1024) "ACTUAL UNDO SIZE (MEGS)", substr(e.value,1,25) "UNDO RETENTION (Secs)",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE (MEGS)"
from (select sum(a.bytes) undo_size from v$dataf! ile a, v! $tablespace b, dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size' ;
The previous query may return a "NEEDED UNDO SIZE" that is less than the "ACTUAL UNDO SIZE".
If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.
WARNING: Because these queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time.
