Optimización de los Parámetros de UNDO

A Partir de Oracle 9i, los segmentos de rollback pasan a llamarse Undo Logs. Tradicionalmente, la información de transacciones (UNDO) se almacenaba en Rollback segments hasta el COMMIT o ROLLBACK. Oracle9i permite la gestión automática de UNDO. El DBA para especificar cuánto tiempo debe mantenerse la información de UNDO, para prevenir los errores "snapshot too old" en consultas largas. Esto se hace mediante el parámetro UNDO_RETENTION. 
El valor por defecto es de 900 segundos (15 minutos), Y puedes cambiar este parámetro para garantizar que Oracle mantiene los registros de UNDO largos períodos de tiempo. En lugar de tener que definir y gestionar los segmentos de rollback, puedes simplemente definir un tablespace UNDO y dejar que se ORACLE se encargue de lo demás. En cuanto a la gestión automática de Undo es fácil. Todo lo que necesitas es crear un Tbs UNDO y poner el UNDO_MANAGEMENT = AUTO. Sin embargo vale la pena para ajustar los siguientes parámetros: 

• El tamaño del Tbs UNDO. 
• El Parámetro UNDO_RETENTION. 
• Calcular el UNDO_RETENTION para el Undo Tablespace. 

Puedes optar por asignar un determinado tamaño del tablespace UNDO y, a continuación, establecer el UNDO_RETENTION a un valor óptimo de acuerdo con el tamaño de UNDO y la actividad de la base de datos. Si su espacio en el disco es limitado y no quiere asignar más espacio del necesario para el Tbs Undo, esta es la forma de proceder. 
 La siguiente consulta le ayudará a optimizar el 
UNDO_RETENTION (Optimo) = UNDO SIZE Actual / DB_BLOCK_SIZE * UNDO_BLOCK_PER_SEQ 

Dado que estas consultas usan las estadísticas V$UNDOSTAT, ejecute las consultas sólo después de que la Base de datos lleve funcionando con UNDO para un importante y representativo periodo de tiempo.

Actual Undo Size

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#; 

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
      "UNDO_BLOCK_PER_SEC"
  FROM v$undostat; 

DB Block Size

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

Optimal Undo Retention

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'
/ 

Calcular el UNDO necesario para la actividad de la BBDD

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024) 
      "NEEDED UNDO SIZE [MByte]"
  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'
/