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:

  1. Dividindo logicamente a tabela em pedaços/blocos menores que a tabela original
  2. Executando o comando solicitado 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:

  1. Update com o commit sendo realizado apenas ao final.
  2. 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 coletei 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 GoldenGate, Oracle Streams e DataGuard.