Re: ASSM bug: slow INSERT after uncomitted DELETE

"Mladen Gogala" <mladen_at_bogus.email.invalid> wrote in message news:h5vevb$nnq$1_at_solani.org...
> 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.
>
>
>
> --
> http://mgogala.freehostia.com

Mladen,

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.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html