Generate the AWR Reports in Oracle

Generate the AWR Reports in Oracle

Basic three way to fetch AWR Reports:
1. AWRRPT.SQL and AWRRPTI.SQL:
Fetch the AWR report with: @?\rdbms\admin\awrrpt.sql;
2. AWRSQRPT.SQL and AWRSQRPI.SQL
Fetch the SQL ID report with: @?\rdbms\admin\awrsqrpt.sql;
3. AWRDDRPT.SQL and AWRDDRPI.SQL
Fetch the difference report with: @?\rdbms\admin\awrddrpt.sql;

AWRRPT.SQL
We need to generate the AWR report for fetching the performance data for specific interval. Show statistics for a range of snaphot ids.
During fetching we have to give some imput:
1. Report type: HTML or TEXT
2. Specify the number of days.
3. Specify the Begin and End Snapshot Ids.
4. Specify the Report Name.

Example:

SQL> @?\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: html

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: 2

Listing the last 2 days of Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

xe           XE                 483  06 Jan 2020 09:27    1
                                484  06 Jan 2020 10:30    1

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

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

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

Enter value for report_name: C:\reportawr.html

Using the report name C:\reportawr.html
...
...
...
Report written to C:\reportawr.html

AWRRPTI.SQL
It ask for two more parameter information database id and instance number.
Example:

SQL> @?\rdbms\admin\awrrpti.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: HTML
Type Specified: html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  2900437705     1      XE           xe           IXC1-LT8TBDW
* 2901846628     1      XE           xe           IXC1-LT8TBDW

Enter value for dbid: 2901846628
Using 2901846628 for database Id
Enter value for inst_num: 1
Using 1 for instance number
...
...

AWRSQRPT
It is used to see the statistics related to SQL ID which help to increase performance of particular SQL query.
You can find SQLID in range of AWR Report fetch from same snapshot with AWRRPT.SQL
Note: AWRSQRPTI.SQL same as AWRSQRPT but used with database id and instance number.

SQL> @?\rdbms\admin\awrsqrpt.sql

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
Type Specified:  html

Current Instance
~~~~~~~~~~~~~~~~
   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2901846628 XE                  1 xe

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  2900437705     1      XE           xe           IXC1-LT8TBDW
* 2901846628     1      XE           xe           IXC1-LT8TBDW

Using 2901846628 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: 2

Listing the last 2 days of Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------
xe           XE                 483  06 Jan 2020 09:27    1
                                484  06 Jan 2020 10:30    1


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

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

Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id:
...
...

AWRDDRPT
Compare the two awr reports and find difference between two different time snapshot with this command AWRDDRPT
Note: awrddrpi.sql same as awrddrpt.sql but used with specific Database id and instance number.

SQL> @?\rdbms\admin\awrddrpt.sql;

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

   DB Id       DB Id    DB Name      Inst Num Inst Num Instance
----------- ----------- ------------ -------- -------- ------------
 2901846628  2901846628 XE                  1        1 xe

Current Instance
~~~~~~~~~~~~~~~~
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  2900437705     1      XE           xe           IXC1-LT8TBDW
* 2901846628     1      XE           xe           IXC1-LT8TBDW

Database Id and Instance Number for the First Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 2901846628 for Database Id for the first pair of snapshots
Using          1 for Instance Number for the first pair of snapshots

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: 2

Listing the last 2 days of Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------
xe           XE                 483  06 Jan 2020 09:27    1
                                484  06 Jan 2020 10:30    1
                                485  06 Jan 2020 11:30    1
                                486  06 Jan 2020 12:30    1
                                487  06 Jan 2020 13:30    1
                                488  06 Jan 2020 14:30    1


Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 483
First Begin Snapshot Id specified: 483

Enter value for end_snap: 484
First End   Snapshot Id specified: 484

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  2900437705     1      XE           xe           IXC1-LT8TBDW
* 2901846628     1      XE           xe           IXC1-LT8TBDW

Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 2901846628 for Database Id for the second pair of snapshots
Using          1 for Instance Number for the second pair of snapshots

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_days2: 2

Listing the last 3 days of Completed Snapshots
                                482  04 Jan 2020 09:59    1
                                483  06 Jan 2020 09:27    1
                                484  06 Jan 2020 10:30    1
                                485  06 Jan 2020 11:30    1
                                486  06 Jan 2020 12:30    1
                                487  06 Jan 2020 13:30    1
                                488  06 Jan 2020 14:30    1


Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 487
Second Begin Snapshot Id specified: 487

Enter value for end_snap2: 488
Second End   Snapshot Id specified: 488

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

Enter value for report_name:
...
...

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.