Re: ASSM bug: slow INSERT after uncomitted DELETE
From: joel garry <joel-garry_at_home.com>
Date: Thu, 13 Aug 2009 09:22:43 -0700 (PDT)
Message-ID: <33372a52-09ac-4a6c-9c3b-b4db7470e17c_at_g1g2000pra.googlegroups.com>
On Aug 12, 3:16 pm, Mladen Gogala <mla..._at_bogus.email.invalid> wrote:
> 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.
>
> --http://mgogala.freehos.com
Date: Thu, 13 Aug 2009 09:22:43 -0700 (PDT)
Message-ID: <33372a52-09ac-4a6c-9c3b-b4db7470e17c_at_g1g2000pra.googlegroups.com>
On Aug 12, 3:16 pm, Mladen Gogala <mla..._at_bogus.email.invalid> wrote:
> 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.
>
> --http://mgogala.freehos.com
Thanks to you and Jonathan for clarifying this. I'm thinking the key to unraveling this semantic knot is that the "metadata" is block description data not user data, and needs its own transactional analysis outside of the user data transactions. I think it would be a mistake to judge it as a point for other rdbms's that encourage dirty reading by app programmers, or against Oracle for using dirty reading for housekeeping - as always, if you tour the sausage factory, prepare for some eye-opening experience. The important points are that we understand what is happening when a trade-off is involved, and that it works bug-free - that latter must be judged at a very high standard, much higher than app code.
jg
-- _at_home.com is bogus. http://www3.signonsandiego.com/stories/2009/aug/13/standards-sought-electronic-books/?uniontribReceived on Thu Aug 13 2009 - 11:22:43 CDT