Find tables with high inserts/updates and rebuilding indexes

Well, last Friday was a good day. I was able to recover a major system process that had been plaguing one of our systems for a few days. It took some time because part of the issue is a hardware problem. Looking at what this application did brought back those memories of other systems will similar issues and I found myself saying “[Theoretically this isn’t supposed to work, but I want to try this because the scenario looks correct]”

Once more the problem was “bad” indexes. In theory a b-tree index should never “go bad”, but in my experience they do. I’ve seen it too many times on tables with huge numbers of inserts and deletes. Partitioning is the best solution, but when that hasn’t been done in the design and the system is down now, then I’ll always try rebuilding indexes. Worst case you burn some CPU and maybe have to add a little space while it is running. Best case, you save the day.

Now we just have to hope they figure out how to get the hardware working!

So, here are two pages I’ve written and posted previously. Both of which apply.

Breaking two myths about rebuilding indexes in the Oracle Database

How to find the number of inserts, updates, and deletes on an Oracle table


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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