Rem Filename   : undo_retention.sql
Rem 
Rem Parameters : None
Rem
Rem Author     : 
Rem RDBMS      : Oracle V9.x.x
Rem
Rem Modification history
Rem --------------------
Rem 21-May-2004  Initial creation.
Rem
Rem ==========================================================================================================
Rem
Rem Description : Verifies the undo tablespace size vs. de undo retention period taking into account
Rem               the current transaction load (undo blocks per second).
Rem 
Rem Sample output
Rem -------------
Rem Verify the undo tablespace size versus the undo retention period.
Rem =======================================================================================================
Rem 
Rem Actual undo size        :      300     Mbytes
Rem Actual undo retention   :       30     Minutes
Rem Actual transaction load :        0.187 Undo blocks per second
Rem 
Rem Number of "ORA-01555 (Snapshot too old)" errors encountered since the last startup of the instance : 0
Rem 
Rem 
Rem - If you want to adjust the undo tablespace size according to the
Rem   actual undo retention period and taking into account the
Rem   current transaction load,
Rem 
Rem   then consider 3 Mbytes as the minimum value.
Rem 
Rem 
Rem - If you want to adjust the retention period according to the
Rem   actual undo tablespace size and taking into account the
Rem   current transaction load,
Rem 
Rem   then consider 3429 minutes as the maximum value.
Rem 
Rem ==========================================================================================================

SET SERVEROUTPUT ON SIZE 100000
SET FEEDBACK     OFF
SET HEADING      OFF

PROMPT
PROMPT Verify the undo tablespace size versus the undo retention period.
PROMPT =======================================================================================================
PROMPT

DECLARE
  db_block_size       int;
  undo_retention      int;
  undo_blocks_per_sec number;
  undo_size           number;
  ssoldcnt            int;

BEGIN
  SELECT to_number(value) INTO undo_retention FROM v$parameter WHERE name = 'undo_retention';
  SELECT to_number(value) INTO db_block_size  FROM v$parameter WHERE name = 'db_block_size';

  SELECT sum(v$datafile.bytes) INTO undo_size
    FROM v$datafile, v$tablespace, dba_tablespaces
   WHERE dba_tablespaces.contents = 'UNDO' 
     AND dba_tablespaces.status = 'ONLINE'
     AND v$tablespace.name = dba_tablespaces.tablespace_name
     AND v$datafile.ts# = v$tablespace.ts#;

  SELECT max(undoblks/((end_time-begin_time)*3600*24)) INTO undo_blocks_per_sec FROM v$undostat;
  SELECT sum(ssolderrcnt) INTO ssoldcnt FROM v$undostat;

  dbms_output.put_line( 'Actual undo size        : ' || TO_CHAR(undo_size/(1024*1024), '999G999') || '     Mbytes' );
  dbms_output.put_line( 'Actual undo retention   : ' || TO_CHAR(undo_retention/60,     '999G999') || '     Minutes' );
  dbms_output.put_line( 'Actual transaction load : ' || TO_CHAR(undo_blocks_per_sec,   '999G990D999') || ' Undo blocks per second' || chr(10) );

  dbms_output.put_line( 'Number of "ORA-01555 (Snapshot too old)" errors encountered since the last startup of the instance : ' || ssoldcnt );
  dbms_output.put_line( chr(10) );

  if undo_blocks_per_sec = 0 then
    dbms_output.put_line( 'No transactions happened on this database since last startup.' );
  else
    dbms_output.put_line( '- If you want to adjust the undo tablespace size according to the' || chr(10) ||
                          '  actual undo retention period and taking into account the' || chr(10) ||
                          '  current transaction load,' || chr(10) || 
                          chr(10) || 
                          '  then consider ' || ltrim(to_char((undo_retention * db_block_size * undo_blocks_per_sec) / (1024*1024),'999G999')) || ' Mbytes as the minimum value.' );
    dbms_output.put_line( chr(10) );
    dbms_output.put_line( '- If you want to adjust the retention period according to the' || chr(10) ||
                          '  actual undo tablespace size and taking into account the' || chr(10) ||
                          '  current transaction load,' || chr(10) || 
                          chr(10) ||
                          '  then consider ' || round((undo_size / (db_block_size * undo_blocks_per_sec * 60 ))) || ' minutes as the maximum value.' );
    dbms_output.put_line( chr(10) );

  end if;

END;
/

SET HEADING  ON
SET FEEDBACK OFF