Benchmarking DBMS_OUTPUT.GET_LINE[S] from PL/SQL

Oracle.sql

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

CREATE TABLE results (stmt NUMBER, i INTERVAL DAY TO SECOND);


DECLARE
  v_ts TIMESTAMP WITH TIME ZONE;
  v_repeat CONSTANT NUMBER := 50;
  v_max CONSTANT NUMBER := 100;
  v_array DBMSOUTPUT_LINESARRAY;
  v_string VARCHAR2(100);
  v_i NUMBER;
  
  PROCEDURE m IS BEGIN
    FOR i IN 1 .. v_max LOOP 
      dbms_output.put_line('Message ' || i);
    END LOOP;
  END m;
BEGIN

  dbms_output.enable();

  -- 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
      m();
      
      v_i := v_max;
      dbms_output.get_lines(v_array, v_i);
    END LOOP;
      
    INSERT INTO results VALUES (1, (SYSTIMESTAMP - v_ts));
    v_ts := SYSTIMESTAMP;
      
    FOR i IN 1..v_repeat LOOP
      m();
      
      FOR j IN 1 .. v_max LOOP
        dbms_output.get_line(v_string, v_i);
      END LOOP;
    END LOOP;
      
    INSERT INTO results VALUES (2, (SYSTIMESTAMP - v_ts));
  END LOOP;
END;
/

-- 1	0.0609	0.01218	0.0073	0.0303
-- 2	0.0333	0.00666	0.0063	0.007

SELECT stmt, sum(i), avg(i), min(i), max(i)
FROM (
  SELECT stmt, cast(sysdate + (i * 86400) - sysdate as NUMBER(10, 4)) as i
  FROM results
) r
GROUP BY stmt;

DROP TABLE results;