Generating and acting on recommendations for revising SQL statements in query workloads for better performance

The Workload Query Advisor uses a set of rules and best-practices to find structures in SQL statements that are likely cause the optimizer to choose suboptimal access paths. You can rewrite the statements to resolve the problems that are identified in the recommendations.

Before you begin

About this task

The Workload Query Advisor looks for opportunities in a query to do the following:

Procedure

  1. In the Manage section, open the Manage and Tune Workloads page.
  2. Select the query workload and click the Invoke Advisors and Tools icon, which is on the left end of the toolbar above the list of query workloads. The Run Workload Advisors page of the Invoke section opens.
  3. If you are running the Workload Query Advisor after running the RUNSTATS commands that the Workload Statistics Advisor recommended and the DDL statements that the Workload Index Advisor recommended, ensure that the Re-explain the query workload check box is selected. Now that the statistics for the SQL statement are accurate and index recommendations are in place, the workflow assistant must update the EXPLAIN information for statement.
  4. Click the Select What to Run button.
  5. In the Select Activities window, select the Query revisions check box and click OK. After the Workload Query Advisor runs, the Review Advisor Recommendations page of the Review section opens. The Summary section shows whether there are any recommendations for query revisions.
  6. In the Review Workload Advisor Recommendations page, click the Query revisions tab if there are new recommendations for revisions.
  7. Select one of the rows of the table and click the View Queries icon.
  8. In the Workload Query Advisor Details section, select one of the statements and click the Show Query Details icon.
  9. Follow the recommendations that appear for the selected SQL statement.
  10. Follow steps 6 through 10 until all of the recommendations are implemented.

Feedback