The Effect of Indexes on DB2 Performance

The trail for the hunter of performance problems leads from the DB2 table to its indexes. As I went deeper into this DB2 performance jungle, I discovered more interesting artifacts.

DB2 Index

Since the table I talked about in the last post was typical of other tables in other applications in the company, it made a good candidate for research.  Looking at the DB2 indexes by running a DB2 RUNSTATS report on the table indicated some interesting items.  By querying the DB2 Catalog for indexes and statistics, I verified that a number of the DB2 indexes were less than ideal for good DB2 performance.

The reports showed that a number of the indexes were similar in definition with one field which we will call Account Number used in a number of the indexes.  These types of duplicate definitions are sometimes overlapping and further research might indicate that they are not being used or could be consolidated to improve DB2 performance.  The indexes also showed a second field being used in a number of indexes.  These DB2 indexes need to be analyzed to determine if combining some of these indexes could also be done.

Cluster Ratio

Within the RUNSTATS report there were several interesting aspects of the various indexes.  First is the uniqueness or cardinality that plays a significant role in the access performance of the different indexes.  Looking through the RUNSTATS report shows that there were 954,938 rows in the table and eight of the eleven indexes had CLUSTERRATIOS of 50% or less, making their access use less than optimum.  Since the indexes were also not unique within the table design they can be redesigned without this additional consideration.

Uniqueness

Uniqueness is very important for an index structure because it limits the number entries the database must analyze to find your desired row.   The DB2 index uniqueness has huge DB2 performance impacts. For example, an index on account number is better than an index on gender because of the uniqueness or number of entries associated with their various values.  If searching for your information started with a gender ‘M’ for male and ‘F’ for female search the number of entries you need to go through would be tremendous. With an unique account number index limits the number of entries for each value and gets you to the desired information quickly.

Index Reorganization

Also seven of the index structures had more that 10% of their indexes entries stored far from their optimum page location.  This is indicated through the FAROFFPOS numbers in the report with number greater than 100,000.  Most of the far off position index entries were in the 953,000 range making their access very resource intensive.

Index Use

Next we examined if all the table indexes were being used.  By running complex query against the DB2 Catalog,, we found three indexes that are not being used by any of the static DB2 applications.  To determine if these indexes could be dropped, research needed to be done to verify that these indexes were not being used by any dynamic workloads. If they weren’t being used, they should be deleted as soon as possible to eliminate their extra overhead during INSERT, UPDATE and DELETE operations.

Finding these unreferenced indexes was a surprise and researching this issue for the entire system uncovered 213 other indexes that are not referenced by any static DB2 applications.  After research into the dynamic DB2 workload, unused indexes and their overhead needed to be deleted from the system to improve DB2 performance.

Summary

In order to use indexes optimally and improve DB2 performance,

  • Check to insure that indexes leverage uniqueness
  • Make sure that clustering indexes make sense and that the table is clustered in that order with a CLUSTERRATIO of 95% or higher
  • Make sure that indexes are in optimum order
  • Make sure that all defined indexes are being used

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>