Calculate Value for UNDO_RETENTION

Calculate UNDO_RETENTION  for given UNDO Tabespace

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:

As these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant  time.

Actual Undo Size

SQL> SELECT SUM(a.bytes) "UNDO_SIZE"

  2    FROM v$datafile a,

  3         v$tablespace b,

  4         dba_tablespaces c

  5   WHERE c.contents = 'UNDO'

  6     AND c.status = 'ONLINE'

  7     AND b.name = c.tablespace_name

  8     AND a.ts# = b.ts#;

 UNDO_SIZE

----------

1.4098E+10

SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

  2        "UNDO_BLOCK_PER_SEC"

  3    FROM v$undostat;

UNDO_BLOCK_PER_SEC

------------------

              4.01

SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"

  2   FROM v$parameter

  3  WHERE name = 'db_block_size';

DB_BLOCK_SIZE [KByte]

---------------------

                 8192

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'
/