Performance Tuning

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
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).