Re: Do you use ASSM (Automatic Segment Space Management)?

From: ca111026 <ca111026_at_gmail.com>
Date: Thu, 6 Aug 2009 22:04:44 -0700 (PDT)
Message-ID: <365a4a4f-c196-41ef-b8ba-f00b81b4a42d_at_y28g2000prd.googlegroups.com>

Test case to reproduce ASSM bug: slow inserts when table contains large uncomitted DELETE.

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. Received on Fri Aug 07 2009 - 00:04:44 CDT