Oracle Creating an AWR Report

An easy way to generate an AWR report is:

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

The remaining procedures in the dbms_workload_repository package are awr_report_text andawr_report_html, which generate the AWR report for the specified snapshot range in text or HTML formats, respectively.  The following script segment shows how to retrieve the AWR text report for any snapshot range or duration:

SELECT
   output 
FROM    TABLE(dbms_workload_repository.awr_report_text (37933856,1,2900,2911 ));

The sample output below shows the typical report generated for AWR data.  The output displays shows the four arguments to the awr_report_text stored procedure:

      The database ID is 37933856.
      The instance number for RAC is 1.
      The starting snapshot number is 2900.
       The ending snapshot number is 2911.

This standard Oracle elapsed time report has evolved over the past 12 years and had several names:

report.txt: In Oracle7 and Oracle8, this BSTAT-ESTAT was taken by running the utlbstat.sqlfollowed by utlestat.sql in the $ORACLE-HOME/rdbms/admin directory.

 spreport: From Oracle8i to Oracle10g, this is an enhanced BSTAT-ESTAT report where the user chooses the beginning and ending snapshot numbers.

AWR Report: In Oracle 10g, this is the latest time-series report, and it is produced by running a SQL*Plus script in the $ORACLE_HOME/rdbms/admin directory.  awrrpt.sql is a text-based report.  awrrpti.sql is a HTML-based report for online publishing of time-series reports.

AWR  : awr_sql_report_text

Also see: these related AWR reports:

• awrinfo.sql
• awrddrpt.sql 
• awrrpti.sql 

OUTPUT
-----------------------------------------------------------------
WORKLOAD REPOSITORY report for
 
DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------
DBDABR          37933856 dbdabr              1 10.1.0.2.0  NO      Host1
 
              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      2900 19-Aug-04 11:00:29        18       5.2
  End Snap:      2911 19-Aug-04 22:00:16        18       4.6
   Elapsed:              659.78 (mins)
   DB Time:               10.08 (mins)
 
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache:      48M      Std Block Size:         8K
Shared Pool Size:  56M          Log Buffer:       256K
 
Load Profile
~~~~~~~~~~~~          Per Second       Per Transaction
                   --------------       -----------
        Redo size: 1,766.20             18,526.31
    Logical reads:    39.21                411.30
    Block changes:    11.11                116.54
   Physical reads:     0.38                  3.95
  Physical writes:     0.38                  3.96
       User calls:     0.06                  0.64
           Parses:     2.04                 21.37
      Hard parses:     0.14                  1.45
            Sorts:     1.02                 10.72
           Logons:     0.02                  0.21
         Executes:     4.19                 43.91

This is very similar to the old STATSPACK reports from Oracle9i, and it contains vital elapsed-time change information for what happened during the particular snapshot range.

More details on reading the standard AWR report are included later in this book.

The next step is to learn about the dbms_advisor package and see how it can yield important Oracle tuning insights.