Which three functions are performed by the SQL Tuning Advisor?
A.
Building and implementing SQL profiles
B.
Recommending the optimization of materialized views
C.
Checking query objects for missing and stale statistics
D.
Recommending bitmap, function-based, and B-tree indexes
E.
Recommending the restructuring of SQL queries that are using bad plans
Explanation:
The SQL Tuning Advisor takes one or more SQL statements as an input and
invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of
the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for
each recommendation and its expected benefit. The recommendation relates to collection of
statistics on objects (C), creation of new indexes, restructuring of the SQL statement (E), or
creation of a SQL profile (A). You can choose to accept the recommendation to complete the
tuning of the SQL statements.
D is incorrect as bitmap, function-based, and B-tree indexes are advised by SQL Access Advisor.
So correct answer is ACE.
ACE
A.
Building and implementing SQL profiles
C.
Checking query objects for missing and stale statistics
E.
Recommending the restructuring of SQL queries that are using bad plans
ACE – OK
Tuning recommendations include:
Collection of object statistics
Creation of indexes
Rewriting SQL statements
Creation of SQL profiles
Creation of SQL plan baselines
https://docs.oracle.com/database/121/TGSQL/tgsql_sqltune.htm#TGSQL542
A) argh .. I didn’t know an Advisor could implement an SQL Profile, I thought the Advisor only recommended it.
meaning answer A is not correct, but it is correct because BD definitely are not
ACE
ACE
A,C,E
The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile.
Optionally implement the SQL profiles provided they meet the criteria of threefold performance improvement.
Note that other factors are considered when deciding whether or not to implement the SQL profile. For example, a SQL profile is not implemented if the objects referenced in the SQL statement have stale optimizer statistics. You can identify which SQL profiles have been implemented automatically as their type will be set to AUTO in the DBA_SQL_PROFILES view.
A, C, E Correct