Home » Oracle » 1z0-117 » Which three statements are true the Automatic Tuning Optimizer (ATO)?
Which three statements are true the Automatic Tuning Optimizer (ATO)?
A. It identifies the objects with stale or missing statistics and gathers statistics automatically.
B. It investigates the effect of new or modified indexes on the access paths for a workload and recommends running that statistics through the SQL Access Advisor.
C. It recommends a SQL profile to help create a better execution plan.
D. It picks up resource-intensive SQL statements from the ADDM and recommends the use of materialized views to improve query performance.
E. It identifies the syntactic, semantic, or design problems with structure of SQL statements leading to poor performance and suggests restricting the statements.
F. It identifies resource-intensive SQL statements, runs them through the SQL Tuning Advisor, and implements the recommendations automatically.
Correct Answer: ADF
Explanation/Reference:
----MI DAVA D,F----
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
SQL Profiling
Access Path Analysis
SQL Structure Analysis
Alternative Plan Analysis
Note:
*Oracle Database uses the optimizer to generate the execution plans for submitted SQL statements. The optimizer operates in the following modes:
Normal mode
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.
Tuning mode
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.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
