Created
December 18, 2017 08:29
-
-
Save lukaseder/00746f1ab57bfb8af8bc1ba6609dfff9 to your computer and use it in GitHub Desktop.
Benchmarking DBMS_OUTPUT.GET_LINE[S] from PL/SQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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