Oracle Cloud Infrastructure Documentation

Use AWR Explorer to Analyze Database Performance

You can use AWR Explorer to explore and analyze AWR data for a Managed Database and use it as a central repository to import, analyze, and compare AWR data from other databases.

The AWR is a built-in repository in the Oracle Database, which collects, processes, and maintains performance statistics of the database. AWR consists of two components: in-memory performance statistics, accessible through V$ views, and snapshots of these V$ views that persist in the database. Snapshots are sets of historical performance data that are automatically gathered at regular intervals by the database. By default, snapshots are taken every hour and retained for a period of eight days in the AWR, and you can change the snapshot interval to determine the frequency at which snapshots are captured and stored. The historical data derived from these snapshots is recorded in tables such as WRH$ and DBA_HIST in the database, and forms the basis for most of the problem detection and self-tuning mechanisms that Oracle Database provides. In addition, this data can be transported from one database to another using the AWR extract feature, thereby allowing the use of one database as a central repository to analyze AWR data. For more information on AWR and how to enable it, see Diagnosing and Tuning Database Performance in Oracle Database Performance Tuning Guide.

AWR Explorer in Database Management is an integration of performance and data visualization tools, which displays the historical performance data from AWR snapshots in easy-to-interpret charts. It enables you to visualize AWR data in a single interface, thereby allowing you to analyze performance trends and detect issues.

Using AWR Explorer, you can:

  • Explore and analyze AWR data for a Managed Database.
    Note

    • A Management Agent 210403.1349 or later is required to use AWR Explorer for External Databases.
    • AWR Explorer is only available for Oracle Database Enterprise Edition version 12.2 and later and if the COMPATIBLE initialization parameter is set for the database, then it should match version 12.2.0 and later. For information on database compatibility, see What Is Oracle Database Compatibility? in Oracle Database Upgrade Guide. Also, the status of the database must be up for the AWR Explorer button to be enabled.
  • Import AWR data from other databases to a Managed Database using awrload.sql and analyze the data using AWR Explorer. For information on how to import the AWR to a Managed Database from another database, see How to Export and Import the AWR From One Database to Another (Doc ID 785730.1) in My Oracle Support.

You can perform AWR Explorer-related tasks in the console and using API. Information on accessing and using AWR Explorer in the console is given in this section and its topics. For API information, see Database Management API.

To access AWR Explorer:

  1. Go to the Managed database details page of the Managed Database.
  2. Click AWR Explorer.

    AWR Explorer is displayed.

The following fields are displayed in AWR Explorer and you can select the options in these fields to determine the data to be displayed:

  • Database: Name of the database as stored in AWR. By default, the database from whose details page AWR Explorer was launched is selected. If AWR is not enabled for the Managed Database but if AWR data has been imported to the Managed Database from other databases, then the database that has the latest AWR snapshot is selected. Click the Database field to view the Select AWR snapshots panel and:
    • Select a snapshot range from the available snapshot ranges, which are defined based on continuous snapshot IDs from the same database. For example, if snapshots 2, 3, 4, 5, 101, and 102 are available for a database, then the snapshot ranges will be 2 – 5 and 101 – 102. By default, the most recent snapshot range is selected.
    • Select another database and snapshot range. This is applicable if the Managed Database is used as a central repository for AWR data from other databases.
  • Time range: Duration of the snapshot range for which AWR data is displayed. By default, the duration of the latest AWR snapshot range is selected. However, if the snapshot range has more than six snapshots, then the default time range is the duration of the most recent six snapshots in the snapshot range. For example, if the duration of each snapshot (snapshot interval) is 10 minutes, then the time range is 6 * 10 minutes = 1 hour and the data within this time range is displayed.

    Click the Time range field to view the Custom time range dialog box and change the duration within the selected time range, if required.

  • Time zone: Time zone in which AWR data is displayed. By default, the UTC (Coordinated Universal Time) time zone is selected. Click this field to replace the default time zone with your local browser time zone.

AWR Explorer Tabs

