Oracle Tips by Burleson
Oracle 10g Advisory Framework -
So far we have examined the details of the
workload repository. Now let us focus on the advisory framework
Oracle 10g introduces.
Advisors are the server components that provide
you with useful feedback about resource utilization and performance.
The most important of all advisors is the Automatic Database
Diagnostic Monitor (ADDM). ADDM does analysis of the system,
identifies problems and their potential causes, and comes up with
recommendations for fixing the problems. It can call all other
The main features of the ADDM are as follows:
ADDM runs automatically in the background
process MMON whenever a snapshot of in-memory statistics is
taken. ADDM does analysis of the statistics collected between
ADDM analysis results are written back to the
workload repository for further use.
ADDM uses the new wait and time statistics
model, where activities with high time consumption are analyzed on a
priority basis. This is where the big impact lies.
ADDM can also be invoked manually.
The results of the proactive analysis performed
by the ADDM module are posted to the workload repository and then
they are available to users through the OEM console or by other SQL
query means. ADDM analysis is also the driving point for Server
Alerts whenever the threshold is exceeded for the specified metrics.
The new OEM or Grid Control has many web pages devoted to displaying
Other advisors include:
SQL Tuning Advisor: This advisor is
responsible for providing tuning advice for a SQL statement. We have
covered this topic in detail in Chapter 2, Database Tuning and
SQL Access Advisor: This provides expert advice
on materialized views, indexes, and materialized view logs. A full
detailed account of its utility and usage is covered in Chapter 16,
Segment Advisor: This advisor is responsible
for space issues involving a database object. It analyzes the growth
trends. It can also extend advice on the shrink possibility for the
Undo Advisor: This advisor suggests parameter
values, and how much additional space would be needed to support
flashback for a specified time. However, most of the undo tuning
(like undo retention) is automatic in Oracle Database 10g.
Oracle 10g Advisory Framework - ADDM