Re: ASSM bug: slow INSERT after uncomitted DELETE
From: joel garry <joel-garry_at_home.com>
Date: Wed, 12 Aug 2009 09:02:49 -0700 (PDT)
Message-ID: <6cb6eb86-8582-4c53-88cd-2cd9dce99d9d_at_m7g2000prd.googlegroups.com>
On Aug 12, 8:31 am, Mladen Gogala <mla..._at_bogus.email.invalid> wrote:
> On Fri, 07 Aug 2009 13:50:35 +0100, Jonathan Lewis wrote:
> > "Cristian Cudizio" <cristian.cudi..._at_yahoo.it> wrote in message
> > news:c8ba2866-cda9-45c8-
>
> bca8-9b646f450..._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
>
> > The problem is when the bitmap blocks are updated to show free space.
> > It's a difficult one to handle. In this case, Oracle has updated the
> > bitmap blocks as the 100,000 rows were deleted, so all the bitmaps show
> > that the target blocks may have free space.
>
> > The second session comes in and reads the first hinted bitmap block,
> > which points it to a data block, which turns out to have an uncommitted
> > delete and doesn't have enough free space.
>
> > So the session works through every single bitmap block in turn, and each
> > bitmap block points to 64 or 128 table blocks which all "might" have
> > space but none of them do because the delete hasn't committed.
> > Ultimately you examine every single block in the table before you get
> > past all the blocks affected by the delete.
>
> > In an earlier version of a similar bug, Oracle DIDN'T mark the bitmap
> > blocks on the delete, and then failed to clean up, so ended up with lots
> > of free space in tables that couldn't be reached because the bitmaps
> > said the blocks were full.
>
> > Look on this as a demonstration that ASSM is supposed to help with OLTP
> > activities only - and DSS/DW bulk activity will cause problems.
>
> > (The trouble is, highly concurrency OLTP activity can still run into
> > problems
> > with ASSM).
>
> Jonathan, the situation that you are describing here reads almost exactly
> like something called "dirty read" by the MS SQL Server folks. In other
> words, you are saying ASSM bitmaps are not protected by the transaction
> mechanism, like the free lists in the "normal" tablespaces and we have
> "phantom reads", blocks are declared free before they actually are free.
> I think (I am trying to develop some tests to test that hypothesis) that
> "normal" free list blocks are protected by the transaction mechanism. If
> that is the case, it would be a huge vote against ASSM.
>
> --http://mgogala.frehostia.com
Date: Wed, 12 Aug 2009 09:02:49 -0700 (PDT)
Message-ID: <6cb6eb86-8582-4c53-88cd-2cd9dce99d9d_at_m7g2000prd.googlegroups.com>
On Aug 12, 8:31 am, Mladen Gogala <mla..._at_bogus.email.invalid> wrote:
> On Fri, 07 Aug 2009 13:50:35 +0100, Jonathan Lewis wrote:
> > "Cristian Cudizio" <cristian.cudi..._at_yahoo.it> wrote in message
> > news:c8ba2866-cda9-45c8-
>
> bca8-9b646f450..._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
>
> > The problem is when the bitmap blocks are updated to show free space.
> > It's a difficult one to handle. In this case, Oracle has updated the
> > bitmap blocks as the 100,000 rows were deleted, so all the bitmaps show
> > that the target blocks may have free space.
>
> > The second session comes in and reads the first hinted bitmap block,
> > which points it to a data block, which turns out to have an uncommitted
> > delete and doesn't have enough free space.
>
> > So the session works through every single bitmap block in turn, and each
> > bitmap block points to 64 or 128 table blocks which all "might" have
> > space but none of them do because the delete hasn't committed.
> > Ultimately you examine every single block in the table before you get
> > past all the blocks affected by the delete.
>
> > In an earlier version of a similar bug, Oracle DIDN'T mark the bitmap
> > blocks on the delete, and then failed to clean up, so ended up with lots
> > of free space in tables that couldn't be reached because the bitmaps
> > said the blocks were full.
>
> > Look on this as a demonstration that ASSM is supposed to help with OLTP
> > activities only - and DSS/DW bulk activity will cause problems.
>
> > (The trouble is, highly concurrency OLTP activity can still run into
> > problems
> > with ASSM).
>
> Jonathan, the situation that you are describing here reads almost exactly
> like something called "dirty read" by the MS SQL Server folks. In other
> words, you are saying ASSM bitmaps are not protected by the transaction
> mechanism, like the free lists in the "normal" tablespaces and we have
> "phantom reads", blocks are declared free before they actually are free.
> I think (I am trying to develop some tests to test that hypothesis) that
> "normal" free list blocks are protected by the transaction mechanism. If
> that is the case, it would be a huge vote against ASSM.
>
> --http://mgogala.frehostia.com
I don't think so. If it were like dirty read, the performance problem wouldn't show up and bitmaps would get corrupted (if I'm understanding correctly what you mean, which I'm probably not). Tests will be interesting. Maybe a better way to put it is the type of transaction mechanism used favors small transactions, and the previous bug handled large transactions even more ham-handedly than now. Perhaps it was just one of those bugs where the scope of the feature wasn't really thought through as Oracle waited to see how people would use it.
jg
-- _at_home.com is bogus. "Beverly Hills, that's where I want to be." - Weezer http://www3.signonsandiego.com/stories/2009/aug/11/jury-acquits-beverly-hills-real-estate-agent-stars/?uniontribReceived on Wed Aug 12 2009 - 11:02:49 CDT