Os comandos BULK COLLECT e FORALL são essenciais para o processamento em lote no Oracle, permitindo a manipulação eficiente de grandes conjuntos de dados. Eles minimizam a troca de contexto entre o SQL e o PL/SQL, resultando em melhorias significativas de desempenho.
BULK COLLECT: Permite carregar múltiplas linhas em coleções PL/SQL em uma única operação.FORALL: Permite realizar operações DML em lote utilizando coleções PL/SQL.
Utilização do LIMIT com BULK COLLECT e FORALL
Ao utilizar BULK COLLECT, é essencial considerar o uso do parâmetro LIMIT para controlar o número de registros processados em cada operação. Isso ajuda a evitar o consumo excessivo de memória e melhora a eficiência do processamento em lote.
Exemplo Simples:
SET SERVEROUTPUT ON;
DECLARE
CURSOR c1 IS
SELECT rowid
FROM exemplo_tabela
WHERE id <= 10000;
TYPE t_rowid_tab IS TABLE OF ROWID INDEX BY PLS_INTEGER;
v_rowid_tab t_rowid_tab;
v_inicio NUMBER;
v_total_deletados INTEGER := 0;
BEGIN
v_inicio := dbms_utility.get_time;
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO v_rowid_tab LIMIT 1000;
EXIT WHEN v_rowid_tab.COUNT = 0;
FORALL i IN v_rowid_tab.FIRST .. v_rowid_tab.LAST
DELETE FROM exemplo_tabela WHERE rowid = v_rowid_tab(i);
v_total_deletados := v_total_deletados + v_rowid_tab.COUNT;
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('Total de Linhas Deletadas: ' || v_total_deletados);
DBMS_OUTPUT.PUT_LINE('Tempo de Execução: ' || ROUND((dbms_utility.get_time - v_inicio) / 100, 2) || ' segundos');
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
Explicação:
- Cursor c1: Define um cursor que seleciona os
rowiddas linhas que queremos deletar. - t_rowid_tab: Declara um tipo de tabela PL/SQL para armazenar os
ROWIDs. - v_rowid_tab: Declara uma variável do tipo
t_rowid_tab. - v_inicio: Usado para armazenar o tempo de início da operação.
- v_total_deletados: Contador para o total de linhas deletadas.
- LIMIT 1000: Carrega até 1000 linhas por vez em cada operação de fetch para evitar sobrecarga de memória.
- FORALL: Utiliza a coleção de
ROWIDs para deletar as linhas em lote de forma eficiente.
Este bloco de código ilustra como usar BULK COLLECT e FORALL com o parâmetro LIMIT para deletar dados de uma tabela de maneira eficiente, controlando a quantidade de dados processados em cada operação.
Gerando Dados para Realizar Teste de Desepenho
Criaremos a tabela CASE_0003_R_SCR com base na dba_objects.
CREATE TABLE CASE_0003_R_SCR AS
SELECT
rownum AS ID_X,
d.object_id,
d.object_name,
d.owner,
d.object_type,
d.status,
d.created
FROM dba_objects d
CROSS JOIN (SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10)
WHERE rownum <= 1500000;
ALTER TABLE CASE_0003_R_SCR ADD CONSTRAINT pk_CASE_0003_R_SCR PRIMARY KEY (ID_X);
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'CASE_0003_R_SCR');
Executar Bloco PL/SQL Usando BULK COLLECT e FORALL para DELETE
ALTER TABLE CASE_0003_R_SCR MOVE;
ALTER INDEX pk_CASE_0003_R_SCR REBUILD;
SET SERVEROUTPUT ON;
DECLARE
cursor c1 is
select x.rowid as rw
from CASE_0003_R_SCR x
where OBJECT_TYPE in ('JAVA CLASS', 'SYNONYM', 'VIEW', 'JAVA RESOURCE', 'JAVA DATA', 'TABLE', 'INDEX', 'PACKAGE', 'PACKAGE BODY');
type t_rowid is table of rowid index by pls_integer;
row_t_rowid t_rowid;
v_inicio NUMBER;
v_conta INTEGER := 0;
BEGIN
v_inicio := dbms_utility.get_time;
open c1;
loop
fetch c1 bulk collect
into row_t_rowid limit 1000;
exit when row_t_rowid.count = 0;
v_conta := v_conta + row_t_rowid.count;
forall i in row_t_rowid.first .. row_t_rowid.last
delete CASE_0003_R_SCR where rowid = row_t_rowid(i);
--
ROLLBACK;
end loop;
close c1;
--/
ROLLBACK;
--/
dbms_output.put_line('=======================================================================');
dbms_output.put_line('TOTAL DE LINHAS DE DELETE : ' || v_conta);
dbms_output.put_line('TEMPO COM BULK COLLECT/FORALL: ' || trim(TO_CHAR(ROUND((dbms_utility.get_time - v_inicio) / 100, 2), '999900.99') || ' Segundos'));
dbms_output.put_line('=======================================================================');
--/
exception
when others then
raise_application_error(-20010,'Backtrace: ' ||dbms_utility.format_error_backtrace ||' Sqlerrm: ' || substr(sqlerrm, 1, 300));
end;
/
Executar Bloco PL/SQL sem Usar BULK COLLECT e FORALL
ALTER TABLE CASE_0003_R_SCR MOVE;
ALTER INDEX pk_CASE_0003_R_SCR REBUILD;
SET SERVEROUTPUT ON;
DECLARE
cursor c1 is
select x.rowid as rw
from CASE_0003_R_SCR x
where OBJECT_TYPE in ('JAVA CLASS', 'SYNONYM', 'VIEW', 'JAVA RESOURCE', 'JAVA DATA', 'TABLE', 'INDEX', 'PACKAGE', 'PACKAGE BODY');
v_del c1%ROWTYPE;
v_inicio NUMBER;
v_conta INTEGER := 0;
v_save INTEGER := 0;
BEGIN
v_inicio := dbms_utility.get_time;
OPEN c1;
LOOP
FETCH c1
INTO v_del;
EXIT WHEN c1%NOTFOUND;
delete CASE_0003_R_SCR where rowid = v_del.rw;
v_conta := v_conta +1;
v_save := v_save +1;
if v_save >= 1000
then
ROLLBACK;
v_save := 0;
end if;
end loop;
close c1;
ROLLBACK;
--/
dbms_output.put_line('=============================================================================================');
dbms_output.put_line('TOTAL DE LINHAS DE DELETE : ' || v_conta);
dbms_output.put_line('TEMPO SEM BULK COLLECT/FORALL: ' || trim(TO_CHAR(ROUND((dbms_utility.get_time - v_inicio) / 100, 2), '999900.99') || ' Segundos'));
dbms_output.put_line('=============================================================================================');
--/
exception
when others then
raise_application_error(-20010,'Backtrace: ' ||dbms_utility.format_error_backtrace ||' Sqlerrm: ' || substr(sqlerrm, 1, 300));
end;
/
Comparação de Desempenho
Podemos ver a diferença significativa no tempo de execução entre os dois métodos. Usando BULK COLLECT e FORALL, conseguimos deletar 685920 linhas em 72,46 segundos. No método tradicional, o mesmo número de linhas levou 527,98 segundos para ser deletado. Isso demonstra a eficácia do processamento em lote para operações de grande volume.
Melhora em Percentual:
Melhora de Desempenho: ((527.98 - 72.46) / 527.98) * 100 = 86.28%A utilização de BULK COLLECT e FORALL proporcionou uma melhora de desempenho de aproximadamente 86,28%.
Vantagens do Processamento em Lote com BULK COLLECT e FORALL
- Redução de Trocas de Contexto Minimizando a troca de contexto entre SQL e PL/SQL, o desempenho é significativamente melhorado. Cada troca de contexto entre o PL/SQL e o SQL pode adicionar uma sobrecarga de tempo. Com
BULK COLLECTeFORALL, o número dessas trocas é reduzido, tornando a operação mais eficiente. - Manipulação de Grandes Conjuntos de Dados
BULK COLLECTeFORALLsão ideais para operações que envolvem grandes volumes de dados. Quando precisamos realizar operações DML em milhares ou milhões de linhas, essas técnicas permitem que a operação seja feita de maneira muito mais rápida do que se fosse feita linha por linha. - Código Mais Limpo e Manutenível O uso de coleções e operações em lote pode resultar em um código mais organizado e mais fácil de manter. Em vez de ter loops complexos e numerosas operações DML individuais, podemos simplificar a lógica com
BULK COLLECTeFORALL. - Melhoria na Performance Geral Como vimos nos resultados de desempenho, a utilização dessas técnicas pode resultar em melhorias significativas de tempo. Essa melhoria de performance pode liberar recursos do sistema, permitindo que outras operações sejam executadas de maneira mais eficiente.
Script de Teste Disponível no GitHub
Para que você possa testar essa solução em seu próprio ambiente, disponibilizei um script no GitHub que gera uma tabela de teste chamada CASE_0003_R_SCR e executa o passo a passo da solução. Após a execução do teste, a tabela será automaticamente excluída. Você pode acessar o script aqui.
O uso de BULK COLLECT e FORALL no Oracle é uma prática essencial para qualquer DBA ou desenvolvedor que deseja otimizar a performance de operações de banco de dados que envolvem grandes volumes de dados. Essas técnicas reduzem significativamente o tempo de execução ao minimizar as trocas de contexto entre SQL e PL/SQL, permitindo um processamento em lote eficiente.
🚬🍺 #GuinaNãoTinhaDó #SQLTuning #DBAProblematico #CaceteDeAgulha #EngenheiroDeObraPronta #AcacioÉMeuAmigo #AvidaÉLoka #OraclePerformance #OracleDB #DBATips #Mais1PraHistória #VidaLoka 🍺🚬
