How can I obtain an AWR Report from the Oracle Database?

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

AWR Reports can be used to troubleshoot performance issues and find offending queries that may be causing performance problems.

How can I obtain an AWR Report from the Oracle Database? 

 

 

Environment

Release: ESPCLA99000-13.3-Clarity-Extended Support Plus
Component:

Resolution

Below are the steps to generate an AWR Report:

 

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

 

- 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.
The remaining procedures in the dbms_workload_repository package are awr_report_text and awr_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 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.