Processamento linha a linha em PL/SQL

Laço em PL/SQL que executa um SQL por linha paga, a cada volta, o custo de uma chamada. Com muitas linhas, esse custo pequeno vira a maior parte do tempo; o mesmo trabalho feito em conjunto costuma levar uma fração dele.

Sintomas

No Diagnóstico, aparece um destes achados (LINHA_A_LINHA), com o número do comando e a contagem de chamadas:

• Comando #n executado n vezes, por exemplo Comando #4 executado 25.000 vezes. O texto diz que é SQL recursivo executado uma vez por linha, típico de laço em PL/SQL, e sugere processamento em conjunto (um único SQL ou FORALL).
• Comando #n lido linha a linha: n FETCH. O texto diz quantas linhas cada FETCH trouxe, típico de laço de cursor em PL/SQL, e sugere BULK COLLECT com LIMIT.

O achado vale só para SQL recursivo (profundidade 1 ou mais), o que roda dentro de PL/SQL, triggers e funções, e aparece em dois casos. No primeiro, o comando executa muitíssimas vezes, uma linha por execução: um DML que altera uma linha por vez, ou um SELECT executado uma vez para cada linha do laço. No segundo, o cursor é percorrido com muitíssimos FETCH de uma linha cada. A amostragem dinâmica do próprio Oracle fica de fora: ela roda a cada hard parse do comando pai, e a causa é o hard parse, não um laço.

A severidade é sempre média, e o percentual ao lado é o tempo inclusivo do comando sobre o tempo total. O link Ver comando #n abre o detalhe.

Um SQL dinâmico montado com literais dentro do laço aparece como uma forma, com o selo N variantes, e as execuções das variantes somam no mesmo comando. Nesse caso, este achado pode aparecer junto com o de literais em vez de bind: o laço e os literais são dois problemas, e as duas correções se somam.

Causa

O código PL/SQL percorre as linhas num laço e, para cada uma, executa um SQL ou busca a próxima linha do cursor. Cada volta é uma chamada recursiva com o próprio custo de EXEC ou FETCH, de CPU e de leitura. Também acontece com trigger de linha que executa SQL e com função PL/SQL chamada por um SQL para cada linha.

Resolução

1. Abra Ver comando #n e confira, na tabela Chamadas, a Quantidade de EXEC e FETCH e as Linhas. No detalhe do bloco PL/SQL que chama o comando, SQL recursivo executado por este comando mostra os filhos.
2. Sempre que der, troque o laço por um único SQL que faça o trabalho em conjunto:

   -- linha a linha: um INSERT para cada linha
   begin
     for r in (select id, valor from exemplo_origem) loop
       insert into exemplo_destino (id, valor) values (r.id, r.valor);
     end loop;
   end;
   /
   
   -- em conjunto: um INSERT só
   insert into exemplo_destino (id, valor)
   select id, valor from exemplo_origem;

3. Quando a lógica precisa ficar em PL/SQL, leia em lotes com BULK COLLECT e LIMIT e grave em lotes com FORALL:

   declare
     cursor c is select id, valor from exemplo_origem;
     type t_linhas is table of c%rowtype;
     v_linhas t_linhas;
   begin
     open c;
     loop
       fetch c bulk collect into v_linhas limit 500;
       exit when v_linhas.count = 0;
       forall i in 1 .. v_linhas.count
         insert into exemplo_destino values v_linhas(i);
     end loop;
     close c;
   end;
   /

4. Se o SQL repetido vem de uma função chamada dentro de outro SQL, troque a função por uma junção no SQL de fora.
5. Se vem de um trigger de linha, leve a lógica para o comando que dispara o trigger, feita em conjunto.

Como confirmar

Gere um trace novo da mesma operação depois da mudança. O comando do achado deve ter poucas execuções, ou dar lugar a um SQL só, e no Perfil do comando do bloco PL/SQL que o chamava a linha SQL recursivo (comandos filhos) deve cair. O achado some quando o comando deixa de ser chamado uma vez por linha e passa a tratar várias linhas por chamada.

No relatório

No relatório de exemplo, o comando #2 é recursivo, com 1 EXEC e 1 FETCH, sem achado. Os relatórios de traces reais têm este achado: veja o Diagnóstico do caso 1 e o do caso 2. Os links abaixo abrem as seções do exemplo:

• Diagnóstico: o achado e o link Ver comando #n.
• Detalhe do comando #2: Profundidade, que diz se é SQL recursivo, e a tabela Chamadas.
• Comandos SQL por contribuição: Prof., Exec, Fetch e Linhas. A contribuição do SQL recursivo fica com o comando de nível 0 que o chamou: compare pelo Inclusivo (s).
• No detalhe do bloco PL/SQL que chama o comando: SQL recursivo executado por este comando e a linha SQL recursivo (comandos filhos) do Perfil do comando.

O SQL recursivo e o componente dos comandos filhos têm página própria: classes e SQL recursivo e detalhe do comando.