Optimal Undo Tablespace Size and Undo Retention

The undo tablespace and its size is related to the retention period and the transactional activity of the database.  These must be regularly reviewed and check to ensure that 1, you are not trying to have a retention period that the undo tablespace can not handle, 2 that you are size properly so that you do not encounter the dreaded ORA-01555 snapshot too old.

Optimal Undo Size and Retention
I have seen many environments where the undo tablespace is far to small for the undo retention setting and they target undo retention the environment requires.  This is due to the fact that the undo tablespace was size and undo retention set, but never rechecked or updated as the environment grew or activity changed.  Therefore if the expectation is that a flashback query can go back 2 hours, but the tablespace is too small the reality is that they really can not go back 2 hours.

First we need to check the Optimal Undo Size based on our desired retention as in most environments this will change regularly based on increases or decreases in the trasnactional activity.

The script below will ask for the desired undo retention, then will examine your database and tell you the optimal undo retention setting for the current environment as the undo tablespace size and transactional activity indicates, but will also show you what the size of the undo tablespace needs to be to be able to support the desired undo retention you have entered.

---------------------------------------------------------------------------------
-- Script: see_optimal_undo_retention.sql
-- Author: Michael Messina, Management Consultant TUSC a Rolta Company
--
-- Description:  Will calculate based on undo utilization and undo size
--               the optimal value for undo_retention.
---------------------------------------------------------------------------------
set serveroutput on size 1000000
set feedback off

ACCEPT desired_undo_retention PROMPT "Enter Desired Undo Retention in Seconds ->"

DECLARE
   v_desired_undo_retention          NUMBER := &desired_undo_retention ;
   v_block_size                      NUMBER ;
   v_undo_size                       NUMBER ;
   v_undo_blocks_per_sec             NUMBER ;
   v_optimal_undo_retention          NUMBER ;
   v_current_undo_retention          NUMBER ;
   v_undo_size_desired_ret           NUMBER ;
  
BEGIN
   -- get the current undo retention setting
   select TO_NUMBER(value)
   INTO v_current_undo_retention
   FROM v$parameter
   WHERE name = 'undo_retention' ;
  
   -- Calculate Actual Undo Size
   SELECT SUM(a.bytes)
   INTO v_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#;

   -- Calcuate the Undo Blocks per Second
   SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
   INTO v_undo_blocks_per_sec
   FROM v$undostat ;

   -- Get the database block size
   SELECT TO_NUMBER(value)
   INTO v_block_size
   FROM v$parameter
   WHERE name = 'db_block_size';

   v_optimal_undo_retention := v_undo_size/(v_block_size * v_undo_blocks_per_sec) ;
   v_undo_size_desired_ret := ((v_block_size * v_undo_blocks_per_sec) * (v_desired_undo_retention)) / 1024 / 1024 ;
  
   DBMS_OUTPUT.PUT_LINE ('              Current undo Size --> ' || TO_CHAR(v_undo_size/1024/1024) || 'MB') ;
   DBMS_OUTPUT.PUT_LINE (' Current undo_retention Setting --> ' || TO_CHAR(v_current_undo_retention) || ' Seconds') ;
   DBMS_OUTPUT.PUT_LINE (' Optimal undo_retention Setting --> ' || TO_CHAR(ROUND(v_optimal_undo_retention,2)) || ' Seconds') ;
   DBMS_OUTPUT.PUT_LINE (' Desired undo_retention Setting --> ' || TO_CHAR(ROUND(v_desired_undo_retention,2)) || ' Seconds') ;
   DBMS_OUTPUT.PUT_LINE ('Undo Size for desired retention --> ' || TO_CHAR(ROUND(v_undo_size_desired_ret,2)) || 'MB') ;
END ;
/

Example Execution of script:
SQL> @see_optimal_undo_retention.sql
Enter Desired Undo Retention in Seconds ->7200
old   2:    v_desired_undo_retention          NUMBER := &desired_undo_retention ;
new   2:    v_desired_undo_retention          NUMBER := 7200 ;
Current undo Size --> 1980MB
Current undo_retention Setting --> 900 Seconds
Optimal undo_retention Setting --> 100504.96 Seconds
Desired undo_retention Setting --> 7200 Seconds
Undo Size for desired retention --> 141.84MB

In this example on a very small database with no activity I want a retention of 2 hours, The output shows the current undo tablespace size, the current undo retention setting, the optimal undo retention setting indicates what I could make the undo retention based on the current activity and size of the undo tablespace, the desired undo retention that I entered and the undo tablespace size required to meet my desired undo retention setting.