Following some analysis between one of my customers and Oracle support has been suggested that allowing the setting OCI_ATTR_CACHE to avoid creating a temporary LOB is much quicker. They would like an odbc.ini option to allow OCI_ATTR_CACHE to be enabled/disabled.
Here is an example supplied by the customer
It show the difference between two stored procedures, one that uses a clob with temp table space write and varchar2 that does not use disc write.
Their test shows that the OCI_ATTR_CACHE enabled procedure was 9 times faster. CREATE OR REPLACE PROCEDURE p ( c clob, n out number ) is begin n := dbms_lob.getLength(c); end; / CREATE OR REPLACE PROCEDURE p_v ( c varchar2, n out number ) is begin n := length(c); end; / declare v_temp clob; v_len number; begin for i in 1..100000 loop v_temp := rpad('.', 32767, '.'); test.p(v_temp, v_len); end loop; end; / -- elapsed 4.5 sec declare v_temp varchar2(32767); v_len number; begin for i in 1..100000 loop v_temp := rpad('.', 32767, '.'); test.p_v(v_temp, v_len); end loop; end; / -- elapsed 0.5 sec