Nenhum resultado encontrado

Sua pesquisa não corresponde a nenhum resultado.

Sugerimos que você tente o seguinte para ajudar a encontrar o que procura:

  • Verifique a ortografia da sua pesquisa por palavra-chave.
  • Use sinônimos para a palavra-chave digitada; por exemplo, tente “aplicativo” em vez de “software.”
  • Tente uma das pesquisas populares mostradas abaixo.
  • Inicie uma nova pesquisa.
Perguntas Frequentes

DBMS_PARALLEL_EXECUTE - BY ROWID

Por Regis Aparecido de Araujo ,
Postado em Março 2014

Do que se trata o artigo: Neste artigo será apresentado uma package que foi implantada no Oracle 11g chamada DBMS_PARALLEL_EXECUTE. Em que situação o tema é útil: O ponto destacado neste artigo será útil quando houver a necessidade de realizar a alteração de valores em uma quantidade elevada de registros em um curto período de tempo.

No artigo de hoje, iremos falar sobre a package DBMS_PARALLEL_EXECUTE, que foi implementada no Oracle 11g.

Está package permite alterar registros de uma tabela de forma incremental e em paralelo, para isto utiliza 2 passos:

  • Dividindo logicamente a tabela em pedaços/blocos menores que a tabela original
  • Executando o comando sulicitado em cada pedaço paralelamente, comitando a transação ao final de cada pedaço/blocos alterado.

Esta package pode ser executada por qualquer usuário que possua grant de “create job”.

O ganho de tempo na utilização desta package é considerável em comparação a um update comum.

Desta forma, irei demonstrar os procedimentos necessários para a utilização desta package.

Para a demonstração deste artigo, estou utilizando o Virtual Box da Oracle com as especificações abaixo:

  • Virtual Box Versão 4.2.12 r84980
  • Oracle Enterprise Linux 6.4
  • Oracle Enterprise 11.2.0.2
  • 1 core
  • 2GB de RAM

Como eu havia informado anteriormente, esta package poderá ser usada por qualquer usuário que possua privilégio de “Create Job”.



Connected to Oracle Database 11g Enterprise Edition  Release 11.2.0.2.0 Connected as system  
11:05:37 GPODB.SYSTEM>> create user regis  identified by regis default tablespace GPODB_DATA  
 temporary tablespace TEMP;
 User created 
 11:05:59 GPODB.SYSTEM>> grant connect,  resource to regis;
 Grant succeeded 
 11:06:07 GPODB.SYSTEM>> grant create job to  regis;
 Grant succeeded 
 11:06:15 GPODB.SYSTEM>>  
 11:07:04 GPODB.SYSTEM>> conn  regis/regis@GPODBConnected to Oracle Database 11g Enterprise Edition   Release 11.2.0.2.0 Connected as regis 
 11:07:11 GPODB.REGIS>> 11:07:17 GPODB.REGIS>> 11:07:17 GPODB.REGIS>>  
 CREATE TABLE TESTE_CHUNCK (ID_TESTE NUMBER, NM_TESTE VARCHAR2(50), DT_TESTE DATE); 
 Table created 
 11:09:45 GPODB.REGIS>> CREATE TABLE  TESTE_CHUNCK_B (ID_TESTE NUMBER, NM_TESTE VARCHAR2(50), DT_TESTE DATE);
 Table created 
 11:09:51 GPODB.REGIS>> ALTER TABLE  TESTE_CHUNCK ADD CONSTRAINT PK_TESTE_CHUNCK PRIMARY KEY(ID_TESTE)  
 USING INDEX;
 Table altered 
 11:09:56 GPODB.REGIS>> ALTER TABLE  TESTE_CHUNCK_B ADD CONSTRAINT PK_TESTE_CHUNCK_B PRIMARY KEY(ID_TESTE)
 USING  INDEX;
 Table  altered

Para que eu possa mensurar os tempos, “setei” o parâmetro timing para on.



11:10:01 GPODB.REGIS>> set timing on; 
 11:10:06  GPODB.REGIS>>

