El paquete DBMS_PARALLEL_EXECUTE, procesamiento en paralelo desde PL/SQL

En muchas ocasiones nos encontraremos con la necesidad de realizar operaciones DML (UPDATE, INSERT, etcétera) sobre millones de registros de una tabla. Escribir el código PL/SQL encargado de realizar estas operaciones no tiene por qué ser complicado, pero el manejo de los segmentos de rollback y conseguir que el rendimiento de dicho código sea bueno, es decir, que termine en un tiempo aceptable, puede ser harina de otro costal.

Todas las nuevas versiones de las bases de datos Oracle incluyen una amplia variedad de nuevas funcionalidades que amplían su capacidad. La release 2 de la versión 11g de las bases de datos Oracle no es una excepción a esta norma e incluye más de cincuenta nuevos paquetes PLSQL, siendo uno de ellos el DBMS_PARALLEL_EXECUTE.

El paquete DBMS_PARALLEL_EXECUTE proporciona a los programadores PL/SQL la posibilidad de dividir una tabla enorme en función de una gran variedad de criterios, desde rangos de ROWID hasta métodos de particionado definidos por el propio usuario. Una vez definidos es posible ejecutar una secuencia SQL o un bloque PLSQL contra estos diferentes “pedazos” de tabla en paralelo, utilizando el planificador de la base de datos Oracle para gestionar los procesos que se están ejecutando en background. El planificador, a pesar de realizar procesamiento en paralelo, puede gestionar también errores, reintentos automáticos y COMMITS.

Para utilizar el paquete DBMS_PARALLEL_EXECUTE y poder ejecutar tareas en paralelo, nuestro esquema o usuario de la base de datos Oracle debe tener permiso del sistema para crear trabajos (CREATE JOB system privilege). Teniendo este permiso podremos utilizar cualquiera de los siguientes subprogramas, rutinas o procedimientos del paquete DBMS_PARALLEL_EXECUTE:

• CREATE_TASK: permite crear una tarea para ser manejada utilizando el paquete DBMS_PARALLEL_EXECUTE.
• CREATE_CHUNKS_BY_ROWID: define en base al ROWID las diferentes particiones del total de registros que van a ser modificados por nuestra sentencia SQL DML.
• CREATE_CHUNKS_BY_SQL: lo mismo pero basándose en una sentencia SQL definida por el usuario.
• CREATE_CHUNKS_BY_NUMBER_COL: lo mismo pero basándose en los valores de una columna numérica.
• RUN_TASK: ejecuta una tarea después de que las particiones se hayan definido.
• TASK_STATUS: obtiene el estado en que se encuentra una determinada tarea.
• STOP_TASK: detiene la ejecución de una tarea.
• RESUME_TASK: reinicia la ejecución de una tarea que ha sido parada previamente o que ha fallado por cualquier motivo.
• DROP_TASK: elimina una tarea cuando esta ha sido completada.

Todos estos procedimientos y algunos otros que se encuentran definidos en el paquete estándar PL/SQL DBMS_PARALLEL_EXECUTE, con excepción como es lógico de TASK_STATUS, realizan un COMMIT.

A continuación os muestro un ejemplo de código PLSQL que os permitirá comprender mejor el funcionamiento del paquete DBMS_PARALLEL_EXECUTE. En dicho código he utilizado el método más sencillo, es decir, el basado en particionar nuestra tabla por ROWID (en artículos posteriores incluiré ejemplos de otros tipos de particionado).

Ahora supongamos que trabajamos para una compañía eléctrica y que ésta nos ha encargado aplicar una subida de tarifas a todos los usuarios de dicha compañía, con la particularidad de que si falla alguna de las tareas procesadas en paralelo sea posible reintentar dicha tarea durante un número específico de veces hasta que se complete satisfactoriamente.

El procedimiento PL/SQL en cuestión podría ser el siguiente:

