Neste artigo veremos como criar um processo para executar vários procedimentos de forma paralela no banco de dados↳Banco de dados134 conteúdosSQL ou NoSQL: eis a questão!!Data · mar 2020Banco de dados: como organizar e dar segurança para milhões de dados de loteriasData · mai 20215 serviços gratuitos na cloud para bancos de dados PostgresData · fev 2025Ver tudo em Data → 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<br />IS<br /><br />TYPE TYP_EXECUTAR IS RECORD (ROTINA VARCHAR2(400),<br /> ERRO VARCHAR2(2000));<br /><br />TYPE TYP_TB_EXECUTAR IS TABLE OF TYP_EXECUTAR INDEX BY BINARY_INTEGER;<br /><br /><br />PROCEDURE EXECUTAR(P_EXECUTAR IN OUT TYP_TB_EXECUTAR);<br /><br />END;<br /><br />CREATE OR REPLACE PACKAGE BODY utl_paralelo<br />IS<br /><br /> V_PIPENAME VARCHAR2(30);<br /> V_INDEX_PIPE NUMBER := 0;<br /> V_RESULTADO NUMBER;<br /> V_ERRO VARCHAR2(2000);<br /><br />PROCEDURE INICIAR IS<br /> V_CRIADO INTEGER;<br />BEGIN<br /><br /> V_PIPENAME := DBMS_PIPE.UNIQUE_SESSION_NAME;<br /> V_CRIADO := DBMS_PIPE.CREATE_PIPE(V_PIPENAME);<br /> DBMS_PIPE.RESET_BUFFER;<br /> END;<br /><br /> FUNCTION COMANDO(P_ROTINA IN VARCHAR2)<br /> RETURN VARCHAR2<br /> IS<br /> V_COMANDO VARCHAR2(400);<br /> BEGIN<br /><br /> V_INDEX_PIPE := V_INDEX_PIPE + 1;<br /> V_COMANDO := V_COMANDO || ' declare';<br /> V_COMANDO := V_COMANDO || ' v_resultado number;';<br /> V_COMANDO := V_COMANDO || ' v_erro varchar2(2000);';<br /> V_COMANDO := V_COMANDO || ' begin';<br /> V_COMANDO := V_COMANDO || ' begin';<br /> V_COMANDO := V_COMANDO || ' ' || P_ROTINA;<br /> V_COMANDO := V_COMANDO || ' v_erro := null;';<br /> V_COMANDO := V_COMANDO || ' exception when others then';<br /> V_COMANDO := V_COMANDO || ' v_erro := sqlerrm;';<br /> V_COMANDO := V_COMANDO || ' end;';<br /> V_COMANDO := V_COMANDO || ' DBMS_PIPE.pack_message('||V_INDEX_PIPE||');';<br /> V_COMANDO := V_COMANDO || ' DBMS_PIPE.pack_message(v_erro);';<br /> V_COMANDO := V_COMANDO || ' v_resultado := DBMS_PIPE.send_message(''' ||V_PIPENAME||''');';<br /> V_COMANDO := V_COMANDO || ' end;';<br /><br /> RETURN V_COMANDO;<br /><br /> END;<br /><br /> PROCEDURE AGENDAR(P_COMANDO IN VARCHAR2) <br /> IS<br /> V_ID_JOB NUMBER;<br /> BEGIN<br /><br /> DBMS_JOB.SUBMIT(JOB => V_ID_JOB,<br /> WHAT => P_COMANDO,<br /> NEXT_DATE => SYSDATE); <br /><br /> COMMIT;<br /><br /> END;<br /><br /> PROCEDURE RETORNO<br /><br /> IS<br /><br /> BEGIN<br /><br /> V_RESULTADO := DBMS_PIPE.RECEIVE_MESSAGE(V_PIPENAME);<br /> DBMS_PIPE.UNPACK_MESSAGE(V_RESULTADO);<br /> DBMS_PIPE.UNPACK_MESSAGE(V_ERRO);<br /><br /> END;<br /><br /> PROCEDURE EXECUTAR(P_EXECUTAR IN OUT TYP_TB_EXECUTAR)<br /> IS<br /> V_COMANDO VARCHAR2(400);<br /><br /> BEGIN<br /><br /> INICIAR;<br /><br /> FOR DD IN 1..P_EXECUTAR.COUNT LOOP<br /><br /> V_COMANDO := COMANDO(P_ROTINA => P_EXECUTAR(DD).ROTINA);<br /><br /> AGENDAR(P_COMANDO => V_COMANDO);<br /><br /> END LOOP;<br /><br /><br /> FOR DD IN 1..V_INDEX_PIPE LOOP<br /><br /> RETORNO;<br /> P_EXECUTAR(V_RESULTADO).ERRO := V_ERRO;<br /><br /> END LOOP;<br /><br /> END EXECUTAR;<br /><br />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,<br /> b CHAR,<br /> c DATE);Abaixo, temos um bloco anônimo com um exemplo de chamada do pacote.
DECLARE<br /><br />V_EXECUTAR UTL_PARALELO.TYP_TB_EXECUTAR;<br /><br />BEGIN<br /><br />V_EXECUTAR(1).ROTINA := 'INSERT INTO teste VALUES (1,''C'',SYSDATE);COMMIT;';<br />V_EXECUTAR(2).ROTINA := 'INSERT INTO teste VALUES (''A'',3,SYSDATE);COMMIT;';<br />V_EXECUTAR(3).ROTINA := 'DECLARE RESULT NUMBER;BEGIN RESULT := dbms_random.random; END;';<br />V_EXECUTAR(3).ROTINA := 'DECLARE RESULT NUMBER;BEGIN RESULT := 10/0; END;';<br /><br />UTL_PARALELO.EXECUTAR(P_EXECUTAR => V_EXECUTAR);<br /><br /><br />FOR DD IN 1..V_EXECUTAR.COUNT LOOP<br /><br /> DBMS_OUTPUT.PUT_LINE(DD||'-'||V_EXECUTAR(DD).ERRO);<br /><br />END LOOP;<br /><br />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.
- 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.
1-<br />2-ORA-01722: invalid number<br />3-<br />4-ORA-01476: divisor is equal to zeroAs 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.
é formado em Análise de Sistemas pela Uniban. Possui 10 anos de experiência em análise, implementação e desenvolvimento de softwares com Oracle Forms/Reports, PL/SQL. Também possui experiência de 6 anos em WebTool Kit, HTML, JavaScript, XML, CSS e APEX, além de conhecimentos em JAVA e Delphi. Possui certificação Oracle Advanced PL/SQL Developer Certified Professional 11g. Em sua experiência profissional teve a oportunidade de participar de diversos projetos, dos quais pode-se destacar migrações de sistemas de arquivos indexados em Cobol para banco de dados Oracle; tunning em camada de aplicações e camada de banco de dados; administração de banco de dados Oracle 9i e 10g; modelagem relacional de dados utilizando Erwin; migração do Forms 6i para Forms IAS 10g; levantamento, análise e desenvolvimento de software em Delphi com Oracle, Oracle WebTool Kit e APEX. Atualmente trabalha em uma empresa petroquimica, na qual atua como Desenvolvedor Oracle EBS, desenvolvendo customizações para todos os módulos, nos padrões e recursos do ERP, utilizando PL/Sql, Forms 6i, Reports 6i, Discover, WorkFlow e APEX.
