Through the SQL Tuning Advisor, information ADDM
has analyzed from the AWR is used to identify
high load or poorly performing SQL statements.
ADDM also knows about the top SQL and its impact
on the system. Once a statement is identified,
then ADDM can pass the statement into another
analyzer, so to speak, and come up with
recommendations. The SQL Tuning Advisor is the
engine behind the scenes and it develops finer
tuned plans along with some advice.
The recommendations fall into four categories:
1.
Statistics Analysis – The advisor checks the
status of statistics (stale or missing) and if
necessary, gathers new or updated statistics.
2.
SQL Profiling – What was the past execution
history like? Profiling can use the history of a
statement to generate a well-tuned plan. This is
especially useful for applications where the DBA
has no control over the code. What can be done
is, via the advisor, build a better plan around
the code.
3.
Access Path Analysis – This is a lot like what
performance tuning in SQL Server does. If it is
determined that an index will help a statement,
the index will be automatically created. In
Oracle, a recommendation will be received to
build the index for it is not built for the DBA.
4.
SQL Structure Analysis – This component of the
advisor identifies bad plans and offers
suggestions as to restructuring them.
Restructuring suggestions include both syntactic
and semantic changes to code.
The APIs for this advisor are discussed after
the next section.
SQL Access Advisor
Right behind and alongside the SQL Tuning
Advisor is the SQL Access Advisor. The former
helps fix or tune statements. The second offers
analysis based on recommending indexes,
partitions, and materialized views. The Access
Advisor works not only on single statements, but
also on complete business workloads.
One of the features of SQL Access Advisor is
that what is gathered on a production system can
be transferred to another system.
The SQL Tuning Advisor and SQL Access Advisor
command-line APIs are accessed via the
DBMS_SQLTUNE and
DBMS_ADVISOR PL/SQL built-ins. The
sqltrpt.sql script in the rdbms/admin directory
is also part of the licensed Tuning Pack. Access
to
V$SQL_MONITOR and V$SQL_PLAN_MONITOR
are as well.
In Oracle 11g, a new initialization parameter
helps to control access to Tuning Pack and
Diagnostics Pack features. The parameter
CONTROL_MANAGEMENT_PACK_ACCESS can
be set to:
-
DIAGNOSTIC+TUNING – the features from both
packs are enabled
-
DIAGNOSTIC – only the features from the
Diagnostic Pack are enabled
-
NONE – features from both packs are disabled
The settings can also be controlled or accessed
via Enterprise Manager (see the Setup link).
WARNING: The default setting is
for DIAGNOSTIC+TUNING. If during
an audit Oracle License
Management determines that
licensed features have been used
above and beyond reasonable use,
such as for education, research
or demonstration, be prepared to
pay the consequences!
|
Having a hard
time reading this sentence.
Are we saying that
transferring gathered queries/stats
from production to a test or tuning
environment is a critical feature of
the SQL Access Advisor?
 |
For more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
