Oracle Automatic Workload Repository an Overview

Performance tuning and problem diagnosing are the two most challenging and important management task DBAs perform on their databases. AWR report display performance information from a particular time period identified by snapshots of AWR report data that have been generated previously. 

AWR reports are an extremely useful diagnostic tool for the determination of the potential cause of database wide performance issues.  Typically when a performance issue is detected, you would collect an AWR report covering the period of the poor performance. It is best to use a reporting period no longer than 1 hour as otherwise specifics can be lost. It is also important to collect performance report when the database is running normal, not when it is idle, to establish a base line performance metric. 

The first thing to observe in an AWR report is the snap interval and Elapsed/DBtime. Following report showing about 1470 minutes of DB time for a wall clock time of 30 minutes for a 32 core server. 

On an average Oracle spend 45 minute per CPU core in 30 minutes. DB time is the sum of DB time for all activities that include CPU time, I/O time and non-idle wait time by foreground sessions. Average active sessions is the key matric for measuring the DB load, Average active sessions are the sum of average activity over all sessions.

AWR has the report summary part that summarizes a database activity. One important part in the section is the Load profile.

Depending on the waits, the load profile section provides useful information. We can get overall idea about metrics such as Redo size, Logical reads etc. per second and per transaction. 

Another important section of the summary part is the Top 5 foreground wait events. 

These are the top 5 waits during the 30 minutes period where database spend most of it’s DB time. When we add up the Time(s) in the above list, It should roughly equal to 1470 minutes(88200s). 

Summary section also gives the Instance efficiency and load average. 

In this article, I am not going to the main report part. Amount of data/metrics it provides cannot be explained in one article, each section needs it’s own article to explain the details.

AWR repository has following specific  views where history of snapshot and other metric data is stored. Oracle uses SYSAUX tablespace for storing these data. There are other DBA_HIST_* view not shown in this picture.

MMON process collect these statistics/metrics from views such as v$session, v$session_wait and keep it in the SGA buffer cache. MMON slaves captures data directly from memory into SYSAUX tablespace into wrh$ and wri$ tables. By default, AWR collects “snapshots” of database statistical data every hour. Capture frequency can be adjusted. 

In RAC environment, Every instance MMON slave collect data and store it in DBA_HIST* tables.

When viewing AWR report, always check corresponding ADDM report for actionable recommendations. ADDM is a self-diagnostic engine designed from the experience of Oracle’s best tuning experts. AADM analyzes AWR data automatically after an AWR snapshot and makes specific performance recommendations

AWR report can generated using OEM or awr SQL scripts. AWR SQL scripts are located in $ORACLE_HOME/rdbms/admin directory. These scripts also provide a set of other report.