ADDM Reports   Apr 11, 2008

ADDM reports are available through Oracle Enterpirse Manager Dashboard.

ADDM is a self-diagnostic engine built into the Oracle Database 10g kernel, ADDM automatically detects and diagnoses common performance problems, including:


bulletHardware issues related to excessive I/O
bulletCPU bottlenecks
bulletConnection management issues
bulletExcessive parsing
bulletConcurrency issues, such as contention for locks
bulletPGA, buffer-cache, and log-buffer-sizing issues
bulletIssues specific to Oracle Real Application Clusters (RAC) deployments, such as global cache hot blocks and objects and interconnect latency issues

In certain sites, OEM might not be working. 

I find the following statement handy to find out the


set long 1000000
set pagesize 50000
column get_clob format a80
select dbms_advisor.get_task_report(
task_name, 'TEXT', 'ALL') 
as ADDM_report
from dba_advisor_tasks
	where task_id=(
	select max(t.task_id)
	from dba_advisor_tasks t, 			dba_advisor_log l
	  where t.task_id = l.task_id
	  and t.advisor_name='ADDM'
	  and l.status= 'COMPLETED');
FINDING 3: 5.2% impact (147 seconds)
The buffer cache was undersized causing significant additional read I/O.
RECOMMENDATION 1: DB Configuration, 5.2% benefit (147 seconds)
ACTION: Increase SGA target size by increasing the value of parameter "sga_target" by 24 M.
Wait class "User I/O" was consuming significant database time. (5.3% impact [150 seconds])

To run a new snapshot of statistics required by ADDM (AWR, Automatic Workload Repository)

SQL> exec dbms_workload_repository.create_
PL/SQL procedure successfully completed.