Re: ASSM bug: slow INSERT after uncomitted DELETE

From: Cristian Cudizio <>
Date: Fri, 7 Aug 2009 06:51:50 -0700 (PDT)
Message-ID: <>

On 7 Ago, 14:50, "Jonathan Lewis" <> wrote:
> "Cristian Cudizio" <> wrote in message

> I've made this test.
> Really each insert on the second session is very slow, i've traced the
> session and Oracle scans all (it seems) table blocks, with "db file
> sequential read"s
>  while in the first session doesn't.
> also after commit on first session insert on secondo session is fast.
> I'm just curious to know why oracle make those *consistent*  gets on
> a insert.
> Regards,
>  Cristian
> The problem is when the bitmap blocks are updated to show free space.
> It's a difficult one to handle. In this case, Oracle has updated the bitmap
> blocks as the 100,000 rows were deleted, so all the bitmaps show that
> the target blocks may have free space.
> The second session comes in and reads the first hinted bitmap block,
> which points it to a data block, which turns out to have an uncommitted
> delete and doesn't have enough free space.
> So the session works through every single bitmap block in turn, and each
> bitmap block points to 64 or 128 table blocks which all "might" have space
> but none of them do because the delete hasn't committed.  Ultimately
> you examine every single block in the table before you get past all the
> blocks affected by the delete.
> In an earlier version of a similar bug, Oracle DIDN'T mark the bitmap
> blocks on the delete, and then failed to clean up, so ended up with lots
> of free space in tables that couldn't be reached because the bitmaps
> said the blocks were full.
> Look on this as a demonstration that ASSM is supposed to help with
> OLTP activities only -  and DSS/DW bulk activity will cause problems.
> (The trouble is, highly concurrency OLTP activity can still run into
> problems
> with ASSM).
> --
> Regards
> Jonathan Lewis
> Author: Cost Based Oracle: Fundamentals
> The Co-operative Oracle Users' FAQ

Thank Jonathan,
so, first session (that wich as made the deletes) is quicker because it can use directly the first block ?

 Cristian Received on Fri Aug 07 2009 - 08:51:50 CDT

Original text of this message