In 2021, we announced support for Automatic Workload Repository (AWR) Hub in Oracle Cloud Infrastructure (OCI) Operations Insights service for Oracle Autonomous Database. AWR Hub enables you to consolidate and store detailed performance data from the AWR of your important Oracle autonomous databases. We’re excited to share that AWR Hub now supports AWR Explorer.
AWR Explorer for AWR Hub
AWR is an important and useful source of information for performance analysis of your Oracle database. The general practice creates an AWR report, analyzes the top wait events, and expands on a specific category of interest. The Operations Insights AWR Hub lets you consolidate and store detailed performance data from the AWR of your important Oracle databases. This consolidated AWR Hub allows you to view and analyze historical performance data beyond the AWR retention period of the source database.
You can handle AWR data from multiple databases with AWR Hub, a cloud-based repository that collects and consolidates AWR snapshots from multiple databases in your OCI tenancy.
With AWR Explorer, AWR Hub helps you increase the usefulness of the AWR data beyond looking at the reports manually. With AWR Explorer, you can visualize performance trends easily without needing to switch between hourly AWR reports.
Mining performance trends from AWR Hub using AWR Explorer
While you examine a single AWR report and are trying to analyze how certain database components perform, you’re most likely looking at a single number. For example, you need to know the disk I/O performance to log files or a log file sync introduced by CPU starvation. Most likely, you create an AWR report for the entire peak or the whole day and examine the log file sync at the foreground wait events to find the average wait time for log file sync. This analysis gives you only one data point.
Figure 1: AWR report for the peak period reporting log file sync
You can also get a series of AWR reports, manually going through the reports and noting the value of interest for a particular statistic and plot it in a chart. You’re manually mining the AWR data to get a trend chart like the following example.
Figure 2: Trend chart for log file sync
AWR Hub allows you to ingest and store AWR data from various databases. Using this data, you can extract the required information in a trend chart provided in AWR Explorer. For example, if you want to know about the trends for Redo Generated per second or Logical reads per transaction, use the metrics tab of the AWR Explorer to visualize the trends over a set of AWR snapshots without having to switch between the reports and manually creating the charts. Uncountable examples of AWR mining can be used to research issues and find root causes.
Figure 3: AWR metrics trends in AWR Explorer
If you’re investigating problems related to wait event times, you can use the histogram charts in the wait event section of the AWR Explorer to review wait event statistics and view them as histograms. This capability is handy if you need to understand a specific Oracle wait event timing scenario in more detail than the average wait time.
Figure 4: AWR wait event histograms in AWR Explorer
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 capability enables long-term analysis of AWR data across databases without performance or storage impact on the source database targets. AWR Explorer 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, allowing you to analyze performance trends and detect issues.
AWR Explorer for AWR Hub is available to use with Oracle Autonomous Database in all commercial regions. Support for Oracle Databases on OCI virtual machine, 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.