Re: ASSM bug: slow INSERT after uncomitted DELETE

From: Jonathan Lewis <>
Date: Thu, 13 Aug 2009 10:31:04 +0100
Message-ID: <>

"Mladen Gogala" <> wrote in message news:h5vevb$nnq$
> On Wed, 12 Aug 2009 09:02:49 -0700, joel garry wrote:
>> 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.
> The phrase "dirty read" means that the effects of uncommitted
> transaction, a delete operation in this case, are visible to other
> transactions. Basically, "delete" puts the block back on the "free"
> list, before the transaction has committed. An insert transaction tries
> to find free blocks to insert rows, reads the bitmap blocks and finds out
> that bitmap blocks are lying and ends up scanning the entire table.
> Effects of the "delete" are visible before the transaction has committed.
> What happens if the delete transaction is rolled back? Will the blocks be
> re-declared as full? Now that is an extremely interesting scenario that
> I intend to test in an ASS managed tablespace and an old school
> tablespace.
> --


There are problems with "metadata" of this type whether you are using ASSM or freelist management.

Here's an equivalent freelists "anomaly":

User A inserts 1,000,000 rows into the table - Oracle adds a load of extents, moves the highwater mark upwards, and takes loads of blocks OFF the freelist before the commit.

If this weren't the case then everyone would have to scan a huge freelist to discover that read consistency said the blocks were empty when in fact they were full. The fact that they don't do this is something you are labelling a dirty read.

It doesn't matter what approach you take - at some point an insert, delete, commit, or rollback, will leave you in a position where the behaviour of metadata looks like a dirty read of data.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ 
Received on Thu Aug 13 2009 - 04:31:04 CDT

Original text of this message