Re: ASSM bug: slow INSERT after uncomitted DELETE
From: Cristian Cudizio <cristian.cudizio_at_yahoo.it>
Date: Fri, 7 Aug 2009 06:51:50 -0700 (PDT)
Message-ID: <b5ee2590-3c82-459f-8a86-28707ffcbb69_at_q35g2000vbi.googlegroups.com>
On 7 Ago, 14:50, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Cristian Cudizio" <cristian.cudi..._at_yahoo.it> 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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
Date: Fri, 7 Aug 2009 06:51:50 -0700 (PDT)
Message-ID: <b5ee2590-3c82-459f-8a86-28707ffcbb69_at_q35g2000vbi.googlegroups.com>
On 7 Ago, 14:50, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Cristian Cudizio" <cristian.cudi..._at_yahoo.it> 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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
Thank Jonathan,
so, first session (that wich as made the deletes) is quicker because
it can use directly the first block ?
Regards,
Cristian
Received on Fri Aug 07 2009 - 08:51:50 CDT