Determining the Optimal Oracle Undo Retention Period

To determine the optimal value for the UNDO_RETENTION parameter, you must first calculate the actual amount of undo that the database is generating. Once you know approximately how much undo the database is generating, you can calculate a more precise value for the UNDO_RETENTIONparameter.


Use the following formula to calculate the optimal value of the UNDO_RETENTION parameter:



Calculate the current space allocated for undo


SQL> select sum(d.bytes) "undo"

2 from v$datafile d,

3 v$tablespace t,

4 dba_tablespaces s

5 where s.contents = 'UNDO'

6 and s.status = 'ONLINE'

7 and = s.tablespace_name

8 and d.ts# = t.ts#;





Calculate the value of UNDO_BLOCKS_PER_SEC


select max(undoblks/((end_time-begin_time)*3600*24))


3 FROM v$undostat;




Check the block size for your database:

SQL> show parameter db_block_size


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

db_block_size integer 8192


Calculate the optimal value for the UNDO_RETENTION parameter using the formula, for example, giving a result in seconds: 23,158 = 68232937472/(359.366667 * 8,192).

In this case, assigning a value of 1,800 seconds for the undo_retentionparameter is appropriate.


If disk space isn’t an issue for you, you can instead choose the ideal value for the UNDO_RETENTION parameter for
your instance, The formula now becomes:


size of undo = undo_retention * db_block_size :undo_block_per_sec


SQL> select d.undo_size/(1024*1024) "Current UNDO SIZE",

2 SUBSTR(e.value,1,25) "UNDO RETENTION",

3 (to_number(e.value) * to_number(f.value) *

4 g.undo_block_per_sec) / (1024*1024)

5 "Necessary UNDO SIZE"

6 from (

7 select sum(a.bytes) undo_size

8 from v$datafile a,

9 v$tablespace b,

10 dba_tablespaces c

11 where c.contents = 'UNDO'

12 and c.status = 'ONLINE'

13 and = c.tablespace_name

14 and a.ts# = b.ts#

15 ) d,

16 v$parameter e,

17 v$parameter f,

18 (

19 Select max(undoblks/((end_time-begin_time)*3600*24))

20 undo_block_per_sec

21 from v$undostat

22 ) g

23 where = 'undo_retention'

24 and = 'db_block_size';

Current UNDO SIZE     UNDO RETENTION            Necessary UNDO SIZE

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

65072                           86400                                                     242572.5



The query shows that you should set the size of the undo tablespace in this database to around 242572.5 MB.
Set the undo retention to 86400  minutes:


alter system set undo_retention=86400  scope=both;



Refrence :





About Mughees Ahmed

Over 2-year experience of Administration in Oracle, SQL Server, and MySQL databases using various tools and technologies. Keen on learning new database technologies having very good analytical skills. Working knowledge of Red Hat Linux, UNIX, Solaris and Windows Server 2012 is a valuable addition to my knowledge desk. KNOWLEDGE & SKILLS • Oracle Database Server 10g/11g/12c. • Oracle RAC, Data guard. • Oracle Golden Gate (Oracle to Oracle, Oracle to MySQL and MySQL to Oracle) • Oracle Enterprise Manager Grid Control, Toad, SQL developer. • SQL Server 2005/2008/2012/2016. • SQL Server Failover clustering, mirroring & log shipping, Always On availability groups. • MySQL 5 Administration, MySQL Workbench, MySQL Enterprise Monitor, SQLyog • MySQL NDB Cluster Installation,Administration. • MySQL Asynchronous/Semi-synchronous replication. • Oracle OEM Contact me on [email protected]

Leave a Reply

Your email address will not be published. Required fields are marked *