We’re excited to announce the support for Automatic Workload Repository (AWR) Viewer in Oracle Cloud Infrastructure (OCI) Database Management service for on-premises Oracle databases. This feature allows you to visualize AWR data for a database monitored by Database Management and use it as a central repository to import and visualize AWR data from other databases.
The Automatic Workload Repository (AWR) is a database feature at the heart of the collection and processing of the vast amount of performance data in Oracle Database service. The data collected by AWR forms the basis for most of the problem-detection and self-tuning mechanisms that Oracle Database provides. AWR consists of two components: In-memory performance statistics, accessible through V$ views, and snapshots of these V$ views persisted in the database that records the historical values. The persisted AWR tables, such as WRH$ and DBA_HIST tables, contain a wealth of important performance data, which can be useful in performance tuning trend analysis and when comparing performance between two separate periods. You can transport the AWR data between systems, using the AWR extract feature and allowing you to use a separate system to perform an analysis of the AWR data when required.
By default, AWR snapshots are taken every hour and retained eight days. The snapshot interval determines the frequency at which snapshots are captured and stored. A smaller snapshot interval increases the frequency, which in turn increases the volume of data collected and stored by AWR. The historical data retention period determines how long AWR data is retained before being purged. Obviously, a longer retention period increases the space required by AWR.
As a performance analyst, you can rely on the Performance Hub to visualize the performance trend of an Oracle Database. However, retaining AWR data in the production database for an extended period is difficult. AWR Viewer is the solution that retains AWR data for longer periods of time, perform an offline analysis, compare AWR data against a similar database, and so on.
AWR Viewer is an integration of performance and data visualization tools, which displays the historical performance data from AWR snapshots in easy-to-interpret charts on the following tabs:
The Top Activity tab displays the performance trend of the database with capabilities to filter by various ASH dimensions, top SQLs, and top wait events.
Figure 1: Top Activity tab in AWR Viewer
The Metrics tab categorizes and displays system metric values from DBA_HIST_SYSMETRIC_HISTORY, such as average active sessions, metrics about CPU, IO, network, and logons.
Figure 2: Metrics tab in AWR Viewer
The Load Profile tab categorizes and displays historical system statistics information in DBA_HIST_SYSSTAT and DBA_HIST_SYS_TIME_MODEL. The charts on this tab report the fundamental information about the global health of the database.
Figure 3: Load Profile tab in AWR Viewer
The Wait Events tab displays the top 10 wait events sorted by wait time. When you click the Show Histogram button for a wait event to view a histogram of the percentage of total waits within the selected snapshot range.
Figure 4: Wait Events tab in AWR Viewer
This tab displays all the database parameters that were changed within the snapshot range and the corresponding change history.
The AWR Viewer is a single interface that enables you to plot the performance trend without having to toggle between hourly AWR reports. It provides different aspects of Oracle Database performance data, which helps in detecting issues, and allows you to generate reports, such as the ASH report, AWR report, and the SQL report.
For more information about this feature and how you can use it, see the documentation.
Sign up for a free account on Oracle’s Cloud Free Tier. For more best practices, solution playbooks, and cross-product reference architectures, visit our reference architecture center. Share your feedback in the comments and let us know how we can continue to improve your experience on Oracle Cloud Infrastructure.