7 Days of Oracle: Day #7 - Generating AWR Reports

Last, but not least, I will show how to generate an AWR (Automatic Workload Repository) report within SQL*Plus.  The ADDM report attempts to guide a user to a solution where the AWR provides loads of data and leaves interpretation up to you.  Much like ADDM, Oracle supplies a script named 'awrrpt.sql' that will generate an AWR report when executed.

Steps to generate an AWR via SQL*Plus

1)      Find the location of the script on your server (found in */product/12.2.0.1/*/rdbms/admin)

2)      Execute the script in SQL*Plus

 

SQL> @/opt/oracle/base/product/12.2.0.1/demo/rdbms/admin/awrrpt.sql

 

3)      Provide the format you would like to use for the generated report (html/text/active-html)

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

 

4)      Enter the number of days you would like to report on.  If you just want less than a day, just select 1 and then you will be able to refine your selection further in the process

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

 

5)      Specify the snap_Id associated with the time you would like to start analyzing.

Listing the last day's Completed Snapshots

Instance     DB Name                Snap Id   Snap Started            Snap Level

------------ ------------                ----------   ------------------         ----------

 

DEMO     DEMODB                  71818      20 Oct 2018 00:00    1

                                                   71819      20 Oct 2018 00:30    1

                                                   71820      20 Oct 2018 01:00    1   

                                                .

                                                .                         

71860      20 Oct 2018 21:00    1

                                                   71861       20 Oct 2018 21:30    1

 

 

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 71859

Begin Snapshot Id specified: 71859

 

6)      Specify the snap_Id for the time you would like to end your analysis.

 

Enter value for end_snap: 71861

End   Snapshot Id specified: 71861

 

7)      Specify a file name, or hit enter to accept the default.

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_71859_71861.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

 

Enter value for report_name:

 

The results from an AWR report are far too immense to display the full output.  This report proves to be extremely valuable when trying to identify a performance issue, however,  the data provided takes time, understanding, and passion to master.   This blog was just meant to show you how to generate the report, so I will not be going into detail on how to read the report.  That could be a week-long training itself!  If you would like more information on AWR reports, you should check out Oracle documentation to learn more as well as check out some other blogs that are written by Oracle experts.

That concludes my 7 days of Oracle blogs.  I know these were very basic concepts for some, but these were meant to help those who stumble into Oracle much like I had when I was a younger DBA.  Thank you for taking the time to read the words I have thrown at you this last week!  I look forward to writing more and perhaps I’ll even write about my true love…. SQL Server J