Tuesday, June 7, 2016

How to read an AWR report - How to generate an AWR report with sqlplus?

Generate AWR report with sqlplus:

Connect to sqlplus and run the following command:
sqlplus  @$ORACLE_HOME/rdbms/admin/awrrpt.sql

A few information will be displayed regarding the system.

AWR reports generation works with snapshot ids, so to know that information, we are prompted for a number of days to display them. If there was a problem yesterday, mention 2 days for more snap id:

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

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
dw02         DW02             29150 03 Jun 2016 00:00      1
                              29151 03 Jun 2016 00:30      1
                              29152 03 Jun 2016 01:00      1
                              29153 03 Jun 2016 01:30      1
                              29154 03 Jun 2016 02:00      1
                              29155 03 Jun 2016 02:30      1
                              29156 03 Jun 2016 03:00      1
                              29157 03 Jun 2016 03:30      1
                              29158 03 Jun 2016 04:00      1
                              29159 03 Jun 2016 04:30      1
                              29160 03 Jun 2016 05:00      1
                              29161 03 Jun 2016 05:30      1
                              29162 03 Jun 2016 06:00      1
                              29163 03 Jun 2016 06:30      1
                              29164 03 Jun 2016 07:00      1
                              29165 03 Jun 2016 07:30      1
                              29166 03 Jun 2016 08:00      1
                              29167 03 Jun 2016 08:30      1
                              29168 03 Jun 2016 09:00      1

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

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

Finally, the script prompts for the report name. Do mention a full path, otherwise it will get generated in the current working directory which will be $ORACLE_HOME/rdbms/admin. Additionally, snapids become quickly meaningless when looking if there's a need to search for that report again a few days later. I prefer a format with a full date start time and end time.

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

Enter value for report_name: /home/oracle/awrrpt_20160603_0830_0900.html

Following this, the report is generated and we can look at it.

No comments :

Post a Comment