How To generate Automatic Database Diagnostic Monitor (ADDM) Report In Oracle?

The ADDM accepts AWR snapshots as input and provides various findings and recommentations to resolve the performance bottlenecks.

ADDM reports include :

Top SQL Activities
CPU bottlenecks
Undersized memory allocations
Excessive parsing
I/O usage
Concurrency issues
Object contention

RAC and application issues

ADDM report can be generated by running addmrpt.sql script.

SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

A portion of an ADDM report is shown below :

Finding 6: Undersized SGA
Impact is 0 active sessions, 2.96% of total activity.
—————————————————–
The SGA was inadequately sized, causing additional I/O or hard parses.
The value of parameter “sga_target” was “700 M” during the analysis period.

   Recommendation 1: Database Configuration
   Estimated benefit is 0 active sessions, 2.96% of total activity.
   —————————————————————-
   Action
      Increase the size of the SGA by setting the parameter “sga_target” to 875 M.

   Symptoms That Led to the Finding:
   ———————————
      Hard parsing of SQL statements was consuming significant database time.
      Impact is 0 active sessions, 17.82% of total activity.
      Wait class “User I/O” was consuming significant database time.
      Impact is 0 active sessions, 10.54% of total activity.

Article by : Anju