Mantenimiento y purgado de STATSPACK en Oracle (PERFSTAT)

Mantenimiento y purgado de STATSPACK en Oracle (PERFSTAT)

   septiembre 30, 2026       Luis Felipe

---

En más de una ocasión, los que administramos bases de datos donde no disponemos de licenciamiento para el Diagnostics Pack nos apoyamos en Statspack como nuestra principal herramienta de diagnóstico histórico. Sin embargo, si no mantenemos una política de limpieza estricta, el esquema PERFSTAT puede crecer de forma desmesurada en nuestros discos.

Continuando con las buenas prácticas para mantener saludable nuestro esquema PERFSTAT, hoy veremos un enfoque de control absoluto: cómo purgar por rangos específicos de snap_id, cómo recuperar físicamente el espacio en disco mediante el comando SHRINK SPACE y algunas recomendaciones de rendimiento asociadas.

Antes de ejecutar cualquier acción, el primer paso es consultar los snapshots disponibles en el repositorio para definir con exactitud el rango que deseamos eliminar.

Nota: Ejecuta la siguiente consulta conectado con el usuario administrador o PERFSTAT:

SELECT snap_id, snap_time
FROM perfstat.stats$snapshot
ORDER BY snap_id ASC;

Además, puedes revisar cuánto espacio están consumiendo actualmente las tablas más pesadas antes de iniciar el mantenimiento.

SELECT segment_name,ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM dba_segments
WHERE owner = 'PERFSTAT' 
AND segment_name IN ('STATS$SQL_SUMMARY', 'STATS$SQLTEXT', 'STATS$FILESTATXS')
GROUP BY segment_name
ORDER BY size_gb DESC;

Una vez definido el intervalo, utilizamos el procedimiento statspack.purge especificando los parámetros de rango i_begin_snap e i_end_snap:

BEGIN 

perfstat.statspack.purge( i_begin_snap => 90160 , i_end_snap => 106699); 

END; 

/ 

COMMIT;

 

Al realizar un purge, las filas son eliminadas de las tablas, pero los segmentos lógicos (High Water Mark) y el espacio en los ficheros de datos no se liberan automáticamente de forma predeterminada. Las tablas siguen reteniendo el tamaño anterior en disco.

Para compactar las tablas más pesadas del esquema PERFSTAT y devolver el espacio libre al tablespace, aplicamos la habilitación de movimiento de filas (ROW MOVEMENT) y el comando SHRINK SPACE:

-- 1. Mantenimiento sobre STATS$SQL_SUMMARY 
ALTER TABLE perfstat.STATS$SQL_SUMMARY ENABLE ROW MOVEMENT; 
ALTER TABLE perfstat.STATS$SQL_SUMMARY SHRINK SPACE; 
ALTER TABLE perfstat.STATS$SQL_SUMMARY DISABLE ROW MOVEMENT; 

-- 2. Mantenimiento sobre STATS$SQLTEXT 
ALTER TABLE perfstat.STATS$SQLTEXT ENABLE ROW MOVEMENT; 
ALTER TABLE perfstat.STATS$SQLTEXT SHRINK SPACE; 
ALTER TABLE perfstat.STATS$SQLTEXT DISABLE ROW MOVEMENT; 

  -- 3. Mantenimiento sobre STATS$FILESTATXS 
ALTER TABLE perfstat.STATS$FILESTATXS ENABLE ROW MOVEMENT; 
ALTER TABLE perfstat.STATS$FILESTATXS SHRINK SPACE; 
ALTER TABLE perfstat.STATS$FILESTATXS DISABLE ROW MOVEMENT;

Recomendación: Este tipo de operaciones de SHRINK generan bloqueos momentáneos y consumo de recursos, por lo que se recomienda ejecutarlas en ventanas de mantenimiento o periodos de baja actividad (fuera de horario pico).

Volvemos a revisar cuánto espacio están consumiendo actualmente las tablas más pesadas después de ejecutado el mantenimiento. Observamos un decrecimiento en el consumo por parte de 2 de ellas.

Tras un proceso masivo de borrado y compactación física de tablas, los índices asociados pueden quedar fragmentados. Para asegurar que el optimizador de Oracle continúe accediendo de manera óptima a los datos, puedes reconstruir los índices principales en línea:

ALTER INDEX perfstat.stats$sql_summary_pk REBUILD ONLINE; 

ALTER INDEX perfstat.stats$sqltext_pk REBUILD ONLINE;

El mantenimiento preventivo de Statspack es una tarea ineludible para cualquier DBA que gestione entornos sin Diagnostics Pack. Purgar los datos históricos innecesarios y recuperar el espacio físico liberado es un proceso sencillo pero crítico. Implementando esta rutina mantendrás tus bases de datos optimizadas, evitarás alertas innecesarias por falta de espacio y asegurarás diagnósticos precisos por mucho más tiempo.

Saludos.

 

Read More

Cómo solucionar el ORA-01555: Diagnóstico y cálculo real del Undo Retention

Cómo solucionar el ORA-01555: Diagnóstico y cálculo real del Undo Retention

   agosto 26, 2026       Luis Felipe

---

¡Hola a todos! En esta ocasión vamos a revisar un error clásico con el que nos topamos los administradores de bases de datos Oracle en el día a día: el célebre ORA-01555: snapshot too old.

Cuando este mensaje aparece en los logs, la reacción impulsiva suele ser subir el parámetro UNDO_RETENTION o inflar el tablespace sin un análisis previo. Veamos cómo diagnosticarlo correctamente utilizando las vistas del sistema, cómo calcular el valor ideal y qué alternativas reales existen para solucionarlo.

