Which three functions are performed by the SQL Tuning Advisor? (Choose three.)
A.
Building the SQL profile
B.
Recommending optimization of materialized views
C.
Checking query objects for missing and stale statistics
D.
Recommending bitmap, function-based, and B-tree indexes
E.
Recommending restructuring SQL queries that are using bad plans
Explanation:
SQL Tuning Advisor Automatic SQL tuning is exposed through SQL Tuning Advisor.
SQL Tuning Advisor runs automatically during system maintenance windows as a maintenance
task. During each automatic run, the advisor selects high-load SQL queries in the database and
generates recommendations for tuning these queries.
SQL Tuning Advisor recommendations fall into the following categories:
– Statistics analysis
– SQL profiling
– Access path analysis
– SQL structure analysis
A SQL Profile contains additional statistics specific to a SQL statement and enables the optimizer
to generate a better execution plan. Essentially, a SQL profile is a method for analyzing a query.
Both access path and SQL structure analysis are useful for tuning an application under
development or a homegrown production application.
A principal benefit of SQL Tuning Advisor is that solutions come from the optimizer rather than
external tools (see “Overview of the Optimizer” on page 7-10). Thus, tuning is performed by the
database component that is responsible for the execution plans and SQL performance. The tuningprocess can consider past execution statistics of a SQL statement and customizes the optimizer
settings for this statement.
http://www.remote-dba.net/oracle_10g_new_features/tuning_advisor.htm
How the SQL Tuning Advisor Works:
The optimizer will work in the new tuning mode wherein it conducts an in-depth analysis to come
up with a set of recommendations, the rationale for them and the expected benefit if you follow the
recommendations. When working in tuning mode, the optimizer is referred to as the Automatic
Tuning Optimizer (ATO).