Neste artigo veremos como criar um processo para executar vários procedimentos de forma paralela no banco de dados Oracle.

No Oracle, processos em uma mesma sessão são executados sempre de forma sequencial, ou seja um processo deve esperar o término de seu precedente para executar. A execução de procedimentos em paralelo ocorre quando vários procedimentos são executados simultaneamente na mesma sessão.

Como não é possível chamar a execução de procedimentos em paralelo dentro de uma mesma sessão, vou utilizar o subprograma DBMS_JOB.SUBMIT para agendar a execução dos processos, assim criando o efeito de execução em paralelo.

A cada execução de um JOB, uma nova sessão é criada no banco de dados, fazendo com que o processamento de vários JOBs possam ser executados ao mesmo tempo.

A utilização do pacote DBMS_PIPE será útil para capturar possíveis erros ocorridos no momento da execução do JOB.

Criando o pacote

Criaremos o pacote UTL_PARALELO, que contém os seguintes subprogramas:

• Iniciar: Este subprograma cria um novo PIPE para efetuar a comunicação entre as sessões.
• Comando: Adequa o comando passado para possibilitar a execução do JOB.
• Agenda: Agenda o JOB com execução imediata.
• Retorno: Retorna as possíveis mensagens de erro ocorridas na execução do JOB.
• Executa: Subprograma principal que recebe como parâmetro uma lista com todas as rotinas que deverão ser executadas em paralelo e cria os JOBs retornando os possíveis erros ocorridos neste.

Na especificação do pacote, temos o tipo TYP_TB_EXECUTAR que é uma tabela do Record TYP_EXECUTAR. Será utilizado para definir o tipo do parâmetro P_EXECUTAR do subprograma Executar.

CREATE OR REPLACE PACKAGE utl_paralelo
IS

TYPE TYP_EXECUTAR IS RECORD (ROTINA    VARCHAR2(400),
                             ERRO      VARCHAR2(2000));

TYPE TYP_TB_EXECUTAR IS TABLE OF TYP_EXECUTAR INDEX BY BINARY_INTEGER;


PROCEDURE EXECUTAR(P_EXECUTAR IN OUT TYP_TB_EXECUTAR);

END;

CREATE OR REPLACE PACKAGE BODY utl_paralelo
IS

  V_PIPENAME   VARCHAR2(30);
  V_INDEX_PIPE NUMBER := 0;
  V_RESULTADO  NUMBER;
  V_ERRO       VARCHAR2(2000);

PROCEDURE INICIAR IS
    V_CRIADO INTEGER;