Realizei uma carga de 10 milhões de registros em cada tabela, desta forma terei uma massa de dados considerável para a demonstração neste artigo.


11:10:06 GPODB.REGIS>>   
11:13:39 GPODB.REGIS>> DECLARE     
	2     
 	3  
	BEGIN 
   	4     
 	5  
	FOR A IN 1..10000000 LOOP    
	6  INSERT INTO  TESTE_CHUNCK   VALUES (A, 'TESTE INSERT A',  SYSDATE); 
   	7  INSERT INTO  TESTE_CHUNCK_B VALUES (A, 'TESTE INSERT A', SYSDATE);    
	8  END LOOP;    
	9  COMMIT;    	
	10      	
	11 END;   
 	12 /
	
	PL/SQL procedure successfully completed
	Executed in 1428,86 seconds

Com a carga de dados realizada, irei realizar a alteração da metade dos registros de cada tabela, serão realizados 2 procedimentos de update:

  • Update com o commit sendo realizado apenas ao final.
  • Update com o commit sendo realizado a cada 5000 registros.

Lembrando que para estes testes estou utilizando um banco de dados criado em uma máquina virtual, com baixíssima capacidade computacional.


11:37:27 GPODB.REGIS>>  
 11:41:31 GPODB.REGIS>> 
 11:41:32 GPODB.REGIS>> 
 DECLARE    
 2      
 3 
 BEGIN   
 4      
 5  FOR A IN 1..10000000  LOOP  
 6      	
 7  UPDATE TESTE_CHUNCK_B SET  NM_TESTE = 'TESTE INSERT B' WHERE MOD(ID_TESTE,2) = 0 AND ID_TESTE = A;  
 8      	
 9  END LOOP;  
 10 COMMIT; 
 11      
 12 END; 
 13 /
 PL/SQL procedure successfully completed
 Executed  in 818,148 seconds

Neste primeiro procedimento, pode-se verificar que o tempo ficou em mais de 10 minutos para alterar 5 milhões de registros.


11:55:10 GPODB.REGIS>> 
  12:04:13 GPODB.REGIS>> DECLARE  
  2  B NUMBER := 0;    	
  3  BEGIN  
  4  FOR A IN 1..10000000  LOOP  
  5  UPDATE TESTE_CHUNCK SET  NM_TESTE = 'TESTE INSERT B' WHERE MOD(ID_TESTE,2) = 1 AND ID_TESTE = A;  
  6  B := B+1;  
  7  IF B >= 5000 THEN  
  8  COMMIT;  
  9  B := 0; 
  10 END IF;  
  11 END LOOP;
  12 COMMIT;  	
  13 END;  
  14 /
  PL/SQL procedure successfully completed
  Executed  in 756,277 seconds

Neste segundo procedimento, pode-se verificar que o tempo ficou em mais de 10 minutos também, porém houve um ganho, muito pequeno mais houve.

Podemos ver abaixo que as tabelas estão com 50% de seus dados alterados.


12:16:51 GPODB.SYSTEM 
 12:20:46 GPODB.REGIS>> SELECT COUNT(1),  NM_TESTE FROM TESTE_CHUNCK  
 2  GROUP BY NM_TESTE    
 3  /
 COUNT(1)    NM_TESTE 

 ----------  --------------------------------------------------  

 5000000  TESTE INSERT A  
 5000000  TESTE INSERT B
 Executed in 5,101 seconds
 12:20:51 GPODB.REGIS>> 
 12:20:56 GPODB.REGIS>> SELECT COUNT(1),  NM_TESTE FROM TESTE_CHUNCK_B 
 2  GROUP BY NM_TESTE  
 3  /
 COUNT(1)  
 NM_TESTE  
 
 ----------  --------------------------------------------------  

 5000000  TESTE INSERT A   
 5000000  TESTE INSERT B
 Executed in 5,195 seconds

Agora irei demonstrar o ganho que a package pode proporcionar no mesmo caso, para que este teste seja o mais real possível, eu não culetei estatísticas em nenhum momento.

