The Automatic Workload Repository (AWR) collects and maintains statistics of the database.

We can generate awr report for a particular time frame in the past using the script awrrpt.sql ( located under $ORACLE_HOME/rdbms/admin)

script – @$ORACLE_HOME/rdbms/admin/awrrpt.sql

conn / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type:
old   1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual
new   1: select 'Type Specified: ',lower(nvl('','html')) report_type from dual

Type Specified:  html

old   1: select '&&report_type' report_type_def from dual
new   1: select 'html' report_type_def from dual



old   1: select '&&view_loc' view_loc_def from dual
new   1: select 'AWR_PDB' view_loc_def from dual



Current Instance
~~~~~~~~~~~~~~~~
DB Id          DB Name        Inst Num       Instance       Container Name
-------------- -------------- -------------- -------------- --------------
 2719871177     TESTDB                      1 TESTDB         TESTDB

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
* 2719871177     1      TESTDB       TESTDB       sec60-1

Using 2719871177 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  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

TESTDB       TESTDB            4761  08 Sep 2017 00:00    1
                               4762  08 Sep 2017 01:00    1
                               4763  08 Sep 2017 02:00    1
                               4764  08 Sep 2017 03:00    1
                               4765  08 Sep 2017 04:00    1
                               4766  08 Sep 2017 05:00    1
                               4767  08 Sep 2017 06:00    1
                               4768  08 Sep 2017 07:00    1
                               4769  08 Sep 2017 08:00    1
                               4770  08 Sep 2017 09:00    1
                               4771  08 Sep 2017 10:00    1
                               4772  08 Sep 2017 11:00    1
                               4773  08 Sep 2017 12:00    1
                               4774  08 Sep 2017 13:00    1
                               4775  08 Sep 2017 14:00    1
                               4776  08 Sep 2017 15:00    1
                               4777  08 Sep 2017 16:00    1
                               4778  08 Sep 2017 17:00    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 4776
Begin Snapshot Id specified: 4776

Enter value for end_snap: 4777
End   Snapshot Id specified: 4777



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_4776_4777.html.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: 1hrreport.html

For rac databases, the script awrrpt.sql script need to be run again each instance.

 

For NON-SYSDBA USERS, BELOW GRANTS ARE REQUIRED TO GENERATE AWR REPORT:

SQL> grant connect,SELECT_CATALOG_ROLE to support_id;

SQL>  grant execute on dbms_workload_repository to support_id;