 |
|
Oracle 10g SQL Access Advisor
Oracle Tips by Burleson Consulting |
The SQL Access Advisor Review
The final consolidation of the workload, options and scheduling
screen is now displayed in Figure 19.71. This figure shows a
summary of all the previous screens and presents the opportunity to
make changes before running the tuning task.
Figure 19.71:
The SQL
Access advisor session review screen.
Now that the workload is specified, the type and scope of the
analysis that scheduled the execution is chosen and the plan
reviewed, the output from the SQL Access Advisor session is now
ready for examination.
SQL Access Advisor Recommendations
The output of a SQL Access Advisor session is shown in the
recommendations screen as shown in Figure 19.72.
Figure 19.72:
The SQL
Access advisor recommendations screen.
This screen makes specific recommendations for the creation of
indexes and materialized views. It includes the definitions for
these new database entities.
The creation of a new index or materialized view may immediately
cause thousands of SQL statements to change their execution plans,
so special care is required before implementing the recommendations
in a production environment.
This section has been a quick review of the SQL Access Advisor to
show how it relates to the Enterprise Manager Advisor Central screen
and how the SQL Access advisor makes recommendations about an entire
pre-defined workload. The main points in this section were:
§
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 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.
§
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 following section presents the most powerful and intelligent of
all of the OEM advisory utilities, the memory advisors.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm
|
