Index Reviews and Fruit Baskets

I received an email from an obviously annoyed DBA this weekend.  I’m sure many of you are not surprised, I can be rather annoying.

The annoyance stemmed from a request  I was working on to rebuild a number of bitmap indexes.  The problem is, when I take on a problem, I have a tendency to demand to do things the right way, so a simple request to check bitmap indexes for fragmentation,  always turns into a bit larger project with me at the helm.

When it comes to indexes, it’s not just a simple rebuild.  I want the index JUSTIFIED. 

Is the index used?  (You might be surprised how often the answer comes back with a “No”.)

If involving more than one column, is it in the best order to support the queries that use it?

Is it the correct type of index?  Would it be more beneficial if it was a bitmap or reverse key than btree?

What are the ITL, row lock, buffer busy, physical and logical reads reported on each of the indexes?

How much additional work does the database have to perform to support the index due to transactional demands on the table?

Do they support a primary key, unique or foreign key constraint? Are there triggers and/or sequences involved with the index?

Are we attaining good statistics on these indexes so that they can be used effectively?

Is there any code with hints to influence the optimizer that may need to be examined that “hint” the index?

Across the board->  what is the size ratio of index vs. data?  In OLTP I expect this to vary, but in a large OLAP environment, I do expect this to be small, if not, I’m onto a review of partitioning next!

As the natural life of a database is growth and change, indexes that may have served a great purpose or have been a substantial benefit to performance at one time, may not offer those same deals later on in the environment’s life.  I feel it is essential for a DBA to review indexing from time to time and verify that your indexing strategy still holds true for heavily used or pressured objects. 

Many of the questions above are answered through data gathering and  research, but it is also helpful to monitor index usage.  There is a slight overhead involved in the process, so it is important to monitor for the amount of time you consider substantial enough to verify the index will be “captured” if it is used.
ALTER INDEX <indx name> MONITORING USAGE;

Once finished, you simply turn monitoring back off:
ALTER INDEX <indx name> NOMONITORING USAGE;

Post the time you have monitored your index, you can then report from v$object_usage.

select table_name tbln, index_name indx, monitoring mntr, used
from v$object_usage
order by table_name, index_name;

Once the indexes are stated as used, I print out the report for the used indexes and alter them again, to stop the monitoring. 

The ones listed as used, must now go through a more “definitive” review to ensure there were no “false positives”.  

1. Was it actually an effective use?
2. Was it due to inaccurate statistics?
3. Was there a better choice and the CBO is simply using the index as there isn’t a better option, (i.e. either a more efficient index or time for partitioning)?

These are the types of questions you are going to have to ask yourself as you review the database’s indexing strategy.

For the annoyed DBA, he thought I was questioning the technical choices made by DBA’s, when in truth, I was simply justifying indexes to ensure that those rebuilt were ones that could offer the most benefit to the customer and those that aren’t, are reviewed, redesigned or removed from the  environment.

Now until I realize that those I work with might not be used to the way I deal with databases, I may need to send this poor DBA a floral arrangement or maybe a fruit basket… 🙂