Instantly share code, notes, and snippets.

Embed
What would you like to do?
Benchmarking DBMS_OUTPUT.GET_LINE[S] from PL/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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment