Generating and acting on recommendations for revising SQL statements for better performance

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

Before you begin

About this task

The Query Advisor looks for opportunities in a query to make the following improvements:

After the Query Advisor generates recommendations for revising an SQL statement for better performance, you can review the recommendations, read explanations for the recommendations, and see examples of the recommendations put into practice.

Procedure

To generate recommendations for revising an SQL statement:

  1. After running the DDL scripts that were recommended by the Index Advisor, return to the Run Single-Query Advisors and Tools page of the Invoke section in the workflow assistant.
    Click the Invoke tab on the left side of the workflow assistant. If the Run Single-Query Advisors and Tools page is not open, click Run Advisors and Analysis Tools on the left side of the workflow assistant to open the page.
  2. Click the Select What to Run button.
  3. In the Select Activities window, select the Query revision check box. Then, click OK. After the 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.
  4. In the Review Single-Query Advisor Recommendations page, double-click the Query revision row in the Summary table. If this advisor has more than one recommendation, a row appears for each recommendation.
  5. In the Query revision section, view the underlined portion of the SQL statement. The recommendation refers to the underlined portion.
  6. Read the details of the recommendation, an explanation of the best practice behind the recommendation, and, if provided, an example on an implementation of the recommendation.
  7. Use the SQL editor or another tool to follow the instructions that the advisor gives for resolving the problem that led to the recommendation.

Feedback