CALCULATE AND ESTIMATE OPTIMAL UNDO FOR DATABASE ORACLE

To estimate the optimal undo retention for the sql queries, below query can be used

Optimal undo retention:

Formula:

kIsH@Xhydra<>--OPTIMAL_UNDO = SUM(UNDO_DATAFILE_SIZE) / (DB_BLOCK_SIZE * UNDO_BLOCK_PER_SECOND)
set lines 200 pages 1000
col "OPTIMAL_UNDO_RETENTION" for 9999999999999
col "DEFAULT_UNDO_RETENTION" for a20
col "UNDO_S" for 9999999999999
WITH undo_size as (
                  select sum(bytes) as "UNDO_S" from dba_data_files where file_name like '%undo%'),
	 db_block_size as (
                       select value as "BLOCK_SIZE" from v$parameter where name like '%db_block_size%'),
     undo_block_per_second as (
                               select max(undoblks / ((end_time - begin_time) * 24 * 3600)) as "UNDOBLKPERSEC" from v$undostat) 
select (select value from v$parameter where name like '%undo_retention%') as "DEFAULT_UNDO_RETENTION",
        round((UNDO_S/1073741824),2) as TOTAL_UNDO_SIZE_GB,
        round((UNDO_S / (BLOCK_SIZE * UNDOBLKPERSEC))) as "OPTIMAL_UNDO_RETENTION"
        from undo_size,
		     db_block_size,
			 undo_block_per_second;

Output:

DEFAULT_UNDO_RETENTI TOTAL_UNDO_SIZE_GB OPTIMAL_UNDO_RETENTION
-------------------- ------------------ ----------------------
900                                5.68                1602391

Optimal undo tablespace:

Formula:

kIsH@Xhydra<>--UNDO_TABLESPACE_SIZE = DB_BLOCK_SIZE * UNDO_BLOCK_PER_SECOND * UNDO_RETENTION
set lines 200 pages 1000
col "OPTIMAL_UNDO_RETENTION" for 9999999999999
col "DEFAULT_UNDO_RETENTION" for a20
col "UNDO_S" for 9999999999999
WITH undo_size as (
                  select sum(bytes) as "UNDO_S" from dba_data_files where file_name like '%undo%'),
	 db_block_size as (
                       select value as "BLOCK_SIZE" from v$parameter where name like '%db_block_size%'),
     undo_block_per_second as (
                               select max(undoblks / ((end_time - begin_time) * 24 * 3600)) as "UNDOBLKPERSEC" from v$undostat) 
select (select value from v$parameter where name like '%undo_retention%') as "DEFAULT_UNDO_RETENTION",
        round((UNDO_S / (BLOCK_SIZE * UNDOBLKPERSEC))) as "OPTIMAL_UNDO_RETENTION",
        round((UNDO_S/1073741824),2) as TOTAL_UNDO_SIZE_GB,
		round((round((UNDO_S / (BLOCK_SIZE * UNDOBLKPERSEC))) * BLOCK_SIZE * UNDOBLKPERSEC)/1073741824,2) as "OPTIMAL_UNDO_TABLESPACE_GB"
        from undo_size,
		     db_block_size,
			 undo_block_per_second;

Output:

DEFAULT_UNDO_RETENTI OPTIMAL_UNDO_RETENTION TOTAL_UNDO_SIZE_GB OPTIMAL_UNDO_TABLESPACE_GB
-------------------- ---------------------- ------------------ --------------------------
900                                   32591               5.68                       5.68

1 Comment

Leave a Reply