Anuncios en tutorial de programación PLSQL

lunes, 18 de marzo de 2024

Transacciones parciales en PLSQL para lidiar con tablas muy grandes o voluminosas

Hace unas semanas alguien mencionó en un comentario que en su base de datos Oracle tenía una tabla con millones de registros y que, utilizando un bucle PLSQL, pensaba ejecutar un UPDATE y un COMMIT por cada, digamos, 500 registros procesados en dicha tabla, evitando así posibles problemas con los segmentos de rollback. El caso es que dicho lector me preguntaba si yo tenía alguna sugerencia al respecto. Este tipo de problema es, ciertamente, algo más complejo de lo que a simple vista parece, y sobre el que conviene escribir con cierta calma.

ORA-01555 Snapshot Too Old

Si dividimos una transacción muy larga en muchas otras más pequeñas, existen bastantes probabilidades de que a mitad de la transacción global se produzca un error ORA-01555 (snapshop too old), problema generalmente causado por la alta frecuencia en la ejecución de sentencias COMMIT, o que simplemente se produzca un fallo del sistema. Entonces nos encontraremos con una transacción realizada parcialmente. Por lo tanto, antes de implementar este tipo de solución, deberemos asegurarnos de la que la transacción global puede re-ejecutarse. La cuestión es que en muchos casos esto no será posible y, por tanto, tendremos que escribir bastante código PL/SQL para hacer posible esta re-ejecución.

Supongamos que tenemos el siguiente bloque de código PLSQL:

DECLARE
CURSOR cur IS SELECT * FROM tmillon;
BEGIN
OPEN cur;
LOOP
FETCH cur BULK COLLECT INTO cur_rec LIMIT 500;
...
FORALL i IN 1 .. cur_rec.COUNT
UPDATE tmillon SET ... ;
-- cur_rec se usa en el UPDATE
...
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;

En el caso concreto de este bloque PL/SQL os puedo asegurar de que la probabilidad de que se produzca un error ORA-01555 es bastante elevada, la razón principal es que estamos leyendo y modificando la misma tabla y que el SELECT del cursor debe entregar, en todo instante, los valores del momento en que se inició la consulta. El caso es que la base de datos necesitará espacio para el SELECT y para poder hacer un ROLLBACK de los UPDATE ejecutados, siendo muy probable que se produzca el mencionado error.

Entonces, puesto que se puede producir este error, deberemos pensar en alguna forma de poder re-ejecutar el bloque PL/SQL, necesitaremos bien una columna en la tabla que nos indique que dicho registro ha sido actualizado, u otra tabla en la que se inserten las claves primarias de los registros que han sido modificados. Si empleamos esta última solución, nuestro cursor quedaría de la siguiente manera:

CURSOR cur IS SELECT * FROM tmillon t
WHERE NOT EXISTS (
SELECT 'Existe'
FROM claves_ok c
WHERE c.prclave = t.prclave);

Estás dos soluciones no son únicas, pero lo que yo quiero hacer patente es que, en cualquier caso, siempre tendremos que escribir algo de código adicional.

Para lidiar con este tipo de situaciones yo recomiendo:

  • Actualizar la tabla utilizando una sola sentencia PLSQL. Esta es, sin duda, la manera más eficiente en términos de utilización de los recursos de la base de datos. Además, no nos debemos preocupar demasiado por un posible problema con los segmentos de rollback, ya que estos se necesitan para que la transacción se complete correctamente.
  • Utilizar el paquete PL/SQL DBMS_REDEFINITION. Con este paquete crearemos una copia de la tabla base de forma online, es decir, que no será necesario bloquear las modificaciones de la tabla, y, después, utilizaremos dicha tabla en el cursor principal. Estaremos utilizando una estructura de datos más compacta e incluso, si nos interesa, en la versión 10g de la base de datos Oracle, podemos añadir un comando ORDER BY a la redefinición de la tabla para reordenar los registros de la misma en el disco. De este paquete es muy probable que hable en una entrada posterior.
  • Utilizar el comando CREATE TABLE AS SELECT. Esto sería similar a utilizar el paquete PL/SQL DBMS_REDEFINITION pero, en este caso, no debemos permitir que mientras se realice esta operación se esté modificando la tabla fuente original. Además, el paquete DBMS_REDEFINITION se encarga de la creación automática de todo los objetos necesarios para crear una copia de la tabla (índices, permisos, etcétera), mientras que si utilizamos CREATE TABLE AS SELECT, de ser necesario, tendremos que crear todos estos objetos manualmente.