Para iniciar o procedimento, é necessário criar uma tarefa (task).


12:21:01 R104D.SYSTEM>> 
  12:21:21 R104D.SYSTEM>> BEGIN  
  2  DBMS_PARALLEL_EXECUTE.create_task (task_name => 'GPO_REGIS'); 
  3  END;  
  4  /
  PL/SQL procedure successfully completed
  Executed in 0,202 seconds
  12:21:01 GPODB.REGIS>> 

  12:21:28 GPODB.REGIS>> SELECT TASK_NAME,  
  2  STATUS  
  3  FROM   USER_PARALLEL_EXECUTE_TASKS; 
  TASK_NAME               STATUS 
  
  ----------------------- ------------------- 
  
  GPO_REGIS               CREATED
  
  Executed in 0,171 seconds

Após a tarefa criada, irei iniciar o procedimento para a criação dos pedaços/blocos da tabela.


12:21:57 GPODB.REGIS>>
   12:21:57 GPODB.REGIS>>> BEGIN    
   2  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   => 'GPO_REGIS',  
   3                                               table_owner => 'REGIS',       
   4                                               table_name  => 'TESTE_CHUNCK',  
   5                                               by_row      => TRUE,       
   6                                               chunk_size  => 1000000);   
   7 END;     
   8 /
   PL/SQL procedure successfully completed
   Executed in 0,92 seconds

O parâmetro chunk_size é o número aproximado de linhas para cada ciclo de commit, caso o parâmetro by_row for setado para FALSE, o valor do chunck_size será o número aproximado de blocos.


12:22:19 GPODB.REGIS>> 
  12:22:20 GPODB.REGIS>> SELECT COUNT(1),  STATUS 
  2   FROM  USER_PARALLEL_EXECUTE_CHUNKS      
  3   WHERE TASK_NAME = 'GPO_REGIS'         
  4   GROUP BY STATUS         
  5  /
  COUNT(1)   STATUS 

  ---------- --------------------  

  688 UNASSIGNED

Pode-se verificar que foram criados 688 pedaços/blocos da tabela original, a informação de UNASSIGNED, indica que nenhum processo está executando update naquele pedaço.

Irei iniciar a tarefa para que possamos observar todo o processo.


12:22:42 GPODB.REGIS>> 
  12:22:59 GPODB.REGIS>>  
  12:22:59 GPODB.REGIS>> DECLARE  
  2  comando VARCHAR2(32767);  	 
  3  BEGIN  	
  4    
  5  comando := 'UPDATE /*+ ROWID  (A) */ TESTE_CHUNCK A  
  6  SET NM_TESTE =  ''UPDATE CHUNCK''  	
  7  WHERE  MOD(ID_TESTE,2) = 0  
  8  AND rowid  BETWEEN :start_id AND :end_id'; 
  9    	 
  10 DBMS_PARALLEL_EXECUTE.run_task(task_name      => 'GPO_REGIS',  	 
  11                                sql_stmt       => comando,  	
  12                                language_flag  =>  DBMS_SQL.NATIVE,  	
  13                                parallel_level => 5);  	
  14 END;  	
  15 /
  PL/SQL procedure successfully completed
  Executed in 90,933 seconds

Pode-se verificar que o tempo de execução de todo o procedimento foi quase 10x mais rápido que o executado anteriormente.

Enquanto o processo estava sendo executado, abri outra sessão do sqlplus para verificar as alterações ocorrendo.



12:23:04 GPODB.REGIS>> SELECT COUNT(1),  STATUS   
 	2    FROM  USER_PARALLEL_EXECUTE_CHUNKS  	
	3   WHERE TASK_NAME = 'GPO_REGIS'  	
	4   GROUP BY STATUS  	
	5  /
	COUNT(1)    STATUS 

	----------  --------------------    

	5  ASSIGNED         437  UNASSIGNED     
    246  PROCESSED
	Executed in 0,016 seconds
	12:24:03 GPODB.REGIS>> /

	COUNT(1)     STATUS

	----------   -------------------- 

	5   ASSIGNED  	
	95  UNASSIGNED 
 	588  PROCESSED
	Executed in 0,016 seconds
	12:24:10 GPODB.REGIS>> /

	COUNT(1)   STATUS 

	---------- -------------------- 

	688 PROCESSED

	Executed in 0,015 seconds 

