 |
|
Oracle
Automatic Database Management
Oracle Tips by Burleson Consulting |
This
is an excerpt from "Oracle 10g New Features for Administrators" by
Ahmed Baraka.
Using the Automatic Database Diagnostic Monitor (ADDM)
The Automatic Workload Repository (AWR) is a
statistics collection facility that collects new performance
statistics in the form of a snapshot on an hourly basis and saves
the snapshots for seven days into SYSAUX before purging them.
The Automatic Database Diagnostic Monitor (ADDM)
is a new diagnosis tool that runs automatically every hour,
after the AWR takes a new snapshot. The ADDM uses the AWR
performance snapshots to locate the root causes for poor performance
and saves recommendations for improving performance in SYSAUX.
You can then go to the OEM Database Control to view
the results, or even view them from a SQL*Plus session with the help
of an Oracle-supplied SQL script.
Goal of the ADDM
ADD aims at reducing a key database metric called
DB time,
which stands for the cumulative amount of time (in milliseconds)
spent on actual database calls (at the user level);i.e. both the
wait time and processing time (CPU time).
Problems That the ADDM Diagnoses
• Configuration issues
• Improper application usage
• Expensive SQL statements
• I/O performance issues
• Locking issues
• Excessive parsing
• CPU bottlenecks
• Undersized memory allocation
• Connection management issues, such as excessive
logon/logoff statistics
The New Time Model
V$SYS_TIME_MODEL - This view shows time in terms of
the number of microseconds the database has spent on a specific
operation.
V$SESS_TIME_MODEL - displays the same information in
the session-level.
Automatic Management of the ADDM
The Manageability Monitor Process (MMON) process
schedules the automatic running of the ADDM.
Configuring the ADDM
You only need to make sure that the initialization
parameters STATISTICS_LEVEL is set to TYPICAL or ALL, in order for
the AWR to gather its cache of performance statistics.
Determining Optimal I/O Performance
Oracle assumes the value of the parameter (not
intialization parameter) DBIO_EXPECTED is 10 milliseconds.
SELECT
PARAMETER_VALUE
FROM DBA_ADVISOR_DEF_PARAMETERS
WHERE ADVISOR_NAME='ADDM'
AND PARAMETER_NAME='DBIO_EXPECTED'
If your hardware is significantly different, you can
set the parameter value one time for all subsequent ADDM executions:
DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM'
,'DBIO_EXPECTED', 8000);
Running the ADDM
MMON schedules the ADDM to run every time the AWR
collects its most recent snapshot.
To view the ADDM’s findings:
o Use the OEM Database Control
o Run the Oracle-provided script addmrpt.sql
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
