PL/SQL row-by-row update vs bulk update

plsql-vs-bulk-update.sql

-- Copyright Data Geekery GmbH
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-- This version displays relative execution times (fastest execution = 1)
-- According to our understanding of Oracle licensing, such benchmark results may be published
-- as they cannot be compared to other databases and do not provide absolute time values
SET SERVEROUTPUT ON SIZE 10000000

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

CREATE TABLE results (
  run     NUMBER(2),
  stmt    NUMBER(2),
  elapsed NUMBER
);

DECLARE
  v_ts TIMESTAMP WITH TIME ZONE;
  v_repeat CONSTANT NUMBER := 10000;
  v_updated PLS_INTEGER := 0;
  v_consume PLS_INTEGER := 0;
BEGIN

  -- Repeat the whole benchmark several times to avoid warmup penalty
  FOR r IN 1..5 LOOP
    v_ts := SYSTIMESTAMP;
      
    FOR i IN 1..v_repeat LOOP
      DECLARE
        v_text VARCHAR2(2000);
      BEGIN
        FOR r IN (
          SELECT * FROM t WHERE category = 1
        ) LOOP
          v_updated := v_updated + 1;
            
          IF v_text IS NULL THEN
            v_text := r.text;
          ELSE
            v_text := v_text || ', ' || r.text;
          END IF;
           
          IF r.counter IS NULL THEN
            UPDATE t SET counter = 1 WHERE id = r.id;
          ELSE
            UPDATE t SET counter = counter + 1 WHERE id = r.id;
          END IF;
          
          v_consume := v_consume + LENGTH(v_text);
        END LOOP;
        COMMIT;
      END;

    END LOOP;
  
    INSERT INTO results VALUES (r, 1, SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);
    v_ts := SYSTIMESTAMP;
      
    FOR i IN 1..v_repeat LOOP
      DECLARE
        v_text VARCHAR2(2000);
        v_updated PLS_INTEGER := 0;
      BEGIN
        UPDATE t
        SET counter = nvl(counter, 0) + 1
        WHERE category = 1
        RETURNING
          listagg (text, ', ') WITHIN GROUP (ORDER BY text),
          count(*)
        INTO
          v_text,
          v_updated;
            
        v_consume := v_consume + LENGTH(v_text);
        COMMIT;
      END;
    END LOOP;
      
    INSERT INTO results VALUES (r, 2, SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);
  END LOOP;
  
  dbms_output.put_line(v_updated);
  dbms_output.put_line(v_consume);
  
  FOR rec IN (
    SELECT 
      run, stmt, 
      CAST(elapsed / MIN(elapsed) OVER() AS NUMBER(10, 5)) ratio,
      CAST(AVG(elapsed) OVER (PARTITION BY stmt) / MIN(elapsed) OVER() AS NUMBER(10, 5)) avg_ratio
    FROM results
    ORDER BY run, stmt
  )
  LOOP
    dbms_output.put_line('Run ' || rec.run || 
      ', Statement ' || rec.stmt || 
      ' : ' || rec.ratio || ' (avg : ' || rec.avg_ratio || ')');
  END LOOP;
  
  dbms_output.put_line('');
  dbms_output.put_line('Copyright Data Geekery GmbH');
  dbms_output.put_line('https://www.jooq.org/benchmark');
END;
/

DROP TABLE results;