Pular para o conteúdo

FORALL e BULK COLLECT: Melhore a performance do seu banco de dados

Forall e Bulk Collect

Agilidade é a palavra do momento e tempo é dinheiro. É neste sentido que os bancos de dados sofrem, pois são sempre as mesmas histórias: o banco tá lento, nada funciona, liga para o DBA.

É certo, também, que grande parte das implementações é testada utilizando uma massa de dados aquém do que de fato será trabalhado no dia a dia. Isto dá uma ideia errada de performance, pois no ambiente de desenvolvimento tudo funciona como uma bala. Já na produção existe uma degradação no tempo de resposta por ter um cenário de dados bem diferente e muito maior.

Tuning SQL não é a resolução de todos os problemas e não vai deixar seu banco gravando e lendo em milissegundos. O assunto é bem mais amplo do que somente isso. Contudo, a utilização de algumas técnicas podem diminuir a dor de cabeça e melhorar a performance de grande parte das implementações.

Neste sentido, abaixo, é apresentado o FORALL combinado com o BULK COLLECT para atualização de uma tabela comparando-o com um For Update utilizando Current OF e o percentual de ganho em desempenho.

Para o teste, primeiramente, vamos criar uma tabela com 50 mil registros e com apenas três colunas. O ID será o ROWNUM e os campos MY_NAME e SALARY serão preenchidos utilizando a package DBMS_RANDOM. Utilize o script abaixo para criar a tabela temp_teste:

CREATE TABLE temp_teste AS 
  (SELECT ROWNUM                            my_id, 
          dbms_random.String('U', 10)       my_name, 
          Round(dbms_random.Value(1, 7000)) salary 
   FROM   dual 
   CONNECT BY LEVEL <= 50000 -- quantidade de registro a serem criados 
  )

Para efetuar a comparação foi criado o bloco anônimo abaixo e utilizado a package DBMS_UTILITY para captar o tempo de atualização dos registros. A ideia do código é aumentar todos os salários em 10%, ou seja, 50 mil linhas serão atualizadas.

SET serveroutput ON 

DECLARE 
    CURSOR v_cursor_one IS 
      SELECT my_id, 
             salary, 
             ROWID idlinha 
      FROM   temp_teste; 

    TYPE trow_type 
      IS TABLE OF v_cursor_one%ROWTYPE INDEX BY PLS_INTEGER; 

    CURSOR v_cursor_two IS 
      SELECT my_id, 
             salary 
      FROM   temp_teste 
      FOR UPDATE; 

    trow         TROW_TYPE; 
    v_time_i_one NUMBER; 
    v_time_i_two NUMBER; 
    v_time_f_one NUMBER; 
    v_time_f_two NUMBER; 

BEGIN 
    ---- ATUALIZAÇÃO COM FORALL + BULK COLLECT 
    v_time_i_one := dbms_utility.Get_time(); 

    OPEN v_cursor_one; 
    LOOP 
        FETCH v_cursor_one bulk collect INTO trow limit 5000; 
        exit WHEN trow.count = 0; 

        forall nx IN 1 .. trow.count 
          UPDATE temp_teste 
          SET    salary = salary * 1.1 
          WHERE  ROWID = Trow(nx).idlinha; 

        COMMIT; 

    END LOOP; 

    v_time_f_one := ( dbms_utility.get_time - v_time_i_one ) / 100; 

    dbms_output.Put_line('Atualizacao com Forall + Bulk Collect: ' 
                         || v_time_f_one 
                         || ' segundos.'); 

    CLOSE v_cursor_one; 

    ---- ATUALIZAÇÃO COM CURRENT OF 

    v_time_i_two := dbms_utility.Get_time(); 

    FOR v_crsr IN v_cursor_two LOOP 
        UPDATE temp_teste 
        SET    salary = salary * 1.1 
        WHERE  CURRENT OF v_cursor_two; 

    END LOOP; 

    COMMIT; 

    v_time_f_two := ( dbms_utility.get_time - v_time_i_two ) / 100; 

    dbms_output.Put_line('Atualizacao com Current OF: ' 
                         || v_time_f_two 
                         || ' segundos'); 

    dbms_output.Put_line('Ou seja, ' 
                         || Round(( v_time_f_two * 100 ) / v_time_f_one, 2) 
                         || '% mais rapido.'); 

END;

Veja que com FORALL + BULK COLLECT o ganho de performance chega a 389% em comparação ao FOR UPDATE.

ngL01 gSdBVLDU2 CHPJQvGX6y4V3pPbgs2YoU00m6WL2P JK0D 2RsvK1IewY5VyeYjqW 6ifV9GF7GZJtwbm9F 8KBHg625RYXFxdgipeYMKy29KdPyyoSxm2LiwC1wBq

Para quem não está familiarizado, o CURRENT OF traz para chave de busca do registro o ROWID, ocasionando uma busca rápida das informações.

Alguns podem questionar que com BULK COLLECT o commit está sendo emitido a cada 5000 registros enquanto com o FOR UPDATE foi feito para os 50 Mil registros de uma vez. Pode-se alterar a lógica para o FOR UPDATE e emitir o commit para a mesma quantidade de registro, mas haveria mais trocas de contextos e, talvez, venha, até mesmo, piorar o tempo de gravação.

Ainda, utilize-se do BULK COLLECT com parcimônia, haja vista que pode representar um alto consumo da memória da PGA, principalmente se forem utilizado diversos processos paralelos com esta mesma prática e assim prejudicar a desempenho de todo o banco de dados, criando um problema ainda maior. Para isso é muito importante acompanhar e alinhar junto ao DBA um limite que não prejudique o desempenho e determinar este número através do delimitador LIMIT, que no exemplo acima está como 5000;

Referências

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 5 / 5. Contagem de votos: 27

Sem votos ! Seja o primeiro a classificar !

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress