Thank you for the feedback.
I did some quite extensive testing on a variety of development and production system before migrating code to use the string concatenation operator for CLOB values with a test script that can be boiled down to the following code.
In general the results did not show significant differences and typically the differences are around +/- 5%.
CREATE TABLE temp_clob_perf
(
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
tx VARCHAR2(1000),
el NUMBER
);
TRUNCATE TABLE temp_clob_perf;
DECLARE
t VARCHAR2(32767);
c CLOB;
s NUMBER;
e NUMBER;
l NUMBER;
a dbms_utility.number_array;
BEGIN
a(1) := 1;
a(2) := 10;
a(3) := 100;
a(4) := 1000;
a(5) := 10000;
-- process the different chunk sizes to add
FOR idx IN 1 .. a.COUNT LOOP
l := 100000 / a(idx);
t := RPAD('*', a(idx), '*');
-- dbms_lob.writeappend
dbms_lob.createtemporary(lob_loc=>c, cache=>TRUE);
s := dbms_utility.get_time();
-- test 10 times and use average
FOR i IN 1 .. 10 LOOP
-- iterate l times to reach final size
FOR i IN 1 .. l LOOP
dbms_lob.writeappend(lob_loc=>c, amount=>LENGTH(t), buffer=>t);
END LOOP;
END LOOP;
e := dbms_utility.get_time();
INSERT INTO temp_clob_perf (tx, el) VALUES ('dbms_lob.writeappend: '||l||' concatenations with each '||LENGTH(t)||' chars', (e - s) / 10);
dbms_lob.freetemporary(lob_loc=>c);
-- string concatenation
dbms_lob.createtemporary(lob_loc=>c, cache=>TRUE);
s := dbms_utility.get_time();
-- test 10 times and use average
FOR i IN 1 .. 10 LOOP
-- iterate l times to reach final size
FOR i IN 1 .. l LOOP
c := c || t;
END LOOP;
END LOOP;
e := dbms_utility.get_time();
INSERT INTO temp_clob_perf (tx, el) VALUES ('string concatenation: '||l||' concatenations with each '||LENGTH(t)||' chars', (e - s) / 10);
dbms_lob.freetemporary(lob_loc=>c);
END LOOP;
END;
/
SELECT * FROM temp_clob_perf ORDER BY ID;