ASSM bug: slow INSERT after uncomitted DELETE

From: ca111026 <ca111026_at_gmail.com>
Date: Fri, 7 Aug 2009 03:35:46 -0700 (PDT)
Message-ID: <c26d8840-cb87-4390-9249-2b6293ca66f9_at_u16g2000pru.googlegroups.com>

In my previous post I mentioned ASSM bug (present in 10.2.0.4) that makes single-row INSERT extremely slow when table contains large uncomitted DELETE. Here is the test case:

Tests were run on AIX 5.3 (64-bit) and Red Hat Linux (32-bit), Oracle 10.2.0.4

1. Create tablespace, it uses default 8K block size

create tablespace assm
extent management local uniform size 1m
segment space management auto
datafile
'/abc/db01/oracle/ABC1P/oradata/assm_01.dbf' size 1000m;

2. Create table

create table test_assm
(
 n1 number,

 v1 varchar2(50),
 v2 varchar2(50),
 v3 varchar2(50),
 v4 varchar2(50),
 v5 varchar2(50),
 v6 varchar2(50),
 v7 varchar2(50),
 v8 varchar2(50),
 v9 varchar2(50),
v10 varchar2(50)

)
tablespace assm;

3. Populate table with 1,000,000 rows, COMMIT at the end

begin
for i in 1..1000000 loop
insert into test_assm values
(i,
'123456789*123456789*123456789*123456789*1234567',
'123456789*123456789*123456789*123456789*1234567',
'123456789*123456789*123456789*123456789*1234567',
'123456789*123456789*123456789*123456789*1234567',
'123456789*123456789*123456789*123456789*1234567',
'123456789*123456789*123456789*123456789*1234567',
'123456789*123456789*123456789*123456789*1234567',
'123456789*123456789*123456789*123456789*1234567',
'123456789*123456789*123456789*123456789*1234567',
'123456789*123456789*123456789*123456789*1234567');

end loop;
end;
/

COMMIT; 4. Insert additional 1000 rows into the table using ***SINGLE_ROW*** inserts.
I used following script to generate INSERT statements

select
'insert into test_assm values(' || n1 ||
',''123456789*123456789*123456789*123456789*1234567'',' ||

'''123456789*123456789*123456789*123456789*1234567'',' ||

'''123456789*123456789*123456789*123456789*1234567'',' ||

'''123456789*123456789*123456789*123456789*1234567'',' ||

'''123456789*123456789*123456789*123456789*1234567'',' ||

'''123456789*123456789*123456789*123456789*1234567'',' ||

'''123456789*123456789*123456789*123456789*1234567'',' ||

'''123456789*123456789*123456789*123456789*1234567'',' ||

'''123456789*123456789*123456789*123456789*1234567'',' ||

'''123456789*123456789*123456789*123456789*1234567'');'
from

        test_assm
where

        rownum < 1001;

It took 1 second to insert 1000 rows through single-row inserts.

5. Delete all rows from the table, don't commit

6. Re-execute script that inserts 1000 rows from a different session. Runtime > 20 min.

There were no indexes on the table.

Insert into table containing uncomitted DELETE should not be significantly slower
than insert into table without DELETE.

When test was repeated using table in non-ASSM tablespace (still locally managed) there were no performance degradation when table contained uncomitted DELETE. Received on Fri Aug 07 2009 - 05:35:46 CDT