Procesamiento fila a fila en PL/SQL

Un bucle en PL/SQL que ejecuta un SQL por fila paga, en cada vuelta, el costo de una llamada. Con muchas filas, ese costo pequeño se vuelve la mayor parte del tiempo; el mismo trabajo hecho en conjunto suele llevar una fracción de él.

Síntomas

En el Diagnóstico aparece uno de estos hallazgos (LINHA_A_LINHA), con el número de la sentencia y el recuento de llamadas:

• Sentencia #n ejecutada n veces, por ejemplo Sentencia #4 ejecutada 25.000 veces. El texto dice que es SQL recursivo ejecutado una vez por fila, típico de un bucle en PL/SQL, y sugiere procesamiento en conjunto (un único SQL o FORALL).
• Sentencia #n leída fila a fila: n FETCH. El texto dice cuántas filas trajo cada FETCH, típico de un bucle de cursor en PL/SQL, y sugiere BULK COLLECT con LIMIT.

El hallazgo vale solo para SQL recursivo (profundidad 1 o más), el que se ejecuta dentro de PL/SQL, triggers y funciones, y aparece en dos casos. En el primero, la sentencia se ejecuta muchísimas veces, una fila por ejecución: un DML que modifica una fila a la vez, o un SELECT ejecutado una vez por cada fila del bucle. En el segundo, el cursor se recorre con muchísimos FETCH de una fila cada uno. El muestreo dinámico del propio Oracle queda fuera: se ejecuta en cada hard parse de la sentencia padre, y la causa es el hard parse, no un bucle.

La gravedad es siempre media, y el porcentaje al lado es el tiempo inclusivo de la sentencia sobre el tiempo total. El enlace Ver sentencia #n abre el detalle.

Un SQL dinámico armado con literales dentro del bucle aparece como una forma, con la etiqueta N variantes, y las ejecuciones de las variantes se suman en la misma sentencia. En ese caso, este hallazgo puede aparecer junto con el de literales en lugar de bind: el bucle y los literales son dos problemas, y las dos correcciones se suman.

Causa

El código PL/SQL recorre las filas en un bucle y, para cada una, ejecuta un SQL o busca la siguiente fila del cursor. Cada vuelta es una llamada recursiva con su propio costo de EXEC o FETCH, de CPU y de lectura. También pasa con un trigger de fila que ejecuta SQL y con una función PL/SQL llamada por un SQL para cada fila.

Solución

1. Abra Ver sentencia #n y revise, en la tabla Llamadas, la Cantidad de EXEC y FETCH y las Filas. En el detalle del bloque PL/SQL que llama a la sentencia, SQL recursivo ejecutado por esta sentencia muestra las hijas.
2. Siempre que se pueda, cambie el bucle por un único SQL que haga el trabajo en conjunto:

   -- fila a fila: un INSERT por cada fila
   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;
   /
   
   -- en conjunto: un solo INSERT
   insert into exemplo_destino (id, valor)
   select id, valor from exemplo_origem;

3. Cuando la lógica tiene que quedar en PL/SQL, lea en lotes con BULK COLLECT y LIMIT y grabe en lotes con 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. Si el SQL repetido viene de una función llamada dentro de otro SQL, cambie la función por un join en el SQL de afuera.
5. Si viene de un trigger de fila, lleve la lógica a la sentencia que dispara el trigger, hecha en conjunto.

Cómo confirmar

Genere un trace nuevo de la misma operación después del cambio. La sentencia del hallazgo debe tener pocas ejecuciones, o dar lugar a un solo SQL, y en el Perfil de la sentencia del bloque PL/SQL que la llamaba, la fila SQL recursivo (sentencias hijas) debe bajar. El hallazgo desaparece cuando la sentencia deja de llamarse una vez por fila y pasa a tratar varias filas por llamada.

En el informe

En el informe de ejemplo, la sentencia #2 es recursiva, con 1 EXEC y 1 FETCH, sin hallazgo. Los informes de traces reales tienen este hallazgo: vea el Diagnóstico del caso 1 y el del caso 2. Los enlaces de abajo abren las secciones del ejemplo:

• Diagnóstico: el hallazgo y el enlace Ver sentencia #n.
• Detalle de la sentencia #2: Profundidad, que dice si es SQL recursivo, y la tabla Llamadas.
• Sentencias SQL por contribución: Prof., Exec, Fetch y Filas. La contribución del SQL recursivo queda con la sentencia de nivel 0 que lo llamó: compare por el Inclusivo (s).
• En el detalle del bloque PL/SQL que llama a la sentencia: SQL recursivo ejecutado por esta sentencia y la fila SQL recursivo (sentencias hijas) del Perfil de la sentencia.

El SQL recursivo y el componente de las sentencias hijas tienen página propia: clases y SQL recursivo y detalle de la sentencia.