Re: ASSM bug: slow INSERT after uncomitted DELETE

From: Cristian Cudizio <cristian.cudizio_at_yahoo.it>
Date: Fri, 7 Aug 2009 05:40:09 -0700 (PDT)
Message-ID: <c8ba2866-cda9-45c8-bca8-9b646f450c31_at_q14g2000vbi.googlegroups.com>

On 7 Ago, 12:35, ca111026 <ca111..._at_gmail.com> wrote:
> 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.

I've made this test.
Really each insert on the second session is very slow, i've traced the session and Oracle scans all (it seems) table blocks, with "db file sequential read"s
 while in the first session doesn't.
also after commit on first session insert on secondo session is fast. I'm just curious to know why oracle make those *consistent* gets on a insert.

Regards,
 Cristian Received on Fri Aug 07 2009 - 07:40:09 CDT