Optimize UNDO Parameters

When working with UNDO (instead of Rollback) two things are important to consider.
1. The size of the UNDO tablespace
2. The Undo_retnetion parameter
A particular size for the UNDO tablespace can be selected and set the UNDO_RETENTION parameter to an best possible value according to the undo size and the relevant database activity. If you have limited diskspace, you don’t want to allocate more space than necessary to the Undo tablespace.

The following query will help 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';



If disk space it not an issue, then it would be ideal to choose the UNDO_RETENTION time that is best. (Flashback etc). Assign the suitable 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$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';