Which of the following identifies and creates an index to minimize the DB time for a particular SQL statement?
A.
The SGA Tuning Advisor
B.
The SQL Access Advisor
C.
The SQL Tuning Advisor
D.
The Memory Advisor
Explanation:
The SQL Access Advisor allows the DBA to gather global recommendations for a workload. The SQL Tuning advisor is more granular, tuning a single statement.
The main functions of the SQL Access advisor is to recommend missing indexes and materialized views, but a comprehensive task analysis will also create SQL Profiles that can be used within the SQL Tuning advisor.
The DBA defines the SQL used in the SQL Access Advisor task, and can choose current SQL, a user-defined set of SQL, a historical workload, or a hypothetical workload.
A hypothetical workload is very useful because the DBA need-only specify the tables that participate in the queries, and the SQL Access Advisor gathers the appropriate SQL statements to create the workload.
http://www.dba-oracle.com/oracle10g_tuning/t_sql_access_advisor.htm
The SQL Tuning Advisor (STA) is primarily designed to replace the manual tuning of SQL statements and speed up the overall SQL tuning process. The SQL Tuning Advisor studies poorly executing SQL statements and evaluates resource consumption in terms of CPU, I/O, and temporary space.
The SQL Tuning Advisor (STA) works with the Automatic Tuning Optimizer (ATO) to analyze historical SQL workload using data from the AWR, and it generates recommendations for new indexes and materialized views that will reduce the disk I/O associated with troublesome SQL statements.
http://www.dba-oracle.com/oracle10g_tuning/t_sql_tuning_advisor.htm
The correct answer is A because SQL Access Advisor only as this is the tool designed to generate advise specifically for Index.
Brajmohan is wrong, the given answer C is correct.
SQL Access Advisor does not advise on a single SQL statement, as is the question. SQL Access Advisor instead analyzes general workload access to a table.
Reference:
http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm#i37689
Section 17.2
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, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.