Friday, December 7, 2012

AWR vs ADDM vs ASH

AWR : automatic workload repository

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

1 comment: