053试题 61 / 258 / 298 - SQL Tuning Advisor && SQL repair advisor

题目：

61.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

参考答案 D
解析
题目意思问，问那个选项的advisor分析单个sql语句，并且产生建议从而提高性能。
SQL Tuning Advisor。 所以选择D 。

参考文档：

16.4.2 SQL Tuning Advisor

SQL Tuning Advisor optimizes SQL statements that have been identified as high-load SQL statements. By default, Oracle Database automatically identifies problematic SQL statements and implements tuning recommendations using SQL Tuning Advisor during system maintenance windows as an automated maintenance task, searching for ways to improve the execution plans of the high-load SQL statements. You can also choose to run SQL Tuning Advisor at any time on any given SQL workload to improve performance. See "Tuning Reactively with SQL Tuning Advisor".

16.4.4 SQL Access Advisor

In addition to SQL Tuning Advisor, SQL Access Advisor provides advice on materialized views, indexes, and materialized view logs. SQL Access Advisor helps you achieve performance goals by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload. In general, as the number of materialized views and indexes and the space allocated to them is increased, query performance improves. SQL Access Advisor considers the trade-offs between space usage and query performance, and recommends the most cost-effective configuration of new and existing materialized views and indexes. See "Using SQL Access Advisor".

END

-- 2019-080-29 add

题目：

258.Which two statements are true regarding the SQL Repair Advisor? (Choose two.)
A. The SQL Repair Advisor can be invoked to tune the performance of the regressed SQL statements.
B. The SQL Repair Advisor can be invoked even when the incident is not active for a SQL statement crash.
C. The SQL Repair Advisor is invoked by the Health Monitor when it encounters the problematic SQL statement.
D. The DBA can invoke the SQL Repair Advisor when he or she receives an alert generated when a SQL statement crashes and an incident is created in the ADR.
 
参考答案 BD
解析
题目意思是，关于SQL repair advisor，那个说法是正确的。
SQL repair advisor并不是针对性能方面的，是针对sql语句运行时出现严重错误时候，调用sql repair advisor。A错误 。
SQL repair advisor 在EM上的support workbench上，不是通过health monitor运行的。C错误。

参考文档：

https://docs.oracle.com/cd/E11882_01/server.112/e25494/diag.htm#ADMIN12324

Repairing SQL Failures with the SQL Repair Advisor

In the rare case that a SQL statement fails with a critical error, you can run the SQL Repair Advisor to try to repair the failed statement.

This section covers the following topics:

• About the SQL Repair Advisor

• Running the SQL Repair Advisor

• Viewing, Disabling, or Removing a SQL Patch

About the SQL Repair Advisor

You run the SQL Repair Advisor after a SQL statement fails with a critical error. The advisor analyzes the statement and in many cases recommends a patch to repair the statement. If you implement the recommendation, the applied SQL patch circumvents the failure by causing the query optimizer to choose an alternate execution plan for future executions.

Running the SQL Repair Advisor

You run the SQL Repair Advisor from the Problem Details page of the Support Workbench. The instructions in this section assume that you were already notified of a critical error caused by your SQL statement and that you followed the workflow described in "Investigating, Reporting, and Resolving a Problem".

To run the SQL Repair Advisor:

1. Access the Problem Details page for the problem that pertains to the failed SQL statement.

   See "Viewing Problems with the Enterprise Manager Support Workbench" for instructions.

2. In the Investigate and Resolve section, under the Self Service tab, under the Resolve heading, click SQL Repair Advisor.

   
   Description of the illustration advisor_access1.gif

END

-- 2019-08-30 add

题目：

298.While tuning a SQL statement, the SQL Tuning Advisor finds an existing SQL profile for the statement that has stale statistics available.
What would the optimizer do in this situation?
A. It updates the existing SQL profiles with current statistics.
B. It makes the statistics information available to GATHER_STATS_JOB.
C. It initiates the statistics collection process by running GATHER_STATS_JOB.
D. It logs a warning message in the alert log so that the DBA can perform statistics collection manually.
 
参考答案 B
解析
在对sql语句进行调优的时候，调优向导发现一个存在的sql profile。有陈旧的统计信息。

参考文档：

https://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm#PFGRF94841

17.1.1 Statistics Analysis

The optimizer relies on object statistics to generate execution plans. If these statistics are stale or missing, then the optimizer does not have the necessary information it needs and can generate poor execution plans. The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and produces two types of output:

• Recommendations to gather relevant statistics for objects with stale or no statistics

  Because optimizer statistics are automatically collected and refreshed, this problem occurs only when automatic optimizer statistics collection is disabled. See "Managing Automatic Optimizer Statistics Collection".

• Auxiliary statistics for objects with no statistics, and statistic adjustment factor for objects with stale statistics

The database stores this auxiliary information in an object called a SQL profile.

END