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
- Active Session History (ASH) statistics from the
- Some system and session statistics from the
- 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
- 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).