Re: ASSM bug: slow INSERT after uncomitted DELETE
Date: Thu, 13 Aug 2009 10:31:04 +0100
"Mladen Gogala" <mladen_at_bogus.email.invalid> wrote in message
> 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
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.htmlReceived on Thu Aug 13 2009 - 04:31:04 CDT