AWR vs ADDM vs ASH

The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

I will not get into Details how to generate AWR since i mention it before on my Blog .

 ADDM : automatic database diagnostic monitor

analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks.and we use it for the following cases :

•      CPU bottlenecks
•      Undersized memory structures
•      I/O capacity issues
•      High load SQL statements
•      RAC specific issues
•      Database configuration issues
•      Also provides recommendations on hardware changes, database configuration & schema changes.

Generate ADDM  :

• Login to SQL
• @$ORACLE_HOME/rdbms/admin/addmrpt.sql
•  enter system password when you asked for .
• Specify a begin_snap from the list and press Enter.
• Specify the end_snap from the list and press Enter.
•  Report Name

ASH : Active Session History 

statistics from the in-memory performance monitoring tables also used to track session activity and simplify performance tuning.

ASH reports Give the following information :

• Top User Events (frequent wait events)
• Details to the wait events
• Top Queries
• Top Sessions
• Top Blocking Sessions
• Top DB Object.
• Activity Over Time

 Generate ASH reports :

The Best way to do that using OEM.  (Enterprise manager).

Thank you
Osama Mustafa