Processamento em Bulk no Oracle

As instruções DML select, insert, update e delete são as mais utilizadas nas aplicações existentes no mercado, com um uso ainda maior da select do que das outras. O volume de dados processados tem aumentado cada vez mais fazendo com que nossas instruções demandem mais tempo para serem processadas. Isso fica cada vez mais evidente quando geramos um relatório em que será analisado um grande número de linhas ou quando precisamos alterar ou excluir as mesmas. Com o modelo de negócio ficando cada vez mais complicado, essas consultas não podem ser geradas com uma simples instrução SQL, necessitando de algo procedural, como o PL/SQL da Oracle.

Na grande maioria dos casos, códigos em PL/SQL contêm algum tipo de loop para tratar os dados armazenados no banco de dados, seja para apresentação de informações retornadas por um select, ou sua modificação, inserção ou exclusão. Acontece que, ao utilizarmos um código em PL/SQL, como uma procedure para executar instruções SQL em um loop, acontece algo que degrada a performance da execução – o evento chamado de Context Switch.

Este artigo explica como podemos proceder para usar o mínimo possível de Context Switch em nossas aplicações utilizando as instruções BULK COLLECT e o FORALL. Iremos demonstrar com exemplos o uso de tais comandos e qual impacto é gerado na performance. Veremos que com o uso correto poderemos diminuir um tempo de execução de 34 segundos para menos de um segundo! Cerca de 97% mais rápido. Para uma introdução ao tema, recomendamos a leitura do artigo “Otimização de Stored Procedures em PL/SQL” publicado na SQL Magazine (ver seção Links).

Context Switch

O PL/SQL é uma linguagem procedural, parecida com outras linguagens existentes, como Pascal, Ada e Java, já a linguagem SQL não é. Esses dois ambientes são bem diferentes. Então o Oracle possui duas engines, uma que executa o código em SQL e outra que executa o código PL/SQL. Mas, o que acontece se nosso código PL/SQL tiver instruções em SQL? Acontece o famoso Context Switch.

O Context Switch nada mais é do que a troca entre as engines SQL e PL/SQL. Quando a engine do PL/SQL encontra uma instrução SQL, como um select, ele para a execução e passa essa instrução para a engine do SQL. Quando a instrução é finalizada, a engine do SQL retorna para a engine do PL/SQL, que continua a execução (isso está bem ilustrado na Figura 1). Isso não irá trazer grandes problemas se desejamos apenas os dados de algum cliente. Mas, imagine quando temos um loop dentro do nosso código PL/SQL fazendo um select de mais de 10 mil linhas. Irão acontecer 10 mil Context Switches. Isso irá acabar com a performance da nossa aplicação. Imagine uma base com mais de um milhão de linhas. Esse cenário não é difícil de ser encontrado hoje em dia.

Então fica claro que devemos evitar que nossa aplicação faça muitos Context Switches. Mas, e se nossa aplicação em PL/SQL precisar fazer uma instrução DML com um loop em uma base muito grande? É aí que entram as instruções BULK COLLECT e o FORALL.

Figura 1. Context Switch.

Bulk Collec ...