GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

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 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
---------- --------------------------------------------------
  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 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

Espero 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

Share

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado.