Re: ASSM bug: slow INSERT after uncomitted DELETE

snip

> 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.

Thanks for the complete writeup.

I tried your test case. My system is an 11.1.0.7 Enterprise Edition 64 bit system on OEL 5.2 64 bit. It has 32 gig of RAM and a very nice IO subsystem. Using ASM for storage.

My times to complete steps 4 and 6 ( yes from different sessions ) were the same basically ... not different times as you reported. My testing was done using Toad going into the database thru network not locally on the database server using sqlplus. My times were more like 3 secs than 1 ( as your first step 4 was ).

I can repeat again eventually using sqlplus locally if you want to know if that changes anything.

I do have one system back at 10.2.0.4 that I can eventually also test on. Received on Fri Aug 07 2009 - 18:26:05 CDT