Oracle Collections (Bulk)

By Oradeep: José Eduardo Fiamengui Júnior

O que é ?
Um tipo de cursor para retorno de dados. Com bulk collect, o PL/SQL engine conversa com o SQL engine para retornar muitas linhas a cada interação, reduzindo o número de interações e normalmente o tempo de resposta.
Até aqui encontramos em qualquer site, mas vamos ao como é processado isso tudo por dentro do Oracle:

Temos 2 engines diferentes dentro da memória do banco de dados para processamento dos SQLs (comandos).

Engine1: Bloco PL/SQL (Ifs, declares, whiles, var, etc…)

Engine2: Comando SQL (select, update, delete, insert), para o processamento de cada um dos comandos SQLs contidos no bloco PL/SQL é necessário uma interação entre os engines (vai SQL e volta DADO) e a partir daí todo o processamento do comando SQL (… PARSE, BIND, EXECUTE, FETCH), um maior detalhamento dessas fases foi feito no artigo STATISTICS.

Process: Convencional BINDS

 

Processamento(linha-a-linha)

Process: BULK COLLECT BINDS

Processamento(Bulk Collect)

Bulk Binds Collect fazem um código PL/SQL retorne muitas linhas de um cursor em apenas uma chamada ao invés de uma linha por vez. Esse tipo de código normalmente reduz o consumo de CPU e executa mais rapidamente, pois reduz leituras lógicas e tempo de processamento.

Bulks Collect utilizam a PGA para armazenar seus dados, e a PGA é alocada normalmente por sessão (Conexão Dedicada). Vários bulks executando ao mesmo podem ocasionar problemas de alto consumo de memória, por isso a opção LIMIT (limitando as linhas retornadas) DEVE ser utilizada pelos desenvolvedores.

Partindo para a prática, criei 2 cenários para teste:

a) Implementação de Inserção via Cursor Simples:

Implementação-CursorSimples

 

b) Implementação de Bulk Bind Collect:

Implementação-BulkCollect

Análise Comparativa entre os 2 processos: Trace

Utilizei para ambos os casos:
alter session set events ‘10046 trace name context forever, level 8’ ;
tkprof .trc .txt sys=no sort=fchela explain=teste/teste

a) Implementação (Cursor Simples)

Trace-CursorSimples

b) Implementação (Bulk Collect)

Trace-BulkCollect

 


Houve uma pequena variação na quantidade de linhas (9 linhas), pois as implementações foram feitas em dias diferentes e acredito que já tinha criado outros objetos em meu database. Ainda assim a conclusão que chegamos é bem clara: o tempo de resposta para essa quantidade de registros caí em aproximadamente 85%.
Uma diferença considerável e que aumentará, conforme o aumento na quantidade de registros processados.
Agora que entendemos um pouco mais sobre Collections vale lembrar que é possível utilizar esse recursos também com operações de DELETE e UPDATE.

Grande abraço a todos!, e como esse Post deve fechar o ano de 2011:

Gostaria de desejar um *** Feliz Natal e um Ano Novo repleto de realizações ***

Sobre oradeep

José Eduardo Fiamengui Júnior Graduação: Tecnologia em Informática pela Universidade Estadual de Campinas (Unicamp) Pós-Graduação: Administração em Banco de Dados Oracle pelo Instituto Brasileiro de Tecnologia Avançada (IBTA) Mba em Gestão Estratégica em TI pela FGV OCE Certified ITIL Certified Empresa Atual: Dba Oracle e Performance Specialist na Ccee Empresa Atual: Instrutor Oracle IBTA
Esta entrada foi publicada em Oracle. ligação permanente.

2 respostas a Oracle Collections (Bulk)

  1. Fabricio Silveira diz:

    José Eduardo, parabens pela explicação simples e objetiva do bulk collect. Ao final vc diz: “Houve uma pequena variação na quantidade de linhas (9 linhas), pois as implementações foram feitas em dias diferentes e acredito que já tinha criado outros objetos em meu database”
    Substitua o codigo
    exit when c1%notfound
    Por
    exit when l_data.count = 0

    Att, Fabricio Silveira

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s