Oracle Cloud DBMS - AWR Viewer for Performance Data Retention
Performance data visualization, problem-detection, and auto-tuning of the on-premises Oracle databases (and, other databases) managed by Oracle Cloud Infrastructure (OCI) Database Management service get a level-up with the new Automatic Workload Repository Viewer, a.k.a. the AWR Viewer. It adds significant new capabilities to the already existing, best-in-class database performance statistics collection, processing, and maintenance tools, such as longer AWR data retention, hourly reports toggling free single interface, ASH/SQL and AWR reports, visualization, and more.
The AWR Viewer consists of two resourceful parts:
- in-memory performance statistics, which is accessible via dynamic performance (V$) views, and
- snapshots of these V$ views persisted in the database registering the historical values
The RH$ and DBA_HIST persisted AWR tables comprise affluence of significant performance data usable in performance-tuning trend analysis, and comparison between two different points in time. Analysts can readily transport the AWR data across systems leveraging the AWR extract feature and the capability to perform AWR data analysis separately, as and when required.
Out of the box, the AWR Viewer snapshots are captured every hour and retained for the next eight consecutive days. The interval is adjustable and can regulate the snapshot capturing and storing frequency. Whereas, the AWR data retention time in AWR Viewer is determined by the set historical data retention time settings. Note: A shortened snapshot interval results in increased data collection frequency and volume by AWR. Similarly, a longer retention period also increases the demand for storage.
Related readings:
In short, the Automatic Workload Repository Viewer enables performance analysts to readily visualize the trend in Oracle database performance via the ‘Performance Hub’. Also, because prolonged retention of the AWR data in the production database is a tricky spot, the AWR Viewer enables them to retain AWR data for longer stretches of time, run offline analyses, compare against different databases, and so forth. And, thereby, offers an extended view on the Oracle database performance data.
AWR Viewer Features List
AWR Viewer is the common ground for performance and data visualization in Oracle databases, offering long-range visibility via the historical performance data extracted from AWR snapshots in simple, easy-to-understand charts. These are categorically distributed across the following menu tabs:
Top Activity: The ‘Top Activity’ tab in Automatic Workload Repository Viewer extends visibility on the database performance trend, paired with the capabilities to filter based on numerous ASH dimensions, top wait events, and SQLs.
Metrics: The AWR Viewer’s ‘Metrics’ tab catalogs and shows system metric values via DBA_HIST_SYSMETRIC_HISTORY, including average active sessions, and metrics regarding IO, CPU, logons, and network.
Load Profile: The ‘Load Profile’ tab on AWR Viewer classifies and demonstrates historical system statistics data in DBA_HIST_SYS_TIME_MODEL and DBA_HIST_SYSSTAT, affording analysts with the fundamental information on the global health of the Oracle database.
Wait Events: This AWR Viewer tab makes the top 10 wait events arranged by wait time visible. Upon clicking the ‘Show Histogram’ button for a wait event, it displays a histogram of the ratio of total waits inside the user-defined snapshot range.
Database Parameters: The ‘Database Parameters’ tab in the Automatic Workload Repository Viewer exhibits all the database parameters modified within the snapshot range followed by the corresponding modification history.
Note: The AWR Viewer is available under Oracle’s Free Cloud Tier.
