Which of the following advisors within the Oracle advisory framework will analyze a single SQL
statement and make recommendations for performance improvement?
A.
SQL Repair Advisor
B.
SQL Optimizer
C.
SQL Access Advisor
D.
SQL Tuning Advisor
Explanation:
Answer option D is correct.
The SQL Tuning Advisor takes one or more SQL statements and performs an analysis to
determine what can be done to improve the performance of the SQL statement(s).
Recommendations include rewriting the SQL statement, adding indexes, or even changing
initialization parameters or memory component sizes such as the SGA size or buffer cache.
The SQL Tuning Advisor is accessed via tools such as Top SQL, the Automatic SQL Tuning
Advisor, or the Top Sessions interface from within EM. In contrast, the SQL Access Advisor takes
a broader view of SQL tuning. It considers a larger subset of SQL statements, such as all SQL
statements run within a specified time period or from a SQL Tuning Set (STS). The SQL Access
advisor may recommend additional indexes as well as materialized views to improve performance.
The SQL Repair Advisor analyzes a SQL statement that causes a critical error and records the
results in the Automatic Diagnostic Repository (ADR). It may recommend a patch to fix the
problem, or may provide an alternate execution plan to avoid causing an error in the future. Other
advisors within the advisory framework include memory advisors that monitor usage of the SGA
and PGA and recommend optimal settings for these memory structures including the
substructures within them, such as the library cache and the large pool. Answer option A is
incorrect. The SQL Repair Advisor only makes recommendations for SQL statements that cause a
critical error in the ADR. Answer option B is incorrect. There is no such advisor such as the SQL
Optimizer, although the Oracle optimizer uses statistics to determine the best execution plan.
Answer option C is incorrect. The SQL Access Advisor analyzes groups of SQL statements that
run during the same time period, not one or two SQL statements in isolation as the SQL Tuning
Advisor.