Pode-se verificar que o status dos pedaços/blocos estão sendo alterados de UNASSIGNED (não atribuido) para ASSIGNED (atribuido) e PROCESSED (processado).

A quantidade de pedaços/blocos atribuídos será determinado pelo parâmetro parallel_level informado na execução da tarefa. Mas deve-se tomar muito cuidado para não sobrecarregar o banco de dados, pois esta rotina irá criar JOB´s para realizar este procedimento, o número de JOB´s criados simultâneos é o mesmo valor do parallel_level.

Pode-se notar também que os valores na tabela foram alterados conforme os processos eram commitados.


12:23:22 GPODB.REGIS>> SELECT COUNT(1),  NM_TESTE FROM TESTE_CHUNCK 
  	2  GROUP BY NM_TESTE  
	3  /

	COUNT(1)    NM_TESTE 

	----------  --------------------------------------------------   

	718204  TESTE INSERT A    
	5000000  TESTE INSERT B    
	1281796  UPDATE CHUNCK  
	12:24:01 GPODB.REGIS>> /

	COUNT(1)    NM_TESTE

	----------  --------------------------------------------------    

	110317  TESTE INSERT A   
	5000000  TESTE INSERT B  
	4889683  UPDATE CHUNCK  
	12:24:31 GPODB.REGIS>> /

	COUNT(1)    NM_TESTE 

	----------  --------------------------------------------------   

	5000000  TESTE INSERT B  
	5000000  UPDATE CHUNCK

Após concluído a tarefa, a mesma precisa ser removida.


12:26:45 GPODB.REGIS>>   
12:26:47 GPODB.REGIS>> BEGIN  
	2  DBMS_PARALLEL_EXECUTE.drop_task('GPO_REGIS'); 
 	3  END;  
	4  /
	PL/SQL procedure successfully completed
	Executed in 0,125 seconds

Realizei o mesmo procedimento para a outra tabela, porém alterei os valores de CHUNCK_SIZE e PARALLEL_LEVEL.



12:27:31 GPODB.REGIS>>   
12:27:31 GPODB.REGIS>> 
  12:27:32 GPODB.REGIS>> BEGIN   
  2  DBMS_PARALLEL_EXECUTE.create_task (task_name => 'GPO_REGIS_B');  
  3  END;     
  4  /
  PL/SQL procedure successfully completed
  Executed in 0,031 seconds  
  12:27:32 GPODB.REGIS>>  
  12:27:43 GPODB.REGIS>> 
  12:27:43 GPODB.REGIS>>  
  12:27:44 GPODB.REGIS>> 
  SELECT TASK_NAME,       
  2  STATUS     
  3  FROM    
  USER_PARALLEL_EXECUTE_TASKS         
  4  /
  TASK_NAME                                         STATUS 

  ------------------------------------------------  ------------------- 

  GPO_REGIS_B                                       CREATED
  Executed in 0,016 seconds
  12:27:44 GPODB.REGIS>>  
  12:28:25 GPODB.REGIS>> BEGIN 
  2  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   => 'GPO_REGIS_B',   
  3                                               table_owner => 'REGIS',   
  4                                               table_name  =>  'TESTE_CHUNCK_B',          
  5                                               by_row      => TRUE,    
  6                                               chunk_size  => 500000);    
  7  END;         
  8  /
  PL/SQL procedure successfully completed
  Executed in 0,359 seconds
  12:28:25 GPODB.REGIS>>  
  12:28:41 GPODB.REGIS>>   
  12:28:41 GPODB.REGIS>> 
  12:28:42 GPODB.REGIS>> 
  
  SELECT COUNT(1),  STATUS       
  2  FROM  USER_PARALLEL_EXECUTE_CHUNKS  
  3  WHERE TASK_NAME = 'GPO_REGIS_B'    
  4  GROUP BY STATUS         
  5  /

  COUNT(1)    STATUS 

  ----------  --------------------      

  667  UNASSIGNED
  Executed in 0,016 seconds
  12:28:42 GPODB.REGIS>>  
  12:28:55 GPODB.REGIS>>   12:28:55 GPODB.REGIS>>  
  12:29:18 GPODB.REGIS>> DECLARE      
  2  comando VARCHAR2(32767);       
  3  BEGIN      
  4          
  5  comando := 'UPDATE /*+ ROWID  (A) */ TESTE_CHUNCK_B A    
  6  SET NM_TESTE =  ''UPDATE CHUNCK B''     
  7  WHERE MOD(ID_TESTE,2) = 1        
  8  AND rowid  BETWEEN :start_id AND :end_id';     
  9          
  10 DBMS_PARALLEL_EXECUTE.run_task(task_name      => 'GPO_REGIS_B',   
  11                                sql_stmt       => comando,     

  12                                language_flag  =>  DBMS_SQL.NATIVE,  
  13                                parallel_level => 10);         
  14 END;       
  15 /
  PL/SQL procedure successfully completed
  Executed in 76,098 seconds

Pode-se verificar que aumentando o parâmetro parallel_level o ganho foi ainda maior.


12:29:09 GPODB.REGIS>>  
 12:29:24 GPODB.REGIS>> SELECT COUNT(1),  STATUS  
 2  FROM  USER_PARALLEL_EXECUTE_CHUNKS  	
 3  WHERE TASK_NAME =  'GPO_REGIS_B'  
 4  GROUP BY STATUS  
 5  /
 COUNT(1)    STATUS 

 ----------  --------------------   
 
 10  ASSIGNED   
 449  UNASSIGNED   
 208  PROCESSED
 Executed in 0,016 seconds
 12:30:03 GPODB.REGIS>> /
 COUNT(1)     STATUS 
 
 -----------  --------------------  

 10  ASSIGNED  	121 
 UNASSIGNED  	536
 PROCESSED
 Executed in 0,032 seconds
 12:30:15 GPODB.REGIS>> /

 COUNT(1)     STATUS  
 
 -----------  --------------------  

 667  PROCESSED
 Executed in 0,016 seconds
 12:29:59 GPODB.REGIS>> SELECT COUNT(1),  NM_TESTE FROM TESTE_CHUNCK_B   
 2  GROUP BY NM_TESTE    	
 3  /

 COUNT(1)    NM_TESTE 

 ----------  --------------------------------------------------    

 5000000  TESTE INSERT A     2334813  TESTE INSERT B     2665187  UPDATE CHUNCK B

 12:32:13 GPODB.REGIS>>  12:33:26 GPODB.REGIS>/

 COUNT(1)    NM_TESTE

 ----------  --------------------------------------------------    

 5000000  TESTE INSERT A     5000000  UPDATE CHUNCK B
 Executed in 7,613 seconds
 12:34:21 GPODB.REGIS>>   
 12:34:22 GPODB.REGIS>> BEGIN  	
 2  DBMS_PARALLEL_EXECUTE.drop_task('GPO_REGIS_B');
 3  END;  
 4  /
 PL/SQL procedure successfully completed

 Executed in 0,125 seconds

Referências

(Oracle® Database PL/SQL Packages and Types Reference) http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_parallel_ex.htm#ARPLS233 http://www.oracle.com/technetwork/issue-archive/2010/10-may/o30asktom-082672.html

Regis Araujo é Bacharel em Ciência da Computação. DBA Oracle há 07 anos, atuando principalmente no mercado de São Paulo Brasil, especialista em Banco de Dados Oracle desde a versão 8.1.0.7. Trabalhando em ambientes de alta disponibilizada e com as principais ferramentas oracle disponíveis no mercado, como Oracle GuldenGate, Oracle Streams e DataGuard.