Generating the AWR Report

This chapter focuses on the skills required for reading and interpreting an Automated Workload Repository (AWR) report (awrrpt.sql), which is very similar to the STATSPACK elapsed-time report.  The AWR elapsed-time report (awrrpt.sql)contains valuable information regarding the health of the Oracle instance, but considerable skill is required to fully understand and interpret each section.

The easy way to generate an AWR report from from the SQL*Plus command line:

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

Generating the AWR Report

The procedure for creating a standard report provided by the STATSPACK utility in previous Oracle releases has already been introduced.  The only way to get a final STATSPACK report is to manually run the spreport.sql script in an ad-hoc SQL environment like SQL*Plus. The procedure of report generation in Oracle10g is quite different with AWR. The final AWR report can be built by using the PL/SQL API provided in the dbms_workload_repository package.

Two procedures that generate AWR reports from awrrpt.sql are awr_report_textand awr_report_html .  These procedures generate the AWR report for the specified snapshot range in TEXT or HTML formats, respectively.  The following script shows one way of retrieving the AWR text report for the particular snapshot range:

SELECT

   output 

FROM   

   TABLE

   (dbms_workload_repository.awr_report_text

      (37933856,1,2900,2911 )

   );

You can create snap using the below
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Below query help to find snaps information
select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME desc;