You can monitor the important aspects of database performance within the selected time range, on the following AWR Explorer tabs. These tabs display charts that facilitate an easy interpretation and comparison of AWR data.

  • Load profile: This tab displays historical system statistics from AWR tables such as DBA_HIST_SYSSTAT. The charts display the default SYSSTAT categories, such as Time model overview, Connections, and Logons, which provide an overview of the global health of the database.

    On the Load profile tab, you can add charts to display other supported system statistics. To do so:

    1. Ensure that Custom is selected in the System statistics drop-down list.
    2. Click Add load profile chart.
    3. In the Add custom load profile chart panel, enter a title for the new chart, select the system statistics that you want to display, and click Add load profile chart.

  • Metrics: This tab displays system metrics from AWR tables such as DBA_HIST_SYSMETRIC_HISTORY. The charts display metrics such as CPU, I/O, and network metrics.

    Note

    The Metrics tab does not display data for PDBs as system metrics are not available in the PDB AWR.

    On the Metrics tab, you can add charts to display other supported metrics. To do so:

    1. Ensure that Custom is selected in the Metrics drop-down list.
    2. Click Add metrics chart.
    3. In the Add custom metrics chart panel, enter a title for the new chart, select the metrics that you want to display, and click Add metrics chart.

  • Wait events: This tab displays the top ten wait events sorted by wait time from AWR tables such as DBA_HIST_SYSTEM_EVENT. The charts display the wait event and the corresponding Waits per second and Average wait time information.

    On the Wait events tab, you can also:

    • Add new charts to display more wait events. To do so, select a wait event in the Wait event drop-down list and click Add wait event chart.
    • View a histogram of the wait event with a break down of the percentage of waits by their duration, within a selected snapshot range. To view a histogram, click the Show histogram option available for each wait event chart or click on the chart.

  • Activity: This tab enables you to view the performance trend of the database by selecting an ASH dimension and viewing the top activity for that dimension in the selected time range. You can also scroll down to view the top activity by additional dimensions based on the dimension selected in the Average Active Sessions chart. For example, if the default dimension, Wait class is selected in the Average Active Sessions chart, then you can view the top SQL ID and Wait event by Wait class or select other dimensions in the drop-down lists in the upper-left corner of the tables.

    Note

    Similar to Performance Hub for Managed Databases, the availability of data on the Activity tab in AWR Explorer depends on the Oracle Database type and version and requires certain additional privileges. For more information, see OCI: Prerequisite Conditions for Performance Hub (Doc ID 2760305.1) in My Oracle Support.

  • Database parameters: This tab displays all the database parameters that were changed and you can click the link in the Changed column to view change history. The change history information includes details such as the ID of the snapshot when the database parameter was changed, the start and end time of the snapshot, and the changed value.

    On this tab, you can also view all the database parameters, including those that were not changed. To do so, deselect the Hide unchanged parameters check box.

AWR Chart Visualization Options

You can use the following data visualization options to filter data and make changes to how data is displayed in AWR Explorer charts.

  • Display raw data or chart (Display raw data/chart): Click to switch between a line chart and raw data. You can use this option to copy and analyze raw data.
  • Change to bar or line chart (Change to bar/line chart): Click to switch between a line chart and a bar chart. Note that a change made to the visualization option in one chart applies to all the charts on the tab.
  • Zoom in and Zoom out (Zoom in and Zoom out): Click to alter the scope of what is displayed in the chart. After clicking the Zoom in icon, you can use mouse and touch pad to scroll to the right or left of the chart, and zoom in and out. Note that a change made using the zoom options in one chart applies to all the charts on the tab.
  • Restore (Restore): Click to undo any changes made to the charts using the other options and return to the default display.
  • Save chart as image (Save chart as image): Click to save the chart in .png format.
  • Remove chart (Remove chart): Click to remove the chart from the tab. You can add the chart again by selecting it in the drop-down list on the tab and clicking Add <name of tab> chart.

Generate Reports

You can generate and download various reports from the database in AWR Explorer.

To do so, click Reports and select one of the following options:

  • AWR: In the Generate AWR report panel, select the snapshot range and click Generate report to generate and download an AWR report with additional performance data.
  • SQL: In the Generate SQL health check report panel, select the snapshot range, specify a SQL ID, and click Generate report to generate and download a SQL Health Check report with additional performance data for the specified SQL statement.
  • ASH: In the Generate ASH report panel, select the start and end time and click Generate report to generate and download an ASH report with additional ASH data.