Row-by-row processing in PL/SQL

A PL/SQL loop that executes one SQL per row pays, on each pass, the cost of a call. With many rows, this small cost becomes most of the time; the same work done as a set usually takes a fraction of it.

Symptoms

In the Diagnosis, one of these findings (LINHA_A_LINHA) appears, with the statement number and the call count:

• Statement #n executed n times, for example Statement #4 executed 25,000 times. The text says it is recursive SQL executed once per row, typical of a PL/SQL loop, and suggests set-based processing (a single SQL or FORALL).
• Statement #n read row by row: n FETCH calls. The text says how many rows each FETCH returned, typical of a cursor loop in PL/SQL, and suggests BULK COLLECT with LIMIT.

The finding applies only to recursive SQL (depth 1 or more), the SQL that runs inside PL/SQL, triggers and functions, and it appears in two cases. In the first, the statement runs a huge number of times, one row per execution: a DML that changes one row at a time, or a SELECT executed once for each row of the loop. In the second, the cursor is traversed with a huge number of FETCH calls of one row each. Oracle's own dynamic sampling is left out: it runs on each hard parse of the parent statement, and the cause is the hard parse, not a loop.

The severity is always medium, and the percentage next to it is the inclusive time of the statement over the total time. The View statement #n link opens the detail.

Dynamic SQL built with literals inside the loop shows up as a shape, with the N variants badge, and the executions of the variants add up in the same statement. In that case, this finding can appear together with the literals instead of binds finding: the loop and the literals are two problems, and the two fixes add up.

Cause

The PL/SQL code goes through the rows in a loop and, for each one, executes a SQL or fetches the next row of the cursor. Each pass is a recursive call with its own EXEC or FETCH, CPU and read cost. It also happens with a row trigger that executes SQL and with a PL/SQL function called by a SQL for each row.

Resolution

1. Open View statement #n and check, in the Calls table, the Count of EXEC and FETCH and the Rows. In the detail of the PL/SQL block that calls the statement, Recursive SQL executed by this statement shows the children.
2. Whenever possible, replace the loop with a single SQL that does the work as a set:

   -- row by row: one INSERT for each row
   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;
   /
   
   -- set-based: a single INSERT
   insert into exemplo_destino (id, valor)
   select id, valor from exemplo_origem;

3. When the logic has to stay in PL/SQL, read in batches with BULK COLLECT and LIMIT and write in batches with 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. If the repeated SQL comes from a function called inside another SQL, replace the function with a join in the outer SQL.
5. If it comes from a row trigger, move the logic to the statement that fires the trigger, done as a set.

How to confirm

Generate a new trace of the same operation after the change. The statement of the finding should have few executions, or give way to a single SQL, and in the Statement profile of the PL/SQL block that called it, the Recursive SQL (child statements) row should drop. The finding disappears when the statement is no longer called once per row and starts handling several rows per call.

In the report

In the sample report, statement #2 is recursive, with 1 EXEC and 1 FETCH, without a finding. The reports from real traces have this finding: see the Diagnosis of case 1 and that of case 2. The links below open the sections of the example:

• Diagnosis: the finding and the View statement #n link.
• Detail of statement #2: Depth, which tells whether it is recursive SQL, and the Calls table.
• SQL statements by contribution: Depth, Exec, Fetch and Rows. The contribution of recursive SQL goes to the level-0 statement that called it: compare by Inclusive (s).
• In the detail of the PL/SQL block that calls the statement: Recursive SQL executed by this statement and the Recursive SQL (child statements) row of the Statement profile.

Recursive SQL and the child statements component have their own pages: classes and recursive SQL and statement detail.