Finding and fixing changes to access plans after deploying an application from a test system to a production system

You can find out whether the access plans for SQL statements on a test system remained the same when you deployed those SQL statements on a production system. If any of the access plans on the production system need improvement, you can tune the problematic SQL statements or revert to the access plans that are used on the test system.

Before you begin

About this task

In this procedure, you create a query workload of the SQL statements for an application that you are testing. You tune this query workload, and then deploy the application to a production system. Next, on that production system, you create a query workload of the same SQL statements. Finally, you compare the access plans that the SQL statements used on the test system with the access plans that the statements use on the production system. If there are detrimental changes to the access plans on the production system, you can correct the problems by creating and tuning a query workload of the problematic statements, or by reverting to the access plans from the test system.

Procedure

To identify and address changes to access plans due to deploying an application from a test system to a production system:

  1. In your test environment, create a query workload of the SQL statements that are in your application.
    1. Capture the SQL statements.
    2. After capturing SQL statements, click the Save All to Workload button.
    3. In the Input Workload Name and Description window, specify information for identifying the new query workload.
    The Manage and Tune Workloads page of the Manage section opens. The new query workload appears in the table on this page. The status of the query workload is CAPTURED.
  2. Run the Workload Statistics Advisor and implement its recommendations.
  3. Ensure that the local cache of the system catalog is current.
  4. Specify to re-explain the query workload when you run the Workload Index Advisor.
    1. On the left side of the workflow assistant, click the Invoke tab, if the Invoke section is not already open.
    2. On the Run Workload Advisors page, select the option Re-collect EXPLAIN information before running workload advisors.
  5. Run the Workload Statistics Advisor again to find out whether it has additional recommendations. If it does, implement them.
  6. If you implemented any additional recommendations from the Workload Statistics Advisor, ensure that the local cache of the system catalog is current.
  7. Specify to re-explain the query workload when you run the Workload Index Advisor.
    1. On the left side of the workflow assistant, click the Invoke tab, if the Invoke section is not already open.
    2. On the Run Workload Advisors page, select the option Re-collect EXPLAIN information before running workload advisors.
  8. Run the Workload Index Advisor and implement its recommendations.
  9. If the query workload is tuned to your performance requirements, deploy your application to your production system.
  10. In the Data Source Explorer, connect to your production system.
  11. Right-click the connection and select Analyze and Tune > Start Tuning.
  12. Create a query workload that is identical to or similar to the query workload that you created on your test system.
    1. Capture the SQL statements.
    2. After capturing SQL statements, click the Save All to Workload button.
    3. In the Input Workload Name and Description window, specify information for identifying the new query workload.
    The Manage and Tune Workloads page of the Manage section opens. The new query workload appears in the table on this page. The status of the query workload is CAPTURED.
  13. Explain the query workload.
    Note: If you captured SQL statements from the following sources and you chose to save EXPLAIN information when creating the query workload, you do not need to explain the query workload.
    • DB2 for Linux, UNIX, and Windows: ACTIVITIES event monitors, EXPLAIN tables, packages, the package cache
    • DB2 for z/OS: Catalog plans and packages, the dynamic statement cache
    1. On the Manage Workloads page, select the query workload and select Explain Selected Workload in the More actions field.
    2. In the Collect EXPLAIN Information window, specify optional values for the runtime environment of the SQL statements that are in the query workload.
  14. Return to the instance of the workflow assistant in which you are connected to the test system. If that instance is no longer open, in the Data Source Explorer connect to the system, right-click the connection, and select Analyze and Tune > Start Tuning.
  15. Click the Manage tab on the left side of the workflow assistant.
  16. On the Manage Workloads page, select the query workload that you created in step 1.
  17. Click the Compare button.
  18. Select the option Compare the access plans for the selected workload with the access plans for another workload.
  19. In the Compare Access Plans with Access Plans in Another Workload wizard, select the production system and click Next.
  20. Select the second query workload that you created and click Next.
  21. For both the query workload on the test system and the query workload on the production system, select an EXPLAIN snapshot to use in the comparison. Then, click Finish.

What to do next

On the Comparison History page, double-click the entry for the comparison. On the View Comparison Results by Workload EXPLAIN Snapshots and SQL Statements page, review the Statements table to look for statements with differences in the two EXPLAIN snapshots. If you need to share the results with other people, select Generate Comparison HTML Report in the More actions field above the table.

If you find that the access plans or estimated costs of the SQL statements on the production system need improvement, you can create a query workload from those statements. You can then tune the query workload. In the More actions field on the View Comparison Results by Workload EXPLAIN Snapshots and SQL Statements page, select Create a New Query Workload for Tuning.

A different approach would be to replace the access plans on the production system with the access plans from the test system. In the More actions field on the View Comparison Results by Workload EXPLAIN Snapshots and SQL Statements page, select Lock Down Access Plans for All SQL Statements. If you choose this approach, you must understand that changes to your system that could improve the access plans, such as improving statistics or applying maintenance that contains improvements to the optimizer, will have no effect on the access plans that you locked down. However, the access plans will not experience sudden changes and will execute predictably.


Feedback