Calculate optimized undo retention in Oracle

Here we have discussed 2 options using which you can calculate the optimal undo retention for your Oracle Database: Following document will also help you resolving ORA-1555 snap shot too old or

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

Cause: Rollback records needed by a reader for consistent read are overwritten by other writers.

Action: If your database is in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments.

You can get an ORA-01555 error with a too-small undo_retention, even with a large undo tablespace.

Though there are many more reasons for getting ORA-01555 setting optimal undo_retention could 

help in many cases.

Option:1

First check actual undo size of your oracle database:

Actual Undo Size:
------------------------
SQL> 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#;

Secondly check db_block_size:

DB_BLOCK_SIZE:
---------------------
SQL> SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
 WHERE name = 'db_block_size';

Third check undo block per second

Undo Blocks per Second:
--------------------------------
SQL> SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
  "UNDO_BLOCK_PER_SEC"
   FROM v$undostat;



Calculation Optimal Undo Retention:

Finally use following formula to get optimal undo retention for your undo tablespace:

Optimal Undo Retention =
                                  Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)
Optimal Undo Retention = 2.2549E+10 / (8192 * 349.848333)
= 22549000000 / (8192 * 349.848333)
=22549000000 / 2865957.543936
=7867.876496534571

========================================================================

Optimal undo retention calculation:

Option:2


Using following sql query you can find out the optimal undo retention for your oracle database in one go:

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]       UNDO RETENTION [Sec]             OPTIMAL UNDO RETENTION [Sec]
-----------------------------------------------------------------------------------------------
  21504      1800                7868