IBM® Z Database Assistant can evaluate your SQL statements to identify those that might not be performing optimally and provide recommendations for improving their performance.
Before you begin
Before you evaluate SQL performance, you should complete the following tasks:
- Review and, if needed, update the SQL tuning settings before evaluating SQL performance. When you evaluate SQL performance, if you find that the behavior of IBM Z Database Assistant needs to be adjusted, you might need to update the SQL optimization settings to get the behavior you want. See Updating SQL tuning settings.
- Schedule or manually start SQL statement exploration.
About this task
After you enable IBM Z Database Assistant, Db2® starts collecting query execution history. When an appropriate number of SQL execution records has been collected, Db2 triggers model training.
It's important to keep in mind that the statistics that IBM Z Database Assistant displays are cumulative, and therefore change over time.
Assuming that your SQL tuning settings allow it, when IBM Z Database Assistant detects that an access path regression has occurred, it automatically reverts to the previously used access path.
Procedure
To evaluate top SQL statements - ranked by CPU usage:
-
Open the SQL tuning tab of the connection for which you want to improve
SQL performance.
By default, the Top SQL statements is displayed,
which shows the SQL statements that are using the most CPU resource.
-
Select the range of time for which you want to view SQL statements using the drop-down menu in
the upper-right corner.
You can further filter the statements by the following exploration statuses:
- All displays SQL queries with any status that fall within the time range
selection.
- Candidates for exploration displays those SQL queries that are eligible
to be explored.
- Exploration in progress displays those SQL queries that are currently
being explored. When the exploration concludes, recommendations are available for these
queries.
- Completed displays those SQL queries for which recommendations have been
generated.
Other statuses include:
- Baseline data collection
- Exploration requested
- Complete with error
- Click the statement hash ID to display statement execution history for a statement. You
can specify start and end time criteria for the time period you are interested in.
- If a statement has been evaluated, click View details in the Actions column to view access path, indexes, or query rewrite recommendations.
For more information about the available recommendations, see the following topics:
To view the statements with recommendations:
- Click the Past recommendations tab.
- The Past recommendations table displays statements for which
explorations resulted in recommendations. Past recommendations can come from explorations on demand
or explorations scheduled with the exploration scheduler.
All statements in this table
have the status Ready for review.
- Click View details in the Actions column to
view access path, indexes, or query rewrite recommendations.
To evaluate regression detection:
- Click the Regression detection tab.
- Search for a specific statement by using the Search bar above the Regression detection table.
- Filter the table by clicking the filter icon (
). Filter options fit into two categories: regression cause and regression type.
Filter options for regression cause include:
Filter options for regression type include:
- Click View details in the
Actions column to view access path performance comparison
details.
The result field above the table indicates if there was regression and the weighted delta %.
The confidence % indicates the degree of confidence that IBM Z Database Assistant has in the weighted delta percent. It indicates confidence that the Weighted delta % is accurate. It can vary between 0% (lowest possible confidence) and 99% (highest possible confidence).
You can use these details to determine if you want to resolve or skip the detected regression. For more information about manually resolving access path regression, see Manually resolving access path regressions.