Hit something unusual testing a new production server.

Tests have shown scripts inserting to a table with 1 clob type
run 8x slower in 9207/jan06 CPU compared to 9205 security patch68.

Have isolated steps that query 2 feeder tables and load to a table
containing CLOB type via pl/sql anon block.

Current production environment runs this load in 20mins.

The same steps on a cold backup assembled + upgraded on our new
production
environment take over 4hrs! Like for like database spfile used on new
server.

Explain plan for the query in the load sql is identical in both
environments.

I have traced this so far to insert on clob datatype using a cut
down benchmark (200k rows limited by rownum in the anon block)

Switching the data type to varchar2(4000) on target table with the mini
benchmnark gives us:

Current : 2min 31sec
New : 1min 04sec.

Based on this, i'm relatively happy that the hardware is not at fault.
(was initially suspecting difference in io subsystem for redologging)

All other measurements show our new hardware/oracle release faster
except when loading this clob column.

What i'd like to pin down is why the insert into clob performance seems
radically slower..


Any advice / experiences / tales of lobs and clobs in similar scenarios
welcome.


</Dave>


--My environment Details Follow--
Current production system. 9.2.0.5 + security patch 68 on Sun Solaris 8
64bit.
Hardware: 6x1050mhz v880 16gb ram, LSI san storage, VxVM/VXfs 3.5


Intended new production system
9.2.0.7 + January 2006 Critical patch update on Sun Solaris 8 64bit.
Hardware: 8x1200mz v880 32gb ram, DGC san storage. VxVM/Vxfs 4.1

Pertinent DB details
--------------------
Compatible : 9.2.0.4
Db blocksize : 8K
dbfile_multiblock_read_count : 16
large_pool_size : 8mb
log_buffer : 1mb
optimizer_mode : choose
pga_aggregate_target: 50M
db_cache_size : 160M
shared_pool_size : 150M
undo_management : auto
work_area_size_policy : auto


DDL for load table containing clob datatype
-------------------------------------------

create table TEMP_WOFREETEXT
( WONUM VARCHAR2(30) not NULL,
TEXT_TYPE VARCHAR2(40) not NULL,
CONTENT CLOB)
tablespace XXX_DATA
pctfree 0
storage (initial 1M next 1M pctincrease 0)
nologging;

Tablespace XXX_DATA is locally managed, system managed extents, space
management auto.

Feeder tables details
---------------------

create table temp_WOdescr
(WONUM NOT NULL VARCHAR2(10)
DESCR NOT NULL VARCHAR2(150)
LDKEY NUMBER(38))

2444795 rows, 152mb, 90 exents

desc temp_longdescription
(LDKEY NOT NULL NUMBER
LDOWNERTABLE NOT NULL VARCHAR2(18)
LDOWNERCOL NOT NULL VARCHAR2(18)
LDTEXT LONG)

1551725 rows, 1216mb, 202 extents


AnonBlock to load. (and cast varchar2/long to clob)
----------------------------------------------------

declare
cursor lc is
select WO.WONUM, 'xxxxxxxxxxxx' text_type, l.ldtext content
from temp_WOdescr WO,
temp_longdescription l
where WO.ldkey = l.ldkey
and l.ldownertable = 'yyyyyyyyyyy'
and l.ldownercol = 'zzzzzzzzzzzz'
and rownum < 200000;
rec lc%rowtype;
c NUMBER := 0;
begin
open lc;
loop
fetch lc into rec;
exit when lc%notfound;
c := c + 1;
insert into temp_WOfreetext(WONUM,text_type,content) values
(rec.WONUM,rec.text_type,rec.content);
if c = 50000 then commit;
c:=0;
end if;
end loop;
close lc;
commit;
end;
/

Update on this following isolated testing on a little sun e250.

9207 shows lob insert 200k row benchmark taking 50% longer than 9205.
I have 10min statspack snaps gathered during each run via script.

Based on the spdata below am i right in thinking the 9207 difference may
be due to redo log related differences in behaviour?

(9205 does not seeem to suffer on log file parallel write and spends
more time direct path writing lob)

Thoughts, comments, suggested further tests? am i way off the mark?

cheers,
Dave.


9205 Top 5 Timed Events
-----------------------
Event Waits Time(s) %Total elap time
direct path write (lob) 4,677 281 54.76
dbfile seq read 17,458 130 25.37
dbfile parallel write 23 46 8.93
controlfile par w 182 42 8.28
log file parallel w 512 9 1.70

9207 Top 5 Timed Events
-----------------------
Event Waits Time(s) %Total elap time
log file parallel w 656 204 56.39
db file sequential read 13741 99 27.28
controlfile par write 189 15 4.03
direct path write(lob) 3658 5 1.41


(9205 and 9207 databases created via same script and with init params.
Simple single disk storage and cooked ufs file system with logging
One database running at a time.)