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

From: dbaboy <jojojthomas_at_gmail.com>
Date: Sat, 8 Aug 2009 06:57:50 -0700 (PDT)
Message-ID: <2d24dd67-a2ef-49c2-88d0-63cb83e06843_at_d4g2000vbm.googlegroups.com>



On Aug 7, 10:40 pm, John Hurley <johnbhur..._at_sbcglobal.net> wrote:
> On Aug 7, 1:04 am, ca111026 <ca111..._at_gmail.com> wrote:
>
> snip
>
>
>
> > 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.
>
> 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.

There is a difference between ASM (automatic storage mgmt) and ASSM (automatica segment space mgmt). Are you also on ASSM ? Received on Sat Aug 08 2009 - 08:57:50 CDT

Original text of this message