Cuando ocurre el error, Oracle indica qué sentencia SQL falló y cuánto tiempo llevaba ejecutándose. Para entender el comportamiento general de las consultas de lectura consistente y la retención real, podemos consultar la vista V$UNDOSTAT con el siguiente script:

SELECT BEGIN_TIME, END_TIME, UNDOTSN AS Tablespace_ID,
UNDOBLKS AS Bloques_Generados, TXNCOUNT AS Transacciones,
MAXQUERYLEN AS Query_Mas_Largo_Seg, TUNED_UNDORETENTION AS Retencion_Autotuneada_Seg
FROM V$UNDOSTAT 
WHERE MAXQUERYLEN IS NOT NULL ORDER BY MAXQUERYLEN DESC;

Como se observa, el tiempo del query más largo (QUERY_MAS_LARGO_SEG) ronda consistentemente cerca de las 4 horas (14,184 segundos) debido a procesos analíticos o reportes pesados recurrentes. Oracle intenta autoajustar la retención (RETENCION_AUTOTUNEADA_SEG), pero si el espacio físico no es suficiente, el undo expira antes de tiempo y surge el fallo.

Para evitar adivinar números al azar, podemos apoyarnos en la tasa real de generación de bloques de nuestro entorno. Utiliza la siguiente consulta para calcular los Gigabytes exactos requeridos en el Tablespace de Undo para soportar una ventana de tiempo determinada.

SELECT 
    t.gb_undo_actual,
    n.gb_undo_necesarios,
    CASE 
        WHEN t.gb_undo_actual >= n.gb_undo_necesarios THEN 'SUFICIENTE'
        ELSE 'FALTA ESPACIO'
    END AS estado_undo
FROM 
    (SELECT ROUND(SUM(bytes) / (1024 * 1024 * 1024), 2) AS gb_undo_actual 
     FROM dba_data_files 
     WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')) t,
     --Reemplaza el valor de 14184 por tu valor más alto obtenido de la consulta anterior en la columna "Query_Mas_Largo_Seg"
    (SELECT ROUND( ( 14184 * 
                     (SELECT MAX(UNDOBLKS / ((END_TIME - BEGIN_TIME) * 86400)) FROM V$UNDOSTAT) * 
                     (SELECT TO_NUMBER(VALUE) FROM V$PARAMETER WHERE NAME = 'db_block_size') 
                   ) / (1024 * 1024 * 1024), 2 ) AS gb_undo_necesarios 
     FROM dual) n;

Como podemos ver en este análisis, aunque el proceso es demandante, nuestro tablespace cuenta con 543.99 GB actuales frente a los 450.89 GB requeridos, arrojando un estatus de SUFICIENTE en cuanto a espacio físico.

Si un reporte tarda casi 4 horas en ejecutarse, subir el parámetro UNDO_RETENTION globalmente a ese nivel extremo puede inflar innecesariamente el tablespace para toda la transaccionalidad normal del sistema.

Para evitar que el autoajuste de Oracle se base en picos anómalos, podemos calcular un valor de retención inteligente basado exclusivamente en la operación estándar de la base de datos:

SELECT 
    ROUND(AVG(TUNED_UNDORETENTION)) AS UNDO_RETENTION_SUGERIDO_SEG
FROM V$UNDOSTAT
WHERE MAXQUERYLEN < 3600; -- Excluimos queries mayores a 1 hora (reportes pesados)

 Esto te dará un valor equilibrado para proteger el día a día sin sobredimensionar la retención por culpa de una sola consulta analítica.

Tienes dos caminos posibles para decidir cómo proceder:

Opción A: Ampliar el Undo

Si el reporte es un proceso heredado o una consulta analítica que por naturaleza debe tardar horas, debes aceptar el costo de almacenamiento:

1. Modifica el parámetro UNDO_RETENTION en segundos (cubriendo holgadamente el tiempo del proceso, por ejemplo, 15,100 segundos):
   

   ALTER SYSTEM SET undo_retention = 15100 SCOPE=BOTH; 

2. Si requieres asegurar estrictamente que Oracle no sobrescriba el undo antes de cumplir ese tiempo (garantizando el espacio físico previamente dimensionado):
   

   ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

   Nota: Ten en cuenta que con RETENTION GUARANTEE, si el tablespace se llena por completo, las nuevas transacciones que requieran espacio fallarán. ¡Asegúrate de haber dimensionado bien el espacio físico antes de activarlo!

3. Para revertir en caso de emergencia por espacio:
   

   ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE; 

Opción B: Tunear la consulta

Un proceso analítico o reporte que tarde casi 4 horas suele ser un candidato ideal para optimización. En lugar de gastar gigabytes de disco guardando transacciones viejas, conviene:

• Revisar planes de ejecución y crear o ajustar índices faltantes.

• Migrar consultas pesadas a procesamiento en paralelo (PARALLEL).

• Particionar las tablas involucradas para que el motor lea solo lo necesario.

• Reducir el tiempo de ejecución elimina automáticamente la necesidad de un tablespace de undo gigantesco.

En resumen, modificar UNDO_RETENTION o configurar un RETENTION GUARANTEE funciona excelente como un parche rápido para salir del paso, pero la verdadera habilidad de un DBA se demuestra yendo directo a la raíz: optimizar los procesos.

Al final, lograr que tus consultas sean eficientes no solo te ahorra dolores de cabeza con el odioso ORA-01555, sino que también optimiza el rendimiento general del motor y evita que desperdicies espacio valioso en los discos.

Saludos.

 

Read More