SQL & PL/SQL

Hi,

we are facing serious performance issues with clobs, the fetching performance is really bad.

I have tested this with 10.2.0.5 and 11.2.0.3 - could not see any differences or improvements so far.


Below is a simple testcase - are there any / simple ways to speed this up?

--testcase; I used sqlplus on 11.2.0.3. on linux with al32utf8

--create table
CREATE TABLE clobperf (
id NUMBER,
col_vc2 VARCHAR2(1000),
col_clob CLOB
);

--fill in some data
DECLARE i NUMBER :=1;
BEGIN
WHILE (i <= 10000) loop
INSERT INTO clobperf (id,col_vc2,col_clob) VALUES (i,'this is a simple text','this is a simple text');
i:=i+1;
END LOOP;
commit;
END;
/

--select all data
select id,col_vc2,col_clob from clobperf;
....
Elapsed: 00:00:02.78

--select only id and varchar2 colum
select id,col_vc2 from clobperf;
...
Elapsed: 00:00:00.36

--select only id and clob colum
select id,col_clob from clobperf;
...
Elapsed: 00:00:02.71

So the difference even in this most simple testcase is huge.

We use clob in a rather complex query, where the runtime difference is about factor 20.

tkprof shows, that the number of roundtrips seems to be much higher when fetching the clob.

Any feedback is appreciated.

Thanks,

Werner