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                                                  

Thanks

Andy Ford