Databases Reference
In-Depth Information
Here is the output for this database:
ADVISOR_NAME LAST_EXEC NUM_DB_REPORTS
------------------------- --------- --------------
ADDM 13-MAY-11 0
Compression Advisor 13-MAY-11 0
SQL Access Advisor 18-AUG-11 0
SQL Performance Analyzer 13-MAY-11 0
SQL Repair Advisor 13-MAY-11 0
SQL Tuning Advisor 13-MAY-11 1
SQL Workload Manager 13-MAY-11 0
Segment Advisor 18-AUG-11 0
Tune MView 13-MAY-11 0
Undo Advisor 13-MAY-11 0
Table 10-3. Common Advisor View Descriptions
View
Description
DBA_ADVISOR_TASKS
Displays task name, owner, associated advisor, and execution
information.
DBA_ADVISOR_LOG
Shows current status of tasks in the database.
DBA_ADVISOR_FINDINGS
Findings discovered by advisors.
DBA_ADVISOR_RECOMMENDATIONS
Results and analysis of recommendations from advisors.
DBA_ADVISOR_USAGE
Usage information for each type of advisor.
Summary
The SQL Access Advisor tool can be invoked to tune one SQL statement or a group of statements. This
tool provides advice regarding the creation of indexes, materialized views, materialized view logs, and
partitioning strategies. Because one of the main outputs of this tool is index creation advice, we decided
it should be covered in this topic on indexing strategies.
The
DBMS_ADVISOR.QUICK_TUNE
procedure specifically provides tuning advice for a single query. If
you are required to tune a collection of SQL statements, use the Enterprise Manager SQL Access Advisor
Wizard. This tool allows you to efficiently choose from a vast set of SQL Access Advisor options when
tuning a group of SQL statements. As part of its output, you can view the PL/SQL code that is used to
invoke the SQL Access Advisor. This allows you to manually adjust the code if you require more fine-
grained control over the various features.
Keep in mind the SQL Access Advisor considers the impact an index might have on the entire
workload. If an index speeds up one query but slows down several other statements in the group, then
an index may not be recommended. This is different from the behavior of the SQL Tuning Advisor
(Chapter 9). The SQL Tuning Advisor recommends indexing advice on isolated SQL statements without
consider the influence an index might have on other SQL statements in the system.
