Como executar procedimentos em paralelo no Oracle -
PorLeonardo Litz em	12/08/2010
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.
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
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);
CREATE OR REPLACE PACKAGE BODY utl_paralelo
V_PIPENAME   VARCHAR2(30);
V_INDEX_PIPE NUMBER := 0;
V_RESULTADO  NUMBER;
V_ERRO       VARCHAR2(2000);
PROCEDURE INICIAR IS
V_CRIADO INTEGER;
V_PIPENAME := DBMS_PIPE.UNIQUE_SESSION_NAME;
V_CRIADO  := DBMS_PIPE.CREATE_PIPE(V_PIPENAME);
FUNCTION COMANDO(P_ROTINA IN VARCHAR2)
V_COMANDO VARCHAR2(400);
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;
PROCEDURE AGENDAR(P_COMANDO IN VARCHAR2)
V_ID_JOB NUMBER;
DBMS_JOB.SUBMIT(JOB => V_ID_JOB,
WHAT => P_COMANDO,
PROCEDURE RETORNO
V_RESULTADO := DBMS_PIPE.RECEIVE_MESSAGE(V_PIPENAME);
DBMS_PIPE.UNPACK_MESSAGE(V_RESULTADO);
DBMS_PIPE.UNPACK_MESSAGE(V_ERRO);
PROCEDURE EXECUTAR(P_EXECUTAR IN OUT TYP_TB_EXECUTAR)
FOR DD IN 1..P_EXECUTAR.COUNT LOOP
V_COMANDO := COMANDO(P_ROTINA => P_EXECUTAR(DD).ROTINA);
AGENDAR(P_COMANDO => V_COMANDO);
FOR DD IN 1..V_INDEX_PIPE LOOP
P_EXECUTAR(V_RESULTADO).ERRO := V_ERRO;
END EXECUTAR;
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.
Vale lembrar que para mudar esse parâmetro é necessário ter permissão de administrador no banco de dados.
Para efetuar o teste, criaremos a tabela:
CREATE TABLE teste(a NUMBER,
Abaixo, temos um bloco anônimo com um exemplo de chamada do pacote.
V_EXECUTAR UTL_PARALELO.TYP_TB_EXECUTAR;
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);
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.
Uma inserção válida na tabela teste.
Uma inserção inválida na tabela teste.
Uma chamada válida do subprograma DBMS_RANDOM.RANDOM.
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.
2-ORA-01722: invalid number
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.
24 de agosto de 2010 às 9:18
Excelente artigo Leo, é isso ai mano… parabens!!!
24 de agosto de 2010 às 9:52
Muito bom o artigo, criei um processo para execuções em paralelos, porém a performance deixa a desejar.