I’ve been involved over the last few days in a discussion on the OTN Database forum of a strangely behaved index.
The thread started with someone asking if a corrupted index could be the root cause of a job slowing down [probably not, a corrupted index is more likely to cause a job to crash] but the interesting bit came along later as a follow-up post introducing an index that seemed to grow unreasonably large for no apparent reason – apparently bypassing the automatic reuse of empty index blocks that normally takes place.
The interesting aspects of the thread are two-fold:
- the care that you need to take when investigating a problem to make sure that you have described what’s really happening,
- the need to think carefully about the effects concurrency and read-consistency
Despite the initial impression that this was an index showing an unexpected growth pattern, it seems that there is a likely scenario that would produce the effects seen and describe the actual activity that goes on.
The chain of posts to follow – from the entry point I supplied – is the dialogue between me and Hemant K. Chitale. Thanks to various changes over time in the forum software the formatting can get quite messy and the chain of questions and answers hard to follow, unfortunately.
Oracle Scratchpad 
Could the ITC “problem” helps to explain this strange grow of the index?
Comment by lascoltodelvenerdi — September 28, 2009 @ 9:42 am BST Sep 28,2009 |
lascoltodelvenerdi,
It could explain part of the excess size – if the code caused a number of processes to queue and then start running concurrently the nature of the inserts would probably mean that they would all hit the same leaf block temporarily at the start of insert.
Comment by Jonathan Lewis — September 28, 2009 @ 5:42 pm BST Sep 28,2009 |
Hi Jonathan,
Keep in mind that this question might due the effect of that “index rebuild” talk by Don Burleson.
Why not drop the index already?!
Best regards,
Daniel Stolf
Comment by Daniel Stolf — September 29, 2009 @ 2:27 pm BST Sep 29,2009 |
Daniel,
I think someone on the thread suggested dropping the indexes – and given the unusual use of the table that sounds like a pretty good idea.
But there’s that constant threat: 3rd party app – how do you prove it’s safe ?
(Personally, I think that any supplier should be able to guarantee that their application will still give the correct results if you change the indexing or related data storage features. Of course, they don’t have to guarantee that the performance will still be adequate).
Comment by Jonathan Lewis — October 1, 2009 @ 6:08 pm BST Oct 1,2009 |