 |
|
Automatic Database
Diagnostic Monitor (ADDM)
Oracle Tips by Burleson Consulting
|
The following Tip is from the
outstanding book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by
Dr. Tim Hall, Oracle ACE of the year, 2006:
The Automatic Database Diagnostic Monitor (ADDM)
analyzes data in the Automatic Workload Repository (AWR) to identify
potential performance bottlenecks. For each of the identified issues,
it locates the root cause and provides recommendations for correcting
the problem. An ADDM analysis task is performed and its findings and
recommendations stored in the database every time an AWR snapshot is
taken provided the statistics_level parameter is set to TYPICAL or
ALL. The ADDM analysis includes:
The ADDM reports are much simpler to read than
those of AWR or STATSPACK, making them a useful method for identifying
resource intensive SQL and PL/SQL.
There are several ways to produce reports from
the ADDM analysis which will be explained later, but all follow the
same format. The findings (problems) are listed in order of potential
impact on database performance, along with recommendations to resolve
the issue and the symptoms which lead to its discovery. An example
finding is shown below.
FINDING 1: 59% impact (944 seconds)
-----------------------------------
The buffer cache was undersized causing significant additional read
I/O.
RECOMMENDATION 1: DB Configuration, 59%
benefit (944 seconds)
ACTION: Increase SGA target size by
increasing the value of parameter
"sga_target" by 28 M.
SYMPTOMS THAT LED TO THE FINDING:
Wait class "User I/O" was consuming
significant database time. (83%
impact [1336 seconds])
The recommendations for a particular finding
may include:
The analysis of I/O performance is affected by
the dbio_expected parameter which should be set to the average time
(in microseconds) it takes to read a single database block from disk
with typical values range from 5000 to 20000 microseconds.
EXECUTE
DBMS_ADVISOR.set_default_task_parameter('ADDM', 'DBIO_EXPECTED',
8000);
In Enterprise Manager DBConsole, the
"Performance Analysis" section on the "Home" page is a list of the top
five findings from the last ADDM analysis task. Specific reports can
be produced by clicking on the "Advisor Central" link, then the "ADDM"
link. The resulting page allows the selection of a start and end
snapshot, the creation of an ADDM task and the display of the
resulting report by clicking on a few links.
Alternatively, an ADDM report can be generated
from SQL*Plus using the addmrpt.sql script located in the $ORACLE_HOME/rdbms/admin
directory. When run, the script lists all available snapshots and
prompts the user to enter the start and end snapshot along with the
report name.
It is also possible to create and execute ADDM
advisor tasks using the dbms_advisor package, as shown below.
BEGIN
-- Create an ADDM task.
DBMS_ADVISOR.create_task (
advisor_name => 'ADDM',
task_name => '970_1032_AWR_SNAPSHOT',
task_desc => 'Advisor for snapshots 970 to 1032.');
-- Set the
start and end snapshots.
DBMS_ADVISOR.set_task_parameter (
task_name => '970_1032_AWR_SNAPSHOT',
parameter => 'START_SNAPSHOT',
value => 970);
DBMS_ADVISOR.set_task_parameter (
task_name => '970_1032_AWR_SNAPSHOT',
parameter => 'END_SNAPSHOT',
value => 1032);
-- Execute
the task.
DBMS_ADVISOR.execute_task(task_name => '970_1032_AWR_SNAPSHOT');
END;
/
-- Display
the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('970_1032_AWR_SNAPSHOT') AS report
FROM dual;
SET PAGESIZE 24
The value for the SET LONG command should be adjusted to allow
the whole report to be displayed.
The following views can be used to display the
ADDM output without using Enterprise Manager or the get_task_report
function:
-
dba_hist_snapshot - Lists all valid
snapshots.
-
dba_advisor_tasks - Basic information
about existing tasks.
-
dba_advisor_log - Status information
about existing tasks.
-
dba_advisor_findings - Findings
identified for an existing task.
-
dba_advisor_recommendations -
Recommendations for the problems identified by an existing task.
The nest section will explain how Enterprise
Manager relates to the previous sections.
Using Oracle Enterprise Manager
Over the last few versions of the database,
Oracle has pushed Oracle Enterprise Manager (OEM) as the main
administration tool for the database. Depending on the version used,
it provides either a Java GUI or HTML browser-based console that
allows simplified access to a number of the features discussed above.
I do not dislike Enterprise Manager, but it
does mask some detail of the underlying mechanisms. I prefer to
understand the technology, rather than be shielded from it.
Rather than launch into a lengthy discussion of the features that are
supported by each version of OEM, I will simply say that if you
understand the material discussed above, using OEM to access the
features will be simple and intuitive.
|