BEGIN

    V_PIPENAME := DBMS_PIPE.UNIQUE_SESSION_NAME;
    V_CRIADO  := DBMS_PIPE.CREATE_PIPE(V_PIPENAME);
    DBMS_PIPE.RESET_BUFFER;
 END;

 FUNCTION COMANDO(P_ROTINA IN VARCHAR2)
 RETURN VARCHAR2
 IS
 V_COMANDO VARCHAR2(400);
 BEGIN

    V_INDEX_PIPE := V_INDEX_PIPE + 1;
    V_COMANDO   := V_COMANDO || ' declare';
    V_COMANDO   := V_COMANDO || '   v_resultado number;';
    V_COMANDO   := V_COMANDO || '   v_erro varchar2(2000);';
    V_COMANDO   := V_COMANDO || ' begin';
    V_COMANDO   := V_COMANDO || '    begin';
    V_COMANDO   := V_COMANDO || '    ' || P_ROTINA;
    V_COMANDO   := V_COMANDO || '       v_erro := null;';
    V_COMANDO   := V_COMANDO || '    exception when others then';
    V_COMANDO   := V_COMANDO || '       v_erro := sqlerrm;';
    V_COMANDO   := V_COMANDO || '    end;';
    V_COMANDO   := V_COMANDO || '  DBMS_PIPE.pack_message('||V_INDEX_PIPE||');';
    V_COMANDO   := V_COMANDO || '  DBMS_PIPE.pack_message(v_erro);';
    V_COMANDO   := V_COMANDO || '  v_resultado := DBMS_PIPE.send_message(''' ||V_PIPENAME||''');';
    V_COMANDO   := V_COMANDO || ' end;';

  RETURN  V_COMANDO;

 END;

 PROCEDURE AGENDAR(P_COMANDO IN VARCHAR2) 
 IS
 V_ID_JOB NUMBER;
 BEGIN

    DBMS_JOB.SUBMIT(JOB => V_ID_JOB,
                    WHAT => P_COMANDO,
                    NEXT_DATE => SYSDATE);    

    COMMIT;

 END;

 PROCEDURE RETORNO

 IS

 BEGIN

   V_RESULTADO := DBMS_PIPE.RECEIVE_MESSAGE(V_PIPENAME);
   	           DBMS_PIPE.UNPACK_MESSAGE(V_RESULTADO);
		    DBMS_PIPE.UNPACK_MESSAGE(V_ERRO);

 END;

 PROCEDURE EXECUTAR(P_EXECUTAR IN OUT TYP_TB_EXECUTAR)
 IS
 V_COMANDO VARCHAR2(400);

 BEGIN

   INICIAR;

   FOR DD IN 1..P_EXECUTAR.COUNT LOOP

      V_COMANDO := COMANDO(P_ROTINA => P_EXECUTAR(DD).ROTINA);

      AGENDAR(P_COMANDO => V_COMANDO);

   END LOOP;


   FOR DD IN 1..V_INDEX_PIPE LOOP

     RETORNO;
     P_EXECUTAR(V_RESULTADO).ERRO := V_ERRO;

   END LOOP;

 END EXECUTAR;

END;

Quando utilizado o subprograma DBMS_JOB.SUBMIT para execução do JOB e não passado valor para o parâmetro NEXT_DATE, o Oracle automaticamente após a execução do JOB, o deleta.

Alterando o parametro JOB_QUEUE_PROCESSES

Para executar as rotinas em paralelo, é necessário alterar o parâmetro JOB_QUEUE_PROCESSES, que indica a quantidade de Jobs que podem ser executados ao mesmo tempo no banco de dados. Neste momento, vamos colocar o valor 10, ou seja, o banco de dados é capaz de executar 10 jobs ao mesmo tempo. Esse valor pode ser obtido baseando-se na quantidade de procedimentos que serão executados.

ALTER SYSTEM SET job_queue_processes = 10;

Vale lembrar que para mudar esse parâmetro é necessário ter permissão de administrador no banco de dados.

Executando os procedimentos

Para efetuar o teste, criaremos a tabela:

CREATE TABLE teste(a NUMBER,
                b CHAR,
                   c DATE);

Abaixo, temos um bloco anônimo com um exemplo de chamada do pacote.

DECLARE

V_EXECUTAR UTL_PARALELO.TYP_TB_EXECUTAR;

BEGIN

V_EXECUTAR(1).ROTINA := 'INSERT INTO teste VALUES (1,''C'',SYSDATE);COMMIT;';
V_EXECUTAR(2).ROTINA := 'INSERT INTO teste VALUES (''A'',3,SYSDATE);COMMIT;';
V_EXECUTAR(3).ROTINA := 'DECLARE RESULT NUMBER;BEGIN RESULT := dbms_random.random; END;';
V_EXECUTAR(3).ROTINA := 'DECLARE RESULT NUMBER;BEGIN RESULT := 10/0; END;';

UTL_PARALELO.EXECUTAR(P_EXECUTAR => V_EXECUTAR);


FOR DD IN 1..V_EXECUTAR.COUNT LOOP

 DBMS_OUTPUT.PUT_LINE(DD||'-'||V_EXECUTAR(DD).ERRO);

END LOOP;

END;

Veja que, no exemplo, criamos a tabela TESTE com 3 colunas, a coluna a do tipo Number, a coluna b do tipo Char e a coluna c do tipo Date.
No bloco anônimo, temos a chamada para efetuar 4 comandos em paralelo.

1. Uma inserção válida na tabela teste.
2. Uma inserção inválida na tabela teste.
3. Uma chamada válida do subprograma DBMS_RANDOM.RANDOM.
4. Uma divisão inválida por zero.

Após executado o teste, temos os seguintes erros capturados dos JOBs através do pacote DBMS_PIPE.

1-
2-ORA-01722: invalid number
3-
4-ORA-01476: divisor is equal to zero

As execuções do primeiro e terceiro comando ocorreram com sucesso. A execução do segundo e quarto comando ocorreram com erro, como era o esperado.

Neste artigo, demonstrei uma forma alternativa para executar rotinas em paralelo no banco de dados Oracle. A utilização de paralelismo em execução de procedimentos pode ajudar muito no desempenho das aplicações.

Até a próxima.