Which three functions are performed by the SQL Tuning Advisor?

Which three functions are performed by the SQL Tuning Advisor?

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.

Show Hint

Leave a Reply 7

Your email address will not be published. Required fields are marked *

H*T*H

H*T*H

The given answer, ACE is wrong.
A is correct. The SQL Tuning Advisor can be configured to build a SQL Profile AND apply it automatically.
B is not correct. The recommendation of MVs and MV Logs is a function of the SQL Access Advisor.
C is correct. If stats are missing or are stale, SQL Tuning Advisor can recommend their (re)gathering.
D is correct. SQL Tuning Advisor can make index recommendations.
E is correct. It can also recommend the restructuring of a SQL statement. See this reference from Oracle:
http://docs.oracle.com/database/121/TGSQL/tgsql_sqltune.htm#TGSQL542

H*T*H

Reply

Ranieri

Ranieri

D is wrong. It doesn’t suggest BITMAP indexes – is a function of the SQL Access Advisor.
The correct is A, C and E

Reply

vaheed

vaheed

When running in tuning mode, the optimizer is known as the Automatic Tuning Optimizer.
The Automatic Tuning Optimizer performs the following types of tuning analysis:

Statistics Analysis
SQL Profiling
Access Path Analysis
SQL Structure Analysis
Alternative Plan Analysis

So. ACE is correct.

Reply

Amit

Amit

Agreed with Ranieri.

D is wrong. It doesn’t suggest BITMAP indexes – is a function of the SQL Access Advisor.
The correct is A, C and E

Reply