Which three statements are true the Automatic Tuning Optimizer (ATO)?

Question

Options

• AIt identifies the objects with stale or missing statistics and gathers statistics automatically.
• BIt investigates the effect of new or modified indexes on the access paths for a workload and recommends
• CIt recommends a SQL profile to help create a better execution plan.
• DIt picks up resource-intensive SQL statements from the ADDM and recommends the use of materialized
• EIt identifies the syntactic, semantic, or design problems with structure of SQL statements leading to poor
• FIt identifies resource-intensive SQL statements, runs them through the SQL Tuning Advisor, and implements

• A
  3% (1)
• B
  3% (1)
• C
  13% (4)
• D
  74% (23)
• E
  6% (2)

Explanation

Under tuning mode, the optimizer can take several minutes to tune a single statement. It is both time and resource intensive to invoke Automatic Tuning Optimizer every time a query must be hard- parsed. Automatic Tuning Optimizer is meant for complex and high-load SQL statements that have nontrivial impact on the database. Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements that are good candidates for SQL tuning (see Chapter 6, "Automatic Performance Diagnostics"). The automatic SQL tuning feature also automatically identifies problematic SQL statements and implements tuning recommendations during system maintenance windows as an automated maintenance task. The Automatic Tuning Optimizer performs the following types of tuning analysis: Statistics Analysis Access Path Analysis SQL Structure Analysis Alternative Plan Analysis * Oracle Database uses the optimizer to generate the execution plans for submitted SQL The optimizer operates in the following modes: The optimizer compiles the SQL and generates an execution plan. The normal mode generates a reasonable plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second. The optimizer performs additional analysis to check whether it can further improve the plan produced in normal mode. The optimizer output is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly better plan. When running in tuning mode, the optimizer is known as the Automatic Tuning Optimizer.

No community discussion yet for this question.