We’re excited to announce the support for the Automatic Workload Repository (AWR) Hub in Oracle Cloud Infrastructure (OCI) Operations Insights service for Oracle Autonomous Databases. AWR Hub lets you consolidate and store detailed performance data from the Automatic Workload Repository of your important Oracle Autonomous Databases.

What is AWR Hub?

The Automatic Workload Repository (AWR) is a database feature at the heart of collecting and processing the vast amount of performance data in the Oracle Database. The data collected by AWR forms the basis for most of the problem detection and self-tuning mechanisms that the 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 that can be useful in performance tuning, trend analysis, and comparing performance between two separate periods. The Operations Insights AWR Hub lets you consolidate and store detailed performance data from the AWR of your critical Oracle databases. This consolidated AWR Hub allows you to view and analyze historical performance data beyond the AWR retention period of the source database.

By default, AWR snapshots are taken every hour and retained for 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 the AWR. The historical data retention period determines how long AWR data is retained before being purged. A longer retention period increases the space required by the AWR, making it difficult to retain AWR data in the production database for an extended period. AWR Hub is the solution that retains AWR data for longer periods of time, perform offline analysis, or compare AWR data against similar databases.

To handle AWR data from multiple databases, Operations Insights provides AWR Hub, a cloud-based repository that collects and consolidates AWR snapshots from multiple databases in your OCI tenancy.

Configuring your AWR Hub

  1. Create the warehouse and AWR Hub: You can set up the AWR Hub from the Operations Insights console. The AWR Hub is an Oracle autonomous data warehouse created in the Operations Insights services tenancy. The Oracle Autonomous Database service can provide an SQL interface as a processing layer to analyze the AWR data.

  2. Configure a data lake centered on Oracle Cloud Infrastructure Object Storage: All the AWR data from the source databases are ingested into OCI Object Storage service, then processed or transformed and loaded to the AWR Hub Warehouse created in step 1. Configure the AWR data from the source database to upload to an Object Storage bucket that resides in your tenancy. Before that, define cross-tenancy policies that allow the AWR Hub to pull AWR snapshots from OCI Object Storage.

  3. Configure the source databases to upload AWR snapshots to AWR Hub: For any source database uploading AWR snapshots to AWR Hub, you need to register that database with the AWR Hub. You register the database by running the PL/SQL scripts provided in the Operations Insights AWR Hub registration page.

  4. Create an Operations Insights warehouse database user: Create a warehouse database user with read-only access and access to the PL/SQL packages to run queries on the AWR tables. These permissions enable long-term analysis of AWR data across databases without impacting performance or storage on the source database targets.


Figure 1: AWR Hub configuration steps

Use cases

  • Track and analyze SQL plan changes for workloads within a single database

  • Track and analyze SQL plan changes for workloads across multiple databases

  • Understand past and present workload (SQL) performance over a period of two years

  • Build custom dashboards to plot SQL performance statistics, database waits, CPU usage history, and connections established

Conclusion

AWR contains vast amounts of database performance data. The AWR Hub lets you keep a long-term history of AWR data forever, if configured that way, from the selected database sources. This configuration enables long-term analysis of AWR data across databases without performance or storage impact on the source database targets.

AWR Hub is available to use with Oracle Autonomous Database Shared Infrastructure on all commercial regions. Support for Oracle Autonomous Dedicated Infrastructure, Oracle Databases on Oracle Cloud Infrastructure virtual machines, bare metal and Exadata Cloud services, and on-premises is coming soon.

We hope that you enjoy the Operations Insights AWR Hub feature! Sign up for an Oracle Cloud Infrastructure trial account! For more information about this feature and how you can use it, see the documentation.