Database report .

There are 3 Oracle database report which help us to manager or  dig issue .

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 :