Generating performance reports with Automatic Workload Repository (AWR)

To gather performance data and generate reports, Oracle recommends Automatic Workload Repository (AWR). AWR requires Oracle Database Enterprise Edition and a license for the Diagnostics and Tuning packs. To enable AWR, set the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter to either DIAGNOSTIC or DIAGNOSTIC+TUNING.

Working with AWR reports in RDS

To generate AWR reports, you can run scripts such as awrrpt.sql. These scripts are installed on the database host server. In Amazon RDS, you don't have direct access to the host. However, you can get copies of SQL scripts from another installation of Oracle Database.

You can also use AWR by running procedures in the SYS.DBMS_WORKLOAD_REPOSITORY PL/SQL package. You can use this package to manage baselines and snapshots, and also to display ASH and AWR reports. For example, to generate an AWR report in text format run the DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT procedure. However, you can't reach these AWR reports from the AWS Management Console.

When working with AWR, we recommend using the rdsadmin.rdsadmin_diagnostic_util procedures. You can use these procedures to generate the following:

The rdsadmin_diagnostic_util procedures save the reports to the DB instance file system. You can access these reports from the console. You can also access reports using the rdsadmin.rds_file_util procedures, and you can access reports that are copied to Amazon S3 using the S3 Integration option. For more information, see Reading files in a DB instance directory and Amazon S3 integration.

You can use the rdsadmin_diagnostic_util procedures in the following Amazon RDS for Oracle DB engine versions:

For a blog that explains how to work with diagnostic reports in a replication scenario, see Generate AWR reports for Amazon RDS for Oracle read replicas.

Common parameters for the diagnostic utility package

You typically use the following parameters when managing AWR and ADDM with the rdsadmin_diagnostic_util package.

You typically use the following parameters when managing ASH with the rdsadmin_diagnostic_util package.

Generating an AWR report

To generate an AWR report, use the rdsadmin.rdsadmin_diagnostic_util.awr_report procedure.

The following example generates a AWR report for the snapshot range 101–106. The output text file is named awrrpt_101_106.txt. You can access this report from the AWS Management Console.

EXEC rdsadmin.rdsadmin_diagnostic_util.awr_report(101,106,'TEXT');

The following example generates an HTML report for the snapshot range 63–65. The output HTML file is named awrrpt_63_65.html. The procedure writes the report to the nondefault database directory named AWR_RPT_DUMP.

EXEC rdsadmin.rdsadmin_diagnostic_util.awr_report(63,65,'HTML','AWR_RPT_DUMP');

Extracting AWR data into a dump file

To extract AWR data into a dump file, use the rdsadmin.rdsadmin_diagnostic_util.awr_extract procedure.

The following example extracts the snapshot range 101–106. The output dump file is named awrextract_101_106.dmp. You can access this file through the console.

EXEC rdsadmin.rdsadmin_diagnostic_util.awr_extract(101,106);

The following example extracts the snapshot range 63–65. The output dump file is named awrextract_63_65.dmp. The file is stored in the nondefault database directory named AWR_RPT_DUMP.

EXEC rdsadmin.rdsadmin_diagnostic_util.awr_extract(63,65,'AWR_RPT_DUMP');

Generating an ADDM report

To generate an ADDM report, use the rdsadmin.rdsadmin_diagnostic_util.addm_report procedure.

The following example generates an ADDM report for the snapshot range 101–106. The output text file is named addmrpt_101_106.txt. You can access the report through the console.

EXEC rdsadmin.rdsadmin_diagnostic_util.addm_report(101,106);

The following example generates an ADDM report for the snapshot range 63–65. The output text file is named addmrpt_63_65.txt. The file is stored in the nondefault database directory named ADDM_RPT_DUMP.

EXEC rdsadmin.rdsadmin_diagnostic_util.addm_report(63,65,'ADDM_RPT_DUMP');

Generating an ASH report

To generate an ASH report, use the rdsadmin.rdsadmin_diagnostic_util.ash_report procedure.

The following example generates an ASH report that includes the data from 14 minutes ago until the current time. The name of the output file uses the format ashrptbegin_timeend_time.txt, where begin_time and end_time use the format YYYYMMDDHH24MISS. You can access the file through the console.

BEGIN
    rdsadmin.rdsadmin_diagnostic_util.ash_report(
        begin_time     =>     SYSDATE-14/1440,
        end_time       =>     SYSDATE,
        report_type    =>     'TEXT');
END;
/

The following example generates an ASH report that includes the data from November 18, 2019, at 6:07 PM through November 18, 2019, at 6:15 PM. The name of the output HTML report is ashrpt_20190918180700_20190918181500.html. The report is stored in the nondefault database directory named AWR_RPT_DUMP.

BEGIN
    rdsadmin.rdsadmin_diagnostic_util.ash_report(
        begin_time     =>    TO_DATE('2019-09-18 18:07:00', 'YYYY-MM-DD HH24:MI:SS'),
        end_time       =>    TO_DATE('2019-09-18 18:15:00', 'YYYY-MM-DD HH24:MI:SS'),
        report_type    =>    'html',
        dump_directory =>    'AWR_RPT_DUMP');
END;
/

Accessing AWR reports from the console or CLI

To access AWR reports or export dump files, you can use the AWS Management Console or AWS CLI. For more information, see Downloading a database log file.