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:
...
...
Smart way of Technology 