Re: ASSM bug: slow INSERT after uncomitted DELETE

From: Mladen Gogala <>
Date: Wed, 12 Aug 2009 22:16:11 +0000 (UTC)
Message-ID: <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.

Received on Wed Aug 12 2009 - 17:16:11 CDT

Original text of this message