When you have executed a tuning job, the Generated Cases area of the Overview tab reflects Tuner’s analysis of the specified statements and cases. The Generated cases create alternative execution or explain paths that could be more or less efficient than the default path the databases uses. Executing these cases provides the statistics necessary to optimize performance.

Once a tuning job has executed, use the Cost and Execution Statistics value columns to determine the fastest execution path for each statement. The Cost column shows the performance cost of an execution path as determined by the database. The Execution Statistics are the actual results of running the SQL statement using the generated case. This is where DB Optimizer can help you find where the database default path is actually not the optimal path. The Elapsed Time(s) and Results columns can more accurately show the most efficient execution path.

In the Cost and Execution Statistics columns, the values of the original statement are considered to be the baseline values. A Cost column can be expanded to provide a graphical representation of the values for statements and cases. Similarly, the Execution Statistics column can also be expanded to display a graphical representation of values as well. The bar length and colors are intended as an aid in comparing values, particularly among cases.

Case query times based on the original statement can be represented as colored bars on the Generated Cases area of the Overview tab to help you determine the fastest execution path for the given selections. The baseline value of the original statement spans half the width of the column, in terms of bar length. For cases of the original statement, if one or more cases show a degradation value, the largest value will span the width of the column. Bar lengths for all other cases will then be displayed in comparison length to the highest degradation value.

The cost and execution results are color-coded as follows:

  • Light blue. These cases are within the degradation and improvement threshold. Applying these changes may marginally improve or degrade the efficiency of the SQL statement.
  • Green. These cases have values less than the improvement threshold. There is a high probability that changing the SQL statement with this alternative execution path will improve efficiency.
  • Red. These cases have values higher than the improvement threshold. Implementing these changes will degrade the efficiency of the SQL statement.

Determine the Best Cases for Statement Execution Path Time

Once the tuning job has executed, view the Generated Cases area of the Overview tab and determine the best possible case in terms of the Execution Statistics column values. This will indicate the most optimized query path for a given statement. Once you have determined the best case, you can execute that case on the specified data source and alter the database code to run the statement as that case on the native environment.

If you don’t find an acceptably fast path, go to the Analysis tab. The Analysis tab can identify missing indexes and by examining the diagram you may be able to determine if there is something wrong with the SQL or schema.