Re: ASSM bug: slow INSERT after uncomitted DELETE

From: John Hurley <johnbhurley_at_sbcglobal.net>
Date: Sat, 8 Aug 2009 10:42:20 -0700 (PDT)
Message-ID: <195adb88-733a-488d-8d78-0a3826a875a0_at_p23g2000vbl.googlegroups.com>



On Aug 7, 6:35 am, ca111026 <ca111..._at_gmail.com> wrote:

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.

Never mind ... Charles was correct it can be reproduced on 11.1.0.7 ... sorry about that.

I wish I could say that beer was responsible ... but it was a long week at work. Somehow I managed to run just the select statement to create the insert statements and not the insert statements themselves.

Step 6 ran in about 5 minutes on my system versus 1 sec for step 4. Received on Sat Aug 08 2009 - 12:42:20 CDT

Original text of this message