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.
About this task
The Query Advisor looks for opportunities in a query to
make the following improvements:
- Minimize the number of index pages and data rows that have to
be read. For example, you can minimize the number of rows read by
having predicates in the query that can determine the needed rows
from just the index alone.
- Minimize sort operations. For example, the advisor determines
whether ORDER BY or GROUP BY clauses are needed in the query, or if
sort operations can be resolved via index access.
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:
- 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.
- Click the Select What
to Run button.
- 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.
- 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.
- In the Query revision section, view
the underlined portion of the SQL statement. The recommendation refers
to the underlined portion.
- 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.
- Use the SQL editor or another tool to follow the instructions
that the advisor gives for resolving the problem that led to the recommendation.
What to do next
Run the Access Path Advisor and then the Index Advisor.