Insert to Clob datatype test 8x slower on 9207 vs 9205?

From: Dave Stien <noyoudont_at_null.null.null>
Date: 05 Jul 2006 14:15:41 GMT
Message-ID: <slrneanicn.b3q.noyoudont@frodo.local.35ad.com>

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;
/ Received on Wed Jul 05 2006 - 09:15:41 CDT