Re: ASSM bug: slow INSERT after uncomitted DELETE

|"ca111026" <ca111026_at_gmail.com> wrote in message news:23f576d0-f399-4cbb-8c2a-e134c190cfb5_at_l35g2000pra.googlegroups.com...
>I tested using Oracle 11.1.0.7.0 (64-bit) on AIX 5.3 - the problem is
> still there, it has not been fixed.
>
> Now, these bitmaps, shouldn't they be updated in transactionally
> consistent way like everything else?
> Let's say all rows have been deleted from the table, but DELETE hasn't
> been commited. Another session comes
> and attempts to run insert. It checks bitmap for free space in the
> blocks that have already been allocated to the table.
> If bitmap is transactionally consistent then it will show that all
> blocks are full, so instead of trying to insert into existing
> blocks Oracle will allocate another extent. Do I miss something here?
> Is there a reason why bitmaps cannot be part of transaction?
>
> Is it a bug or a "feature"?
>
>

In my demonstration case (with 100,000 rows of about the length you specified in your test case) I had about 440 bitmap blocks pointing to 7,000 blocks that were full of deleted but not committed data.

According to your suggestion, what do you want Oracle to do on the commit ? At present it marks the transaction table slot and calls the log writer to write - optionally it does a "commit cleanout" on some of the blocks it has changed.

Do you want Oracle to visit all the bitmap blocks on commit and update them to show that they are now empty ? Even if some, or all, or them have been written to disc already and need to be re-read ? Think about the impact that has on the way the commit time for a transaction could vary.

Consider the opposite situation - you insert 1,000,000 rows that fill thousands of blocks referenced by hundreds of bitmap blocks, but don't commit... Should the bitmap blocks say the table blocks are still empty - which means a single row insert would have to do exactly the same trip through all the bitmap blocks you're now seeing and doing the same access to the undo segment headers to check that the insert is not yet committed.

This is the problem with bitmap blocks - when do you change their state.

That's why they're not a good idea for DSS and DW activity, and why you should only use them with real OLTP activity - they work best for commits after small changes 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