How to Generate AWR ( Automatic Workload Repository ) Report via SQL*Plus, Enterprise Manager and Toad in Oracle

I will explain How to Generate AWR Report via SQL*Plus,Enterprise Manager and Toad in Oracle in this post.

AWR ( Automatic Workload Repository ) Report

Read previous article before this to learn What is the Oracle AWR report.

What is the Oracle AWR ( Automatic Workload Repository ) Report

Generate AWR Report

Before generate AWR report, you should list all snapshots. Because AWR report will be generated between from Two Snapshot. You can list AWR Snapshots by executing following command.

SQL> select snap_id, begin_interval_time,end_interval_time from dba_hist_snapshot;

You can generate Oracle AWR report by using Enterprise manager, SQL*Plus and TOAD etc.

SQL*Plus AWR Report Generate

Execute awrrpt.sql under $ORACLE_HOME/rdbms/admin directory like following.

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2514592975 DEVECI 1 DEVECI
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html  --Choose html type 

Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 2514592975 1 DEVECI DEVECI Dataguard1
Using 2514592975 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1     --- Choose num_days as 1 or more 


Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
DEVECI DEVECI 21626 10 Mar 2019 14:00 1
21627 10 Mar 2019 15:00 1
21628 10 Mar 2019 15:59 1
21629 10 Mar 2019 16:02 1
21630 10 Mar 2019 16:15 1
21631 10 Mar 2019 16:30 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 21626     -- Type begin Snap ID 


Begin Snapshot Id specified: 21626
Enter value for end_snap: 21630        -- Type End Snap ID 

Enter value for end_snap: 21630
End Snapshot Id specified: 21630
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_21626_21630.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: DEVECI_AWR_10032019.html    -- Type Report name, or Click enter for default name.


Report written to DEVECI_AWR_10032019.html
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

AWR Report has been generated under related directory like following.

bash-4.1$ ls -l *DEVECI*
-rw-r--r-- 1 oracle oinstall 427877 Mar 10 16:48 DEVECI_AWR_10032019.html
bash-4.1$

You can download this AWR report to check and analyze it via SFTP or SCP etc.

TOAD AWR Report Generate

You can generate AWR report via TOAD tool if you have TOAD DBA Licence like following steps.

Enterprise Manager Cloud Control AWR Report Generate

You can generate AWR report via Enterprise Manager Cloud Control  like following steps.

Do you want to learn How to Read or Analyze an AWR Report in Oracle, then read the following Tutorial series.

How to Read or Analyze an AWR Report in Oracle -4 | TOP SQL

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.