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.
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
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.
+A
Correct answer should be A, C & E
A – incorrect.
ATO doesn’t gather statistics automatically. It only recommends doing this.
B,C,E correct for me.
In my opinion B,C,E is correct
i think answer is:
D, E, F
“Because the Automatic Tuning Optimizer does not analyze how its index recommendation can affect the entire SQL workload, it also recommends running SQL Access Advisor on the SQL statement along with a representative SQL workload. SQL Access Advisor looks at the impact of creating an index on the entire SQL workload before making recommendations.”, so B is not right.
CEF
Agree with Caesar so CEF.
C
Using SQL Tuning Advisor Automatic Tuning Optimizer make recommendations on SQL Profiles. It can automatically execute that profiles.
E
“The Automatic Tuning Optimizer identifies common problems with structure of SQL statements that can lead to poor performance. These could be syntactic, semantic, or design problems with the statement. In each of these cases the Automatic Tuning Optimizer makes relevant suggestions to restructure the SQL statements. ”
Database Performance Tuning Guide – 17 Automatic SQL Tuning
F
SQL Tuning Advisor gives recommendations on SQL Profiles and can execute them. It can not create recommended indexes or rebuild queries.
E and ?
I have a problem with this question.
A) It identifies the objects with stale or missing statistics [Automatic Tuning Optimizer] and gathers statistics automatically [but this is belong to Automatic Optimizer Statistics Collection].
Source: http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm#i37659
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.
Automatic Tuning Optimizer does not analyze how its index recommendation can affect the entire SQL workload – SQL Access Advisor do that.
Source: http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm#i37688
C) It recommends a SQL profile to help create a better execution plan.
It doesen’t recommend, it creates a profile of the SQL statement called a SQL Profile.
Source: http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm#i37675
D) It picks up resource-intensive SQL statements from the ADDM and recommends the use of materialized views to improve query performance.
Propably SQL Access Advisor (as part of Automatic SQL Tuning)
Source: http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_overview.htm#i35740
E) It identifies the syntactic, semantic, or design problems with structure of SQL statements leading to poor performance and suggests restricting the statements.
Yes, it is correct.
Source: http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm#i37689
F) It identifies resource-intensive SQL statements, runs them through the SQL Tuning Advisor, and implements the recommendations automatically.
It is role of Automatic SQL Tuning Advisor (sometime calls SQL Tuning Advisor, view source, previous point, 17.2 SQL Tuning Advisor, second paragraph).
Source: http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm#CHDIBFGA
For me Automatic Tuning Optimizer consists of:
– Statistics Analysis (no gather),
– SQL Profiling (create SQL Profile),
– Access Path Analysis (recommendation about indexes),
– SQL Structure Analysis (suggestion about syntactic, semantic, or design problems)
Source: http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm#i36217
and Automatic SQL Tuning consists of:
– Automatic Database Diagnostic Monitor (ADDM)
– SQL Tuning Advisor
– SQL Tuning Sets
– SQL Access Advisor
Source: http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_overview.htm#i35740
E true if
It identifies the syntactic, semantic, or design problems with structure of SQL statements
leading to poor performance and suggests REWRITING!!! the statements.
C true if
It CREATES a SQL profile to help create a better execution plan.
and
A true if
It identifies the objects with stale or missing statistics
NOT (gathers!!!)
ACE
All others not about ATO
link here
http://docs.oracle.com/cd/E11882_01/server.112/e41573/sql_tune.htm#PFGRF94844
cef