Undo Retention , Undo Optimization

undo_retention parameter will not guarantee a RETENTION unless you define a RETENTION GUARANTEE CLAUSE on tablespace level

Actual Undo Size

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#;

UNDO_SIZE
———-
209715200

Undo Blocks per Second

SELECT   MAX (undoblks / ( (end_time - begin_time) * 3600 * 24))
 "UNDO_BLOCK_PER_SEC"
 FROM   v$undostat;

UNDO_BLOCK_PER_SEC
——————
3.12166667

DB Block Size

SELECT   TO_NUMBER (VALUE) "DB_BLOCK_SIZE [KByte]"
 FROM   v$parameter
 WHERE   name = 'db_block_size';

DB_BLOCK_SIZE [Byte]
——————–
4096

Optimal Undo Retention

209’715’200 / (3.12166667 * 4’096) = 16’401 [Sec]

Using Inline Views, you can do all in one query!

SELECT   d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION [Sec]",
 ROUND (
 (d.undo_size / (TO_NUMBER (f.VALUE) * g.undo_block_per_sec))
 )
 "OPTIMAL UNDO RETENTION [Sec]"
 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';

ACTUAL UNDO SIZE [MByte]
————————
200

UNDO RETENTION [Sec]
——————–
10800

OPTIMAL UNDO RETENTION [Sec]
—————————-
16401

Calculate Needed UNDO Size for given Database Activity

SELECT   d.undo_size / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR (e.VALUE, 1, 25) "UNDO RETENTION [Sec]",
 (TO_NUMBER (e.VALUE) * TO_NUMBER (f.VALUE) * g.undo_block_per_sec)
 / (1024 * 1024)
 "NEEDED UNDO SIZE [MByte]"
 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';

ACTUAL UNDO SIZE [MByte]
————————
200
UNDO RETENTION [Sec]
——————–
10800
NEEDED UNDO SIZE [MByte]
————————
131.695313