ORA-01555: snapshot too old al usar BULK COLLECT en una tabla fija.

[gnzsoloyo]

Tengo un extraño caso que me ocupa, donde se produce un error ORA-01555 (segmentos de rollback), en un contexto que no encuentro documentado en ninguna parte.
Explico:
Tengo que realizar un procesamiento de una tabla de logs (originados en a cola de mensajes de widows), por lo que los registros no sufren ningún tipo de actualización. Como mucho son leídos y muy puntualmente, en ocasiones.
La depuración puede abarcar entre 10 a 25 millones de registros cada vez que se procesa, para lo cual el SP realiza el proceso por medo de BULK COLLECT de hasta los 25 millones de registros, y luego elimina todos estos registros usando un ciclo de FORALL.
Para calibrarlo habíamos realizado pruebas de cuántos registros podían leerse un un sólo BULK COLLECT, encontrando que nuestro sistema soportaba sin problemas lecturas masivas de hasta 50 millones de registros por cada BULK COLLECT sin pestañear.
En las pruebas iniciales en un entorno de pruebas, todo funcionó correctamente,pero al ponerlo en la base final, se produce el error ORA-01555.
Resolvimos la contingencia momentáneamente haciendo que los ciclos BUL COLLECT lean sólo 2 millones de registros (hicimos pruebas remotas para probarlo en la base final para buscar un valor adecuado), pero por razones operativas tenemos que buscar aumentarlo.
Toda la documentación oficial, y la de diversos tutoriales nos indica que el proceso está bien programado, y que ése es el modo de realizar estos procesamientos masivos de tablas, pero me surge la duda:
¿Por qué un BULK COLLECT, que debería cargar simplemente un array en memoria, puede estar haciendo uso de los segmentos de rollback, especialmente considerando que es una tabla en la que sólo se hacen inserciones y no actualizaciones?
¿Qué relación hay entre BULK COLLECT y los segmentos de rollback?

[huesos52]

Hola gnzsoloyo

Realmente el bulk collect no genera segmentos de rollback. Pero toda operación DML si lo hace. (No solo delete y update). Este error puede estar presentandose debido a la cantidad masiva de inserts que realizas y que posiblemente no realizas commit hasta que termine la totalidad del proceso.

Por lo que veo, tu proceso es bastante grande y por lo que dices, a nivel de infraestructura no tienen problemas de memoria ni de hardware como tal.

Los segmentos de rollback tienen un comportamiento circular y su liberación está basada en un algoritmo llamado LRU (Less Recent Used).

Existe un parámetro en oracle llamado undo_retention en el que se configura en segundos (900 por defecto - 15 minutos) la persistencia de segmentos de rollback en los bloques de memoria.

Es posible que por la magnitud de tu proceso, este tiempo se sobrepase y este segmento se pierda.

Yo ensayaría 2 opciones:
1. Controlado por un ciclo, hacer un commit cada N inserciones.
2. Aumentar este parámetro a un valor de tiempo mas grande.

Saludos

[matanga]

¿Por qué un BULK COLLECT, que debería cargar simplemente un array en memoria, puede estar haciendo uso de los segmentos de rollback, especialmente considerando que es una tabla en la que sólo se hacen inserciones y no actualizaciones?
¿Qué relación hay entre BULK COLLECT y los segmentos de rollback?

El error ORA-01555 aparece cuando la consistencia del SELECT no se puede resolver porque los datos en los segmentos de rollback o de undo ya no están disponibles, y esto es independiente de que el resultado del SELECT lo almacenes en una variable, un array o una tabla, o de que el fetch sea de 1 registro o de 1 millón.

En cuanto al tipo de operaciones DML, los segmentos de undo guardan un registro con el dato anterior y el dato posterior, pero no tienen en cuenta si la modificación proviene de un delete, update o insert, lo que obliga siempre a leer los segmentos de undo para determinar si el dato anterior cumple con las condiciones del SELECT.

La solución varía en cada caso, mucho depende de los recursos que tengas, por ejemplo:

1. Si es posible, optimizar el proceso de depuración, el ORA-01555 no solo se produce por el volumen de registros, también se produce por la duración del SELECT, evalúa la posibilidad de pasar los registros a una tabla temporal con la sentencia insert into TempTable ... select * from LogTable en vez de procesar los registros dentro de un array.

2. Si tienes espacio en disco, agranda el tablespace de undo tanto como sea necesario, el undo_retention no ayuda mucho si el tablespace no puede almacenar como mínimo el volumen de registros del SELECT.

3. Si no tienes espacio en disco, divide el volumen de registros en múltiples SELECT que se ejecutan de forma serial, es decir, select ... where id <= 25, select ... where id between 25 and 50, etc (no es lo mismo que bulk collect limit 25), pero debes tener en cuenta que por las modificaciones sobre la tabla, el resultado de un SELECT puede ser diferente al resultado de múltiples SELECT.

4. Si no tienes espacio en disco y por la consistencia no puedes ejecutar múltiples SELECT, evalúa la posibilidad de lanzar el proceso de depuración en horarios con poca carga de transacciones.

Saludos

[gnzsoloyo]

Bien, como para dar un cierre a este tema que me quedo colgado, les comento:
- El problema efectivamente se produce en mi caso porque esta tabla jamás está inactiva. Está constantemente recibiendo inserts, por lo cual Oracle, para preservar la consistencia de lectura de lo que busco, descarga todo esto en los segmentos de rollback. En un momento dado el snapshot resulta obsoleto, y se cae el proceso, a pesar de no haberse terminado la lectura.
- El hecho de que vayamos a borrar directamente los registros leídos sin hacer otra operación, resulta irrelevante para Oracle. Este tiene como prioridad mantener la lectura consistente, y eso causa un impacto indeseado en el proceso.
- Para resolver el problema, realizamos diferentes parametrizaciones, acortando la cantidad de registros, o reduciendo la cantidad de commits a ejecutar, ,hasta lograr que el proceso más o menos diese resultado.
- La performance lograda resultó insuficiente, y aún así, en ciertas ocasiones el proceso caía, siempre por snapshot too old.
- Finalmente, viendo que en realidad, si íbamos a borrar directamente, leer los registros carecía de sentido, reformulamos el proceso creando una segmentación por tiempo de los registros, aprovechando que sobre el campo de fecha de la tabla había un INDEX definido.
- El proceso ha funcionado de corrido desde entonces, con una performance mucho mejor a la esperada... aunque ahora nos hemos topado con un problema distinto que será motivo de otro thread.

Como resumen de condiciones de entorno:
- No, no se puede modificar los segmentos de rollback (que sería la mejor solución), porque los de administración de la base se niegan, en esencia porque esa base está en un servidor con Oracle 8i, y ya no tiene soporte de Oracle.
- No, migrar el servidor no es una opción, porque la empresa no lo va a hacer, y de todos modos si lo hace, es un plan a plazo de 3 años como mínimo, y nuestro problema es ahora.
- No se espera reducción en la carga de datos. Antes bien se espera un incremento paulatino para los próximos meses. Con lo que el problema debe ser resuelto a nivel de proceso almacenado.

En otras palabras: El escenario es un espanto.