Anuncios en tutorial de programación PLSQL

lunes, 14 de octubre de 2019

Cláusula BULK COLLECT para mejorar el rendimiento al realizar procesamiento masivo

Utilidad de la cláusula BULK COLLECT en PL/SQL
Yo siempre he dicho que cuando para hacer algo se pueden utilizar sentencias SQL sencillas, no resulta conveniente emplear complicados procedimientos PL/SQL que implementen la misma solución. Sin embargo, hay situaciones en que para mejorar el rendimiento de determinados bucles FOR en los que se realizan actualizaciones masivas sobre una determinada tabla de la base de datos Oracle, resulta conveniente utilizar técnicas PLSQL de procesamiento masivo (lo que en inglés se denomina BULK COLLECT).

Para entender mejor en qué consiste esta técnica, primero hay que comprender los motivos por los que un simple bucle FOR puede generar importantes problemas de rendimiento. Veamos el siguiente código PL/SQL:

FOR selrec IN 
  (SELECT * FROM tabla_enorme 
  ORDER BY muchas columnas)
LOOP
  -- Gran cantidad de código que omito y al final:
  UPDATE tabla_enorme SET ...
    WHERE clave_primaria = selrec.clave_primaria;
  COMMIT;
END LOOP;

El presente código es un extracto de un código PLSQL que uno de los lectores de este blog me envió por correo electrónico indicándome que presentaba graves problemas de rendimiento, cosa que desde un primer momento a mi no me extrañó, considerando que la tabla tabla_enorme contenía más de 30 millones de registros. Es el típico ejemplo de código PL/SQL que para mejorar su rendimiento necesita que se aplique la técnica o funcionalidad de BULK COLLECT (que traducido directamente a castellano sería similar a decir "recogida a granel" pero que aquí traduciremos por procesamiento masivo).

Antes de profundizar en el tema, debemos considerar que si leemos los datos de una base de datos Oracle sin necesidad de enviar datos de vuelta a la misma base de datos, no es necesario aplicar la técnica del BULK COLLECT. Es decir, el siguiente código PL/SQL es perfectamente utilizable y no debería generar problemas de rendimiento:

FOR x IN 
  (SELECT * FROM tabla_enorme t WHERE ...)
LOOP
  DBMS_OUTPUT.PUT_LINE (x.col1||...||x.colN);
END LOOP;

En el ejemplo vemos que estamos leyendo los datos utilizando un SELECT, pero que, aun tratándose de una operación registro a registro, no se utilizan para ser retornados a la base de datos utilizando un INSERT, UPDATE o DELETE. La sentencia SQL SELECT ya utiliza, de por sí, la funcionalidad de procesamiento masivo, ya que, desde la versión 10g de la base de datos Oracle, el código "FOR x IN (SELECT ...)", aunque no sea visible para el desarrollador, ha sido optimizado para almacenar internamente en matrices de 100 registros los resultados que devuelve dicho SELECT.

Sin embargo, veamos que ocurre con este otro ejemplo de código PL/SQL:

FOR x IN 
  (SELECT clave_primaria, col1, col2 
  FROM tabla_enorme)
LOOP
  x.col1 := calculos(x.col1, x.col2);
  UPDATE tabla_enorme
    SET col1 = x.col1
    WHERE clave_primaria = x.clave_primaria;
  COMMIT;
END LOOP;

En este código PLSQL, la sentencia SELECT estará utilizando la funcionalidad de procesamiento masivo incorporada con la versión 10g, pero no ocurrirá lo mismo con el procesamiento de la sentencia UPDATE. La pregunta ahora es, ¿cómo podemos mejorar el rendimiento de un código tan sencillo? El primer cambio que realizaremos es algo bastante sencillo:

FOR x IN
  (SELECT rowid, col1, col2 
  FROM tabla_enorme)
LOOP
  x.col1 := calculos(x.col1, x.col2);
  UPDATE tabla_enorme
    SET col1 = x.col1
    WHERE rowid = x.rowid;
END LOOP;

