Blog

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. 

set linesize 200

set pagesize 200

col Begin_interval_time format a30

col end_interval_time format a30

select

snap_ID,

Begin_interval_time,

end_interval_time

from

dba_hist_snapshot

where 

begin_interval_time > sysdate-1

order by

Begin_interval_time;

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.