Advisors provide you with useful feedback about resource utilization and performance for their respective server components. For example, the Memory Advisor provides a recommended value for the MEMORY_TARGET initialization parameter, which controls the total amount of memory used by the Oracle database instance.
By building
on the data captured in the AWR, the ADDM enables the Oracle database to
diagnose its own performance and determine how identified problems can be
resolved. ADDM runs automatically after each AWR statistics capture. It can
potentially call other advisors.
Here are
the major benefits that are provided by the advisor infrastructure:
· All
advisors use a uniform interface.
·All
advisors have a common data source and results storage by using the workload
repository.
Automatic
Database Diagnostic Monitor (ADDM)
The ADDM is
a server-based expert that reviews database performance every 60 minutes. Its
goal is to detect possible system bottlenecks early and recommend fixes before
system performance degrades noticeably.
Memory
Advisors
The Memory
Advisor is actually a collection of several advisory functions that help
determine the best settings for the total memory used by the database instance.
The System Global Area (SGA) has a set of advisors for the shared pool,
database buffer cache, Java pool, and streams pool. The Java pool and streams
pool advisors are not exposed on the EM Memory Advisor page. There is an
advisor for the Program Global Area (PGA). In addition to the advisory
functions, this page provides a central point of control for the large pool and
the Java pool.
Mean-Time-To-Recover
(MTTR) Advisor
Using the
MTTR Advisor, you set the length of time required for the database to recover
after an instance crash.
Segment
Advisor
This
advisor looks for tables and indexes that consume more space than they require.
The advisor checks for inefficient space consumption at the tablespace or
schema level and produces scripts to reduce space consumption where possible.
SQL
Access Advisor
This
advisor analyzes all SQL statements that are issued in a given period and
suggests the creation of additional indexes or materialized views that will
improve performance.
SQL
Tuning Advisor
This
advisor analyzes an individual SQL statement and makes recommendations for
improving its performance. Recommendations may include actions such as
rewriting the statement, changing the instance configuration, or adding
indexes. The SQL Tuning Advisor is not invoked directly. Instead, it is called
from within other tools (such as Top SQL or Top Sessions) to help optimize
high-impact SQL statements.
Undo
Management Advisor
With the
Undo Management Advisor, you can determine the undo tablespace size that is
required to support a given retention period.
Data
Recovery Advisor
This
advisor automatically diagnoses persistent data failures, presents repair
options to the user, and executes repairs at the user’s request. The purpose of
the Data Recovery Advisor is to reduce the mean time to recover (MTTR) and
provide a centralized tool for automated data repair.
SQL
Repair Advisor
You run the
SQL Repair Advisor after a SQL statement fails with a critical error
that
generates a problem in the Automatic Diagnostic Repository. The advisor
analyzes the statement and, in many cases, recommends a patch to repair the
statement. If you implement the recommendation, the applied SQL patch
circumvents the failure by causing the query optimizer to choose an alternative
execution plan for future executions. This is done without changing the SQL
statement itself.
