DB2 Performance Advice: Hash Joins/1,000 TX

One important cost metric for measuring tuning efficiency and effectiveness is "Hash Joins per 1,000 Transactions." DB2 may perform a hash join when there is an equal predicate between two columns of the same data type, and one or more of the columns does not have an index defined. Thus, the presense of hash joins suggests that one or more potential indexes are undefined.

The formula is:
HJ/1000TX = (TOTAL_HASH_JOINS * 1000) / ( COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS)

• Down is good. When DB2 has to do fewer hash joins to complete its transactions, this represents a cost reduction improvement and greater CPU efficiency.
  • Hash Join Costs will go down when missing indexes are created. By reducing Hash Joins, not only will CPU cycles be saved, but you should also note a general reduction in Bufferpool Logical Reads per Transaction and reduced consumption of SORTHEAP memory.

• Up is bad. When DB2 has to do more hash joins to complete its transactions, this represents performance degradation, higher SORTHEAP utilization, and higher CPU cost. If the cost becomes too high, you may be tempted to upgrade your hardware. Please do not needlessly upgrade your hardware. Higher values tend to indicate that indexes are missing or are ineffectively designed.

After implementing a new index or other physical design change, monitor "Hash Joins/1000 TX" to verify that average hash join cost is indeed going down. If the cost metric goes up, it may be appropriate to back out a recent physical design change.

---

IMPORTANT NOTE

The trick, of course, is knowing which missing indexes need to be created. To find out, use the SQL Equalization and Cost Aggregation technique to aggregate statements according to highest CPU costs (DBI recommends using pureFeat for DB2 LUW to automate this analysis (per US Patent #6,772,411)). Explain the statements having the highest CPU costs and look for the presence of the Hash Join method. Alternatively, review the statement text WHERE clauses and query the Catalog to determine missing indexes.

---