Brother-Eagle® DB2 Performance
Advice: Sorts/TX

One of the better cost metrics for measuring tuning efficiency and effectiveness is "Sorts per Transaction."

The formula is:
SORT/TX = (TOTAL_SORTS) / ( COMMIT_SQL_STMTS + ROLLBACK_SQL_STMTS)

Brother-Eagle presents "Sorts/TX" as a delta value showing the change since the prior measurement.

• Down is good. When DB2 has to do fewer sorts to complete its transactions, this represents a cost reduction improvement and greater CPU efficiency.
  • Costs will go down when new clustering indexes, Multi-Dimensional Clustering tables, and other physical design techniques are introduced which help DB2 reduce the number of sorts required.
  • Costs can also go down when ORDER BY, GROUP BY, DISTINCT, and other clauses are removed from SQL. Please consult with your End User community to verify the appropriateness of modifying SQL for this purpose.

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

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

Most application vendors do not provide Clustering Index definitions with their products. This is very unfortunate. Years of experience has shown that CPU utilization can be reduced by 50% or more by adding as few as five Clustering Indexes. A 50% CPU reduction suggests that you could add twice as many users to the same machine, or cut transaction times in half. At a minimum, you can certainly avoid a hardware upgrade. Please, do not needlessly upgrade your hardware!

The trick, however, is knowing which five Clustering indexes to create. To find out, use the SQL Equalization and Cost Aggregation technique to aggregate statements according to highest sort costs (DBI recommends using Brother-Panther™ for DB2 LUW to automate this analysis (per US Patent #6,772,411)). Review the texts of the most sort costly statements. ORDER BY clauses will typically provide excellent clues for valuable Clustering Indexes.

View full list of DB2 LUW advice topics