How to generate AWR report from Oracle Database?

AWR (Automatic Workload repository) Report:

Automatic Workload Repository gathers, processes and maintains performance stats used for problem detection and self-tuning the databases. This collective information can be displayed as views and reports (HTML/text) to better show the current database performance status for further analysis.

AWR gathers the following data

• Object Statistics (access / usage stats of DB segments)

• Time Model Statistics (V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views)

• Some of the System and Session Statistics (V$SYSSTAT and V$SESSTAT views)

• ASH (Active Session History) Statistics

• High load generating SQL Statements

Different Components that uses AWR are:

• Automatic Database Diagnostic Monitor

• Undo Advisor

• SQL Tuning Advisor

• Segment Advisor

By default, Snapshots are generated every hour and retains the statistics in the repository for 8 days. Please find below if you intend to change the default settings:

BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 14400,

interval => 30, topnsql => 100, dbid => 12345678);

END;

/

Here ,

Retention = 14400 minutes (10 days) Snapshot retention period

Interval = 30 minutes Snapshot interval period

topnsql = Number of Top SQL to flush

dbid = database identifier

Different types of AWR Reports for different purposes:

For Single Instance Environment:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

For Oracle RAC Environment :

@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

For a particular SQL Statement :

@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

For Comparing the reports :

@$ORACLE_HOME/rdbms/admin/awrddrpt.sql

SAMPLE ACTIVITY:-

1) Login to DB Server as oracle 

2) export ORACLE_SID=PrimeDG

3) cd $ORACLE_HOME/rdbms/admin

4) sqlplus / as sysdba

5) @awrrpt.sql

(or)
@?/admin/awrrpt.sql

6) Enter value for report_type: html

Note: Since, we want to open the file in nicely formatted way, we pick html format.

7) Enter value for num_days: 7

Note: you can go back upto 7 days.

8) Enter value for begin_snap: 4697

9) Enter value for end_snap: 4704

10) Enter value for report_name: Test_AWR_Report.html

To pull the report, use WinSCP and pull the html file to you laptop. 

10) Start WinSCP and configure it connect to server with “oracle” username. 

11) Copy the file form UNIX (right side) to Windows (left side).

12) Double click the file to open the report in browser

Others:

Query to check the snapshot ID in last 24 hours.