12 comentarios:

XOXOne dijo...

En mi opinión transacciones parciales no solo tienen los problemas mencionados en el articulo, sino que a nivel de negocio pueden dar cabida a información errada, supongase una conversión monetaria: la mitad esta en una moneda y la mitad en otra, pues otro usuario puede leer la información que el servidor da por fija (committed)

Unknown dijo...

Necesito pasar un porcentaje de información de una tabla con millones de registros usando el DBMS_REDEFINITION , es posible usar un WHERE dentro de este procedimiento para excluír algunos registros? Como se haría?

JLPM dijo...

Hola Astrid,

Es posible que esté artículo te ayude a comprender la utilidad del paquete estándar Oracle DBMS_REDEFINITION:

http://www.dba-oracle.com/t_dbms_redefinition.htm

Unknown dijo...

Hola Pepelu, muchas gracias por tu pronta respuesta, sin embargo, he buscado las utilidades del paquete pero en todas las explicaciones los ejemplos se hacen directamente con todos los registros de la tabla origen, sé que este paquete permite modificar datos de todos los registros o adicionar columnas sobre la tabla y algunas otras cosas más, pero lo que necesito saber es si este paquete permite hacer eso mismo sobre un porcentaje de los registros de la tabla... Digamos que necesito sacar el 90% de la información de esa tabla pero es demasiado grande para poder hacerlo en corto tiempo. Y ese paquete hace lo que necesito que haga pero excluyendo la información más vieja...
Gracias... :)

JLPM dijo...

Astrid, que yo sepa la funcionalidad del paquete DBMS_REDEFINITION no permite hacer lo que pides, siempre opera sobre la totalidad de la tabla.

Unknown dijo...

OK, muchas gracias :'(

Unknown dijo...

Hola Pepelu...

No hemos encontrado la solución a lo que necesitamos, por lo tanto, estabamos pensando en usar el redefinition para pasar de la tabla origen a la tabla temporal y sobre la tabla temporal crear un trigger BEFORE INSERT donde se valide si el registro insertado esta entre 1,2 y 3 si es así no lo inserte y sino que lo inserte... Pero al parecer el redefinition en oracle 9 deshabilita el trigger mientras se ejecuta el DBMS_REDEFINITION.START_REDEF_TABLE

Sabes si se alguna manera se le puede indicar al redefinition que no deshabilite ese trigger?

Gracias...

JLPM dijo...

Astrid, dejar el trigger funcionando durante la redefinición de la tabla crearía problemas de inconsistencia. Ese es el motivo por el que el paquete DBMS_REDEFINITION deshabilita los triggers.

Unknown dijo...

Pepe,

Yo me he enfrentado a este problema, y lo que hago es crear una funcion que me devuelve con pipelined los registros que debo de modificar, lo que hago es crear un procedimiento ese procedimiento recibe como parametro la cantidad de registros por bloque a procesar, en base a ello obtengo el numero de segmentos que puedo obtener e itero este valor, en cada iteracion mando llamar una funcion, la cual previamente cree que recibe el numero de segmentos y el numero de registros por bloque y me regresa pipelined estos datos, y asi con el retorno de la funcion hago el proceso que requiero y antes de regresar a la iteracion de segmentos ejecuto mi commit.

No se que pienses de esta salida, me gustaria saber tus comentarios.

Saludos

JLPM dijo...

Gerardo, tu solución me suena a algo más elaborado pero parecido a realizar un COMMIT cada X número de registros procesados. Ya he comentado que esto puede causar problemas.

Gregory Rivero dijo...

BEGIN
OPEN cur;
LOOP
FETCH cur BULK COLLECT INTO cur_rec LIMIT 500;
...
FORALL i IN 1 .. cur_rec.COUNT
UPDATE tmillon SET ... ;
-- cur_rec se usa en el UPDATE
...
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;


El commit se esta ejecutando por cada iteracion del bucle ?

JLPM dijo...

Si, es correcto, el COMMIT se ejecuta en cada iteración y esa precisamente podría se la causa del error ORA-01555.