ADDM and Query for recommendations of the last ADDM run

ADDM
=====
=====
Once the AWR snapshot is taken, the ADDM analysis occurs automatically as long as the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The new Oracle Database 10g background process MMON performs the ADDM analysis. The results of the ADDM run is stored in the AWR, and can be accessed through various means such as OEM, manual query, or an Oracle supplied API.
One of the parameters that ADDM uses to perform its analysis is DBIO_EXPECTED. This parameter is not set in the database parameter file, but is rather set using the Oracle supplied package DBMS_ADVISOR. This parameter defines the response time expected by Oracle from the disk I/O system, and the value defaults to 10 milliseconds. If you have slower disks, you will want to reset this value. In the following example, we have reset the expected disk response time to 20 milliseconds, since that is our measured response time from our disks:

Exec dbms_advisor.set_default_task_parameter(’ADDM’, -’DBIO_EXPECTED’, 20000);

ADDM Analysis Goals
=====================
ADDM’s goal is to improve the value of a statistic called db time. Db time is a value that Oracle calculates to indicate the cumulative time that is spent processing user requests. ADDM’s goal is to reduce the overall db time value, and it does not target individual users or user response times, and thus the db time it uses is an aggregate value of overall system CPU and wait times You can see the current value of db time for the entire system by querying the V$SYS_TIME_MODEL or you can see it for a given session by using the V$SESS_TIME_MODEL view as seen here:

select sum(value) "DB time" from v$sess_time_modelwhere stat_name='DB time';
DB time


Memory-related issues such as
========================
shared pool latch contention, log buffer issues, or database buffer cache related problems
CPU bottlenecks
Disk I/O performance issues
Database configuration problems
Space-related issues, such as tablespaces running out of space
Application and SQL tuning issues such as excessive parsing and excessive locking

ADDM Analysis Results

The results of the ADDM analysis are stored in the AWR in the form of findings. There are three different kinds of findings:

Problem — A problem indicates a root cause problem that is causing a database performance problem.
Symptom — A symptom indicates a performance issue that normally points to one or more specific problem findings.
Information — This is just basic database-related information that is not related to a specific problem.

The recommendations of the last ADDM run, you can execute this query:
==================================================================


SELECT a.execution_end, b.TYPE, b.impact, d.RANK, d.TYPE,
'Message : ' || b.MESSAGE MESSAGE,
'Command To correct: ' || c.command command,
'Action Message : ' || c.MESSAGE action_message
FROM dba_advisor_tasks a,
dba_advisor_findings b,
dba_advisor_actions c,
dba_advisor_recommendations d
WHERE a.owner = b.owner
AND a.task_id = b.task_id
AND b.task_id = d.task_id
AND b.finding_id = d.finding_id
AND a.task_id = c.task_id
AND d.rec_id = c.rec_id
AND a.task_name LIKE 'ADDM%'
AND a.status = 'COMPLETED'
ORDER BY b.impact, d.RANK;