CREATE OR REPLACE PROCEDURE subida_tarifas (
  porcentaje IN NUMBER,
  reintentos IN PLS_INTEGER DEFAULT 5)
IS
  c_update_sql CONSTANT VARCHAR2 (500) :=
    'UPDATE /*+ ROWID (tar) */ TARIFAS tar
     SET tar.tarifa = tar.tarifa * (1 + porcentaje/100)
     WHERE ROWID BETWEEN :rowid_ini AND :rowid_fin';
  c_tarea CONSTANT VARCHAR2 (25) := 'Calcular Tarifas';
  l_intentos PLS_INTEGER := 1;

BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_TASK (c_tarea);

  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID (
    task_name => c_tarea,
    table_owner => USER,
    table_name => 'TARIFAS',
    by_row => TRUE,
    chunk_size => 1000);

  DBMS_PARALLEL_EXECUTE.RUN_TASK (
    task_name => c_tarea,
    sql_stmt => c_update_sql,
    language_flag => DBMS_SQL.native,
    parallel_level => 10);

  LOOP
    EXIT WHEN
      DBMS_PARALLEL_EXECUTE.TASK_STATUS (c_tarea) =
      DBMS_PARALLEL_EXECUTE.FINISHED
    OR l_intentos > reintentos;
      l_intentos := l_intentos + 1;
      DBMS_PARALLEL_EXECUTE.RESUME_TASK (c_tarea);
  END LOOP;

  DBMS_PARALLEL_EXECUTE.DROP_TASK (c_tarea);

END subida_tarifas; 

Como podéis observar el procedimiento subida_tarifas realiza las siguientes acciones principales:

• Se define la sentencia SQL para realizar el UPDATE de la tabla TARIFAS. En el UPDATE se utiliza un hint especificando que se realice un escaneo por ROWID de dicha tabla. También se incluyen dos parámetros de sustitución (:rowid_ini y :rowid_fin). Este código SQL será ejecutado utilizando el también paquete estándar DBMS_SQL y los parámetros de sustitución serán reemplazados por los valores de ROWID determinados por el valor del parámetro CHUNK_SIZE definido más tarde en el código.
• Se define el nombre de la tarea utilizando una constante para evitar tener que utilizar el literal 'Calcular tarifas' a lo largo de todo el código.
• Se crea la nueva tarea (CREATE_TASK).
• Se especifica que el tipo de particionado para la tarea 'Calcular tarifas' y la tabla TARIFAS (el nombre de la tabla debe referenciarse en mayúsculas, en caso contrario el código PLSQL devolverá un error) es por ROWID (CREATE_CHUNKS_BY_ROWID). El parámetro BY_ROW se pone a TRUE, de esta manera el parámetro CHUNK_SIZE hace referencia al número de registros (si BY_ROW fuera FALSE, entonces CHUNK_SIZE haría referencia al número de bloques).
• Se ejecuta la tarea (RUN_TASK), es decir, el UPDATE definido en el primer paso, permitiendo la ejecución de 10 trabajos simultáneos (valor del parámetro PARALLEL_LEVEL).
• A continuación se ejecuta un sencillo bucle LOOP del que se saldrá si la tarea termina satisfactoriamente (estado FINISHED) o si el número de reintentos supera el valor establecido por parámetro REINTENTOS.
• Dentro del bucle se incrementa el número de reintentos y se llama al procedimiento RESUME_TASK que reiniciará la tarea cuando esta no ha terminado correctamente.
• Finamente se elimina la tarea (DROP_TASK) para que pueda volver a ser utilizada.

En conclusión, el paquete estándar PLSQL DBMS_PARALLEL_EXECUTE se trata realmente de una API de alto nivel que permite de una manera elegante especificar la ejecución en paralelo de una sentencia DML, agilizando de esta forma su ejecución y mejorando el rendimiento de la misma.

Artículos relacionados: Cláusula BULK COLLECT para mejorar el rendimiento.