El nuevo código lee cada registro de la tabla_enorme, realiza algún tipo de procesado de los datos y, finalmente, realiza el UPDATE de un campo de la misma tabla por ROWID. ¿Qué hemos conseguido con respecto al primer código?, habremos evitado realizar 30 millones de veces un UNIQUE SCAN sobre el índice de la clave primaria, ya que estaremos accediendo a la tabla por ROWID. Acceder a la tabla a través del índice de la clave primaria puede implicar de tres a cinco operaciones de entrada/salida (I/O) por iteración, por lo que acceder a la tabla por ROWID en una tabla tan grande nos puede ahorrar más de cien millones de operaciones de entrada/salida.

Otro cambio que observaréis es que hemos eliminado el COMMIT después de cada iteración. Aparte de que realizar un COMMIT después de que cada registro es procesado resultará lento y reducirá bastante el rendimiento, si en mitad del procesamiento se produce algún error, habremos dejado la base de datos Oracle en un estado bastante inconsistente, por no decir corrupto (con media tabla_enorme actualizada y la otra sin actualizar).

Pero esto que hemos hecho no tiene en realidad nada que ver con la técnica o funcionalidad de procesamiento masivo. Veamos el siguiente código PL/SQL en el que hemos, por fin, utilizado un FETCH con la cláusula BULK COLLECT:

CREATE OR REPLACE PROCEDURE procesamiento_masivo
AS
  TYPE matriz_rowid IS TABLE OF ROWID;
  TYPE matriz_col1 IS TABLE OF tabla_enorme.col1%TYPE;
  TYPE matriz_col2 IS TABLE OF tabla_enorme.col2%TYPE;

  CURSOR cur IS SELECT rowid, col1, col2
    FROM tabla_enorme;
  m_rowid matriz_rowid;
  m_col1 matriz_col1;
  m_col2 matriz_col2;
  contador NUMBER := 100;

BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT
      INTO m_rowid, m_col1, m_col2 LIMIT contador;
    FOR i IN 1 .. m_rowid.count
    LOOP
      m_col1(i) := calculos(m_col1(i), m_col2(i));
    END LOOP;
    FORALL i IN 1 .. m_rowid.count
      UPDATE tabla_enorme
        SET col1 = m_col1(i)
        WHERE rowid = m_rowid(i);
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  CLOSE cur;
END;

El nuevo código utiliza sin ningún tipo de restricción la funcionalidad de procesamiento masivo BULK COLLECT, de manera que los registros se procesan de cien en cien (valor que podremos cambiar con sólo asignar un valor diferente a la variable contador). Y, una vez procesados, lo que hacemos es un UPDATE masivo (bulk update) utilizando la sentencia PL/SQL FORALL.

Es fácil observar que el código PLSQL que utiliza la funcionalidad BULK COLLECT es bastante más complicado y mucho menos intuitivo que el original, pero los resultados a nivel de rendimiento van a ser realmente sorprendentes. Para tablas con 30 millones de registros, utilizar la funcionalidad de procesamiento masivo puede hacer que nuestro código se ejecute entre diez y veinte veces más rápido que sin utilizar dicha funcionalidad (ojo que, a veces, la mejora de rendimiento puede ser incluso hasta superior).

22 comentarios:

Manuel dijo...

Un cambio que puede mejorar el rendimiento aún mas, es que que en el FORALL, sustituir el m_rowid.count, por m_rowid.last.

En este caso, que las tablas cargan 100 elementos no se notara mucho, pero cuando se carga un número mucho mas grande si se nota el cambio, ya que no tarda lo mismo Oracle en decirte cuantos elementos hay que cual es el último elemento.

Luca dijo...

Una consulta soy muy nuevo en oracle. y siempre pongo un COMMIT, despues de un Cursor o un Bloque de codigo donde se presente un DML(Insert,Update,Delete). la consulta es donde en Tu codigo haces el Commit o este ya esta implicito con el Bulk Collect.

Pepelu dijo...

Luca, ciertamente es recomendable utilizar la sentencia COMMIT para reflejar explícitamente el fin de una transacción.

Al no utilizar el COMMIT lo único que quiero indicar es que no resulta conveniente realizarlo dentro del bucle y que se puede esperar a que termine la ejecución de todos los UPDATE. Es decir, que se puede ejecutar el procedimiento PL/SQL procesamiento_masivo y después de ejecutado ejecutar un COMMIT, ya sea explícito o implícito al cerrar la sesión.

En el código del procedimiento PL/SQL procesamiento_masivo es posible colocar el COMMIT detrás de la sentencia "CLOSE cur;" sin que ello afecte al rendimiento.

Carlos dijo...

Hola Pepelu, tu como siempre tocando los temas en los cuales necesito ayuda...

Pepelu, yo tengo una pregunta, al momento en el que el bulk collect se presente algun error o se genere un exception, como hago para controlar ese evento? ya que en el ejemplo no se puede apreciar como se deberia de controlar ese posible problema.

Pepelu dijo...

Hola Carlos, las excepciones PL/SQL en el bulk collect se manejan igual que en cualquier otro bloque.

Puedes echar un vistazo a esta entrada en la que hablo sobre las excepciones PLSQL predefinidas.

La Nenis, dijo...

Tio, he buscado por toda la internet una forma fácil de entender el Bulk collect, y la tuya ha sido la maaasssssss..
Muchas gracias ;)

Guatemala dijo...

Muchas gracias por el aporte, utilice tu ejemplo y el rendimiento es sorprendente, gracias.

Galo Galarza dijo...

Muchas gracias.........

María Patricia Rodríguez dijo...

Excelente opciòn. Los tiempos se redujeron enormemente Gracias!

Sato dijo...

Muchisimas Gracias, sorprendente el aumento del rendimiento!

Sergio Arenas dijo...

Es increíble lo rápido que lo ha hecho, ni 3 segundos. A través de una sentencia con joins, casi dos minutos.
Muchas gracias.

Xt!@n dijo...

Hola. Me aparece error siguendo la lógica del bulk collect... Compilo sin problemas pero El error al ejecutar es en el fetch: numeric o value error: bulk bind: truncate bind. Ayuda xfavorrr

Xt!@n dijo...

No se si encontre error pero cambié de select rowid, tabla.* a select rowid, t.campo1, t.campo2,...t.campo_ultimo y solucionó el error. Pero tengo otra duda.. Mi tabla es de 11millones y el update no tienen ningun where aparte del rowid, termina procesar sin errores.. Pero solo actualizo 1'200,000 regs. A que puede deberse. Graciassss

hember ladera dijo...

Saludos, me surge una duda de porque colocas la siguiente fracción de código que hago referencia en un ciclo? siendo este que debería ser ejecutado solo una vez para ser calculado?

FOR i IN 1 .. m_rowid.count
LOOP
m_col1(i) := calculos(m_col1(i), m_col2(i));
END LOOP;

José Luis Pérez dijo...

Hember, no entiendo tu pregunta. Es una matriz de datos y para recorrerla se necesita un bucle.

hember ladera dijo...

Si claro te entiendo, pero no bastaría con el primer bucle?

José Luis Pérez dijo...

No si quieres aprovechar la funcionalidad BULK COLLECT y procesar los registros de 100 en 100.

hember ladera dijo...

Buenas tardes necesito su ayuda, estoy intentado cargar una tabla con un cursor y posteriormente realiz un insert pero ejecutanto el proceso siempre me esta dando el error ORA-01652: unable to extend temp segment by 128 in tablespace TEMP y verifico el tablespace y tiene 4 GB disponible, no se supone que cuando se realiza un bulk collect y se coloca limite como 1000 levanta 1000 registros del cursor y los comiitea y libera la memoria??

José Luis Pérez dijo...

Hola Hember,
No, el bulk collect no hace ningún COMMIT, de hecho es error que indicas puede generarse antes que con un procesamiento normal.

hember ladera dijo...

Gracias Jose Luis por tu respuesta, yo tengo un commit luego de hacer el INSERT, y en al final de clausula Fetch tengo un limit de 1000 que segun tengo entendido deberi hacer el commit de los 1000 registros y luego consulta 1000 mas. Es esto asi? o cual deberia ser el funcionamiento del bull collect?? Te pregunto porque la consulta agota el espacio disponible del tablespace temp y queria ver si con esto se podia evitar haciendo commit cada 100 registro, no me ha funcionado.

casd dijo...

Hola, cuando hago una actualización masiva cada tanto guardo con un commit ya que a veces se me bloquea la db o se pone muy lento. Este procedimiento me evitaria ese problema?

JLPM dijo...

Hola Casd, sí, la cláusula BULK COLLECT es precisamente para evitar el problema que comentas.