Oracle Scratchpad

September 27, 2009

Index trouble

Filed under: ASSM,Indexing,Infrastructure,Oracle,Performance,Troubleshooting — Jonathan Lewis @ 5:08 pm BST Sep 27,2009

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.


  1. 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 | Reply

    • 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 | Reply

  2. 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 | Reply

    • 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 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: