This is the first part of my AWR report analysis blogs. This is my understanding and how I approach an AWR report for proactive/reactive performance tuning. If you find any errors or issues in the articles, please leave a comment below.
AWR is an acronym for Automatic Workload Repository which provides vital database information for analyzing and troubleshooting. Just like any other software products oracle also have instrumentation code integrated to it. Even though this adds a little bit of overhead it helps us to troubleshoot the product efficiently.
AWR is introduced in oracle 10g and prior to that Statspack reports were used for database troubleshooting. And the list of metrics captured by oracle is listed in v$metricname.
The AWR report contains the following set of information
- Wait events information.
- Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
- Active Session History statistics from the V$ACTIVE_SESSION_HISTORY view.
- System and session statistics from the V$SYSSTAT and V$SESSTAT views.
- Object usage statistics.
- Resource intensive SQL statements.
Many oracle troubleshooting utilities use AWR data for troubleshooting. Oracle utilities using AWR generated information are
- Automatic Database Diagnostic Monitor
- SQL Tuning Advisor
- Undo Advisor
- Segment Advisor
Oracle AWR reports uses snapshots to collect/record data from database. A snapshot is a set of information regarding performance and resource usage of the database over a period of time.
Oracle collects information all the while and save this data while the snapshot happens. Users can alter the snapshot interval based on their requirements. Users can also generate custom snapshots by explicitly running the snapshot command either through OEM or any oracle client software.
We can check the AWR snapshot interval and retention time by using the following command.
SELECT * FROM DBA_HIST_WR_CONTROL
Retention Time – Time for which the snapshot information will be stored in the Database.
Default Snapshot Interval time – 1Hr
Default Retention Time – 7 days
Oracle Process responsible for taking snapshots is MMON (Memory Monitor).
A baseline is defined a range of snapshots that can be used to compare to other pairs of snapshots. The Oracle database server will exempt the snapshots assigned to a specific baseline from the automated purge routine. Thus, the main purpose of a baseline is to preserve typical runtime statistics in the AWR repository. We can run the AWR snapshot reports on the preserved baseline snapshots at any time and compare them to recent snapshots contained in the AWR.
We need to enable some basic parameters and some privilege for running the AWR report.
Parameters to be set:
TIMED_STATISTICS = TRUE
STATISTICS_LEVEL = TYPICAL or ALL
EXECUTE on DBMS_WORKLOAD_REPOSITORY Package
We can modify the snapshot and baseline setting using the following commands. You need privilege to run these commands. You can run these commands from any oracle clients.
( retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT NULL);
( flush_level IN VARCHAR2 DEFAULT ‘TYPICAL’) RETURN NUMBER;
FLUSH_LEVEL – Typical or All
( start_snap_id IN NUMBER,
end_snap_id IN NUMBER,
baseline_name IN VARCHAR2,
dbid IN NUMBER DEFAULT NULL);