Evaluating query performance

Db2ZAI evaluates the performance your SQL statements in order to determine whether the performance can be improved or to safeguard against degradation.

To evaluate SQL Performance:

1. Open a browser window and navigate to https://<hostname>:<port>/, where <hostname> is your Db2ZAI address and <port> is the port you have assigned to Db2ZAI.
2. Sign in to IBM Db2 AI for z/OS®.
3. From the set of available connections, locate the one for which you want to evaluate SQL performance, and click .
4. The SQL optimization dashboard is displayed. The default view shows the available data for the top CPU consumers for static SQL.

   Use the drop-down in the upper left corner of the page to select the range of the displayed data. You can choose to view the data for:

   • All CPU consumers
   • Top CPU consumers (the default view)

   You can also toggle between viewing data for Static statements or Dynamic statements.

   Note:

   For static SQL, some statement metrics will be displayed once the packages containing the statements are rebound and executed.

   For dynamic SQL, the statement metrics will be updated as they come in and are executed.

5. The SQL optimization progress section of the dashboard shows the current state of the exploration process through which Db2ZAI finds improved access paths for the statements it is targeting.

   In terms of how SQL optimization progress is measured, queries start out with 'baseline data collection', in which Db2ZAI is collecting access path data and query performance metrics for the query.

   Next, if Db2ZAI is able to create a model for the query using the baseline data that was collected, its status moves to 'ready for exploration.' Exploration is a process that Db2ZAI uses to find a better-performing access path for the query. If Db2ZAI is able to find a better performing access path, it will show up as a recommendation under Actions to take now in the dashboard.

   After a query has been explored, or is in the process of being explored, its status is changes to 'exploration complete or in progress.' A query may subsequently be placed back into 'ready for exploration' status if Db2ZAI determines that it might benefit from another exploration process.

6. The Actions to take now section of the dashboard indicates the next steps that Db2ZAI is recommending.
   • The Switch to new access path counter indicates the number of recommended new access paths. Click the arrow (➚) next to the counter to review the recommendations.
   • The Lock in current access path counter indicates the number of access paths for which a regression might occur and for which you might want to lock the access path to avoid the regression.
   • For static statements, the Rebinds for a new access path counter shows the number of packages for which recommendations are partially deployed. It is a reminder that you need to rebind to complete the deployment.
   • For static statements, the Rebinds to resolve access path regressions counter shows the number of rebinds that you need to execute in order to fix access path regressions.
   • For static statements, the Analyze rebinds that failed for new access path counter shows the number of rebinds that you need to execute in order to fix access path regressions.
   • For static statements, the Analyze rebinds that failed to resolve access path regressions counter indicates that there are rebinds that failed to resolve access path regressions, for which you can choose to drill down further, to investigate why the rebind failed. Click the arrow (➚) next to the number of rebinds to scroll down to the Static packages table, then click the Analyze rebind output link in the Recommendations column. The rebind output for the package is displayed.

     The rebind timestamp, rebind command issued, and the rebind output are shown. You can use this information to investigate possible reasons for the rebind failure.

7. For static packages, you can drill-down to view the performance data for individual SQL statements inside the package. In the Static packages table, click the package name.

   The statements for which data are available are displayed in a following table, along with the information described in the Package statement list in the topic SQL optimization data elements.

8. You can view detailed data for both static and dynamic statements.

   To view the details of a particular static statement, click the STMT_HASHID2 in the package statement table. The Statement details page opens.

   To view the details of a particular dynamic statement, click the STMT_HASHID2 in the Dynamic statements table. The Statement details page opens.

   To learn more about the various statement details, see SQL optimization data elements.

9. The Exploration details section shows various parameters related to the most recent exploration for the statement. This section contains data that can help you to determine whether or not to deploy a recommendation. See SQL optimization data elements for a description of the parameters presented.

   Click the down arrow (˅) below the exploration details to open the Latest access path exploration table. This shows detailed information for the explorations for the statement.

   As an example of how to use the data shown in the table to assist you in making the decision to accept or reject a recommendation, assume that you're run an exploration with validation, and in the Exploration details you see a recommendation to switch to a new access path with validation. A key piece of data that can help to inform your decision is found in the New AP with ML Optimization and Validation column of the Latest access path exploration table.

   

   As Figure 1 shows, the Combined weighted delta % value for the New AP with ML optimization and validation column is a -82.7%. This means that, as compared to the baseline access path, the new access path with validation is expected to be significantly better (82.7% better) than the baseline.

   Once the recommended access path is deployed, you can check to see how the estimated improvement stacks up to actual performance of the access path. See step 10.

   Note: When you choose to explore without validation, in order to determine whether or not the recommendation should be deployed, you will need to closely examine the data in order to decide whether to switch to the new access path. A greater chance of regression may exist in this case, since the recommendation is made without validation.
10. The Access path performance graph and following Access path metrics table on the Statement details page provide a visual indication of the relative performance of current and previous access paths, in terms of average elapsed time per row and average CPU time per row.

    You'll notice that the rows of the access path metrics table are color-coordinated with the Access path performance graph. This is to make it easier to associate the data from the table row with the data shown in the graph.

    You can choose to view a complete list of access path history for the statement, or to view an aggregated list. Click Aggregate AP list to view the aggregated list. Click List all AP history to view the full list.

    To change the time range of the access path comparison, enter a new start time or date or a new end time or date and click Apply time range. Depending upon the time frame you choose, there may not be data available.

11. When an access path changes, you can choose to drill down to review the comparison details. In the AP performance comparison column of the Access path metrics table, click Details. The Access path performance comparison details table is displayed.

    As with the Access path performance graph and the Access path metrics table, the Access path performance comparison details table carries through the color-coding of the previous and current access paths.

    For a description of the data shown by the Access path performance comparison details table, see Migrating IBM Db2 AI for z/OS.

    Per the example discussed in step 8, you can check to see how the estimated improvement from a recommended access path performs once the access path has been deployed.

    

    In this case, the actual improvement (-75%) is slightly below the predicted improvement (-82%).

    The confidence % indicates the degree of confidence that Db2ZAI 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).

    Note: If the confidence level is below the confidence threshold, and the result is 'regressed,' Db2ZAI will not revert back to the previous access path. Instead, it will continue to compare the current access path to the previous and only revert the access path when the confidence level meets or exceeds the confidence threshold. This is to avoid inadvertently reverting back to an access path that isn't actually better than the current access path, as that can lead to unnecessary rebinds and a 'flip-flopping' of access paths which might be harmful.

12. To display the details for an access path (or paths, to view a comparison), in the Access path metrics table, click the checkbox next to the access path, then click Display AP details. The plan details are displayed following the SQL text.
13. You can choose to lock or unlock an access path for a particular dynamic or static SQL statement, or, for static SQL, all access paths in a package. You might, for example, want to do this when you are satisfied with the performance of the current access paths in a package and therefore do not want them to change.
    • To lock all access paths at the package level (static SQL):
      1. In the Static packages table on the SQL optimization dashboard, find the package whose statements you want to lock and click the Lock statements icon at the end of the table row. The Lock access paths dialog is displayed.
      2. Click Confirm to lock the access paths.
    • To unlock all access paths at the package level (static SQL):
      1. In the Static packages table on the SQL optimization dashboard, find the package whose statements you want to unlock and click the Unlock statements icon at the end of the row. The Unlock access paths dialog is displayed.
      2. Click Confirm to unlock the access paths.
    • To lock an access path for a particular static SQL statement:
      1. In the Static packages table on the SQL optimization dashboard, click the package name that contains the SQL statement you want to lock. The available SQL statements are displayed in the table following the Static packages table.
      2. Click the STMT_HASHID2 for the statement. The Statement details page opens.
      3. In the Access path metrics table, find the access path that you want to lock for the statement, and click the Action menu icon (︙) and select Lock. The Lock access path dialog opens.
      4. Click Confirm to lock the access path.
    • To unlock an access path for a particular static SQL statement:
      1. In the Static packages table on the SQL optimization dashboard, click the package name that contains the SQL statement you want to unlock. The available SQL statements are displayed in the table following the Static packages table.
      2. Click the STMT_HASHID2 for the statement. The Statement details page opens.
      3. In the Access path metrics table, find the access path to be unlocked, click the Action menu icon (︙) and select Unlock. The Unlock access path dialog opens.
      4. Click Confirm to unlock the access path.
    • To lock an access path for a dynamic SQL statement:
      1. In the Dynamic statements table on the SQL optimization dashboard, find the statement and click the STMT_HASHID2. The Statement details page opens.
      2. In the Access path metrics table, find the access path to be locked, click the Action menu icon (︙) and select Lock. The Lock access path dialog opens.
      3. Click Confirm to lock the access path.
    • To unlock an access path for a dynamic SQL statement:
      1. In the Dynamic statements table on the SQL optimization dashboard, find the statement and click the STMT_HASHID2. The Statement details page opens.
      2. In the Access path metrics table, find the access path to be unlocked, click the Action menu icon (︙) and select Unlock. The Unlock access path dialog opens.
      3. Click Confirm to unlock the access path.
14. You can choose to exclude a static SQL package from Db2ZAI processing, or to include a package that had previously been excluded.
    • To exclude a package:
      1. In the Static packages table on the SQL optimization dashboard, find the package that you want to exclude.
      2. Click the Exclude package icon at the end of the table row. The Exclude package dialog is displayed.
      3. Click Confirm to exclude the package.

    • To include a package:
      1. In the Static packages table on the SQL optimization dashboard, find the package that you want to include.
      2. Click the Include package icon at the end of the table row. The Include package dialog is displayed.
      3. Click Confirm to include the package.