DBMS_PARALLEL_EXECUTE – BY ROWID
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@GPODB
Connected 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 alteredPara 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 secondsCom 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 secondsNeste 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 secondsNeste 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 secondsAgora 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 secondsApó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 secondsO 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 UNASSIGNEDPode-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 secondsPode-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 secondsPode-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
---------- --------------------------------------------------
3718204 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 CHUNCKApó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 secondsRealizei 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 secondsPode-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 secondsEspero que tenham gostado deste artigo, onde procuro demonstrar as vantagens em conhecer as features oferecidas pelas versões do Oracle. Fui apresentado a esta package quando li em 2010 um artigo do Tom Kyte na regista Oracle Magazine.
Esta features tem sido extremamente útil em diversas rotinas de alterações de dados em massa que preciso realizar.
Desejo a todos um Feliz Natal e um ano de 2014 repleto de conquistas e realizações!!
Que nossos bancos de dados apresentem menos Ora-0600 e que nosso ano seja repleto de “Successfully